Nikhil_4_Oracle

• Mar 23rd, 2007

HI ALL,try this..,create or replace procedure ddl_proc(tabname in varchar2)asv_cursor INTEGER;ddl_exe INTEGER;beginv_cursor:=Dbms_sql.open_cursor;Dbms_sql.Parse(v_cursor,' Create Table '||tabn...

Anurag Puranik

• Mar 23rd, 2007

We can use DDL commands in Procedure & Functions through "EXECUTE IMMEDIATE " Command

Answer Question  Select Best Answer

Hi,1. Iam looking for sample program for ref cursor. ref cursor can be associated with many select statements and we can use the ref cursor to return the multiple value from the procedure or function. how to use the ref cursor in a program to associate with many select statements.2. we can't use the %rowtype attribut to return the multiple from the procedure or function instead of ref cursor. if we...

krishnamohan_geek

 Profile Answers by krishnamohan_geek Questions by krishnamohan_geek

• Mar 25th, 2007

Hi,Let me answer your second questin first. Why ref cursor instead of %ROWTYPE.%ROWTYPE is used for fetches record/row of a single table.OR in case of columns of different tables then you have to crea...

Answer Question  Select Best Answer

subbaraovangalli

 Profile Answers by subbaraovangalli Questions by subbaraovangalli

• Mar 26th, 2007

 by using            show_errors we can  see the errors in the prev created procedure.      dbms_output.put_li...

deep_paudel

 Profile Answers by deep_paudel Questions by deep_paudel

• Mar 12th, 2007

DBMS_OUTPUT.put_line

Answer Question  Select Best Answer

Suppose if i have 8 triggers declared for a table..how to disable a single trigger from a table...i don't want to disable all triggers for a table. I know we can disable all triggers by using "alter table disable all triggers;". just i want to know how to disable single trigger from a table.

Pavan

• Apr 3rd, 2007

Thanks parweej, alter trigger <trigger-name> disable worked for me..

subbaraovangalli

 Profile Answers by subbaraovangalli Questions by subbaraovangalli

• Mar 27th, 2007

To disable a perticular trigger on a table             alter table disable trigger

Answer Question  Select Best Answer

mohan

• Apr 9th, 2007

CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE   PRAGMA AUTONOMOUS_TRANSACTION;  i PLS_INTEGER; BEGIN   SELECT COU...

mohan kumar

• Apr 9th, 2007

Yes We can use commit statement in triggers with 10G.Here is example :CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE   PRAGMA AUTONOMOUS_TRANSA...

Answer Question  Select Best Answer

ramnath123

 Profile Answers by ramnath123 Questions by ramnath123

• Oct 21st, 2011

Code + decode

Code
code + decode

Answer Question  Select Best Answer

Hi all I wrote a procedure to send email via plsql procedure, It was compiled successfully but not working ....

here is the code (actuly i added the dbms output like step1 ,step 2 to see in which step the control is going on )

PL/SQLcreate or replace PROCEDURE send_test_message
IS
mailhost VARCHAR2(64) := 'mail.cmcltd.com';
sender VARCHAR2(64) := 'akash.sharma@cmcltd.com';

KiranKW

 Profile Answers by KiranKW Questions by KiranKW

• May 3rd, 2007

Hi,   I dont see any problem with your code ... try to capture the return code for each of the calls ... that will help to identify the exact problem

Answer Question  Select Best Answer

Hi, can any body give me the sample program for avoiding the mutating error. we can avoid the mutating through the statement level trigger instead of using the statement leve trigger, i need one sample program to avoid the mutating error by using pl/sql table.Thanks Saravanan.P

KiranKW

 Profile Answers by KiranKW Questions by KiranKW

• May 3rd, 2007

A mutating error comes when u r trying to do some DML operation on a table that owns the trigger.... there are a lot of options to avoid mutation .. eg, Making it autonomous transaction...

Answer Question  Select Best Answer

What is the differences between it.

norman

 Profile Answers by norman Questions by norman

• May 8th, 2007

Rule Based Optimization is an older technique which is used for optimization of queries but follows a fixed rule or strategy for achieving the same. But in contrast cost Based Optimization is a later ...

Answer Question  Select Best Answer

RoulRaj

• May 9th, 2007

Dear Rajeswaran,Please give one example(application) where one can use force view.Thanks & regards

Rajeshwaran

• Jun 3rd, 2006

usually the views are created from the basetable if only the basetable exists.The purpose of FORCE keyword is to create a view if the underlying base table doesnot exists.ex : create or replace FORCE ...

Answer Question  Select Best Answer

RoulRaj

• May 11th, 2007

There should be atleast one statement in between Begin and End.The Statement may be Null statement also.Syntax:declarebeginnull;end;RegardsR.K.Roul

Answer Question  Select Best Answer

gopichandv2002

• May 22nd, 2007

You should write returning in the blank place

Answer Question  Select Best Answer

 Function is called as part of an expression.   sal := calculate_sal ('a822');   procedure is called as a PL/SQL statement   calculate_bonus ('A822');

idonknowyou

 Profile Answers by idonknowyou Questions by idonknowyou

• May 22nd, 2007

Procedure can be called in the following waysa) CALL <procedure name> direcb) EXCECUTE <procedure name> from calling environmentc) <Procedure name> from other procedures or functions...

M.KUMARAN

• Nov 13th, 2005

Function is called as part of an expression.   sal := calculate_sal ('a822');   procedure is called as a PL/SQL statement   calculate_bonus ('A822');KKK

Answer Question  Select Best Answer

 User_objects, User_Source and User_error. 

idonknowyou

 Profile Answers by idonknowyou Questions by idonknowyou

• May 22nd, 2007

The Data dictionary tables/ Views where the characteristics of subprograms and Packages are stored are mentioned belowa) USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTSb) USER_SOURCE, ALL_SOURCE, DBA_SOURCEc) USER_DEPENCENCIESd) USER_ERRORS, ALL_ERRORS, DBA_ERRORS

vinod kumar

• Nov 16th, 2005

User_source , user_object , user_errors

Answer Question  Select Best Answer

madhug56

 Profile Answers by madhug56 Questions by madhug56

• Jun 12th, 2007

SQL is a structured query language.Used to perform operations such as retrieval,updations,insertions etc. on database.PL/SQL is a Programming language which is an extension for SQL with control struct...

psom

• Jun 8th, 2006

  Embedded SQL - SQL statements are invoked from a host environment like C/C++, Java or any other programming languages.

Answer Question  Select Best Answer

Chinmay

• Jun 28th, 2007

Yes,you can do by using like with orlike select * from tab1where col1 like '%back%' or col2 like '%back%'or col3 like '%back%'or col4 like '%back%'

RoulRaj

• May 11th, 2007

Hello Guna Sagar,

Please give one example i.e without knowing the column name one can write a query.

Regards
Roul

Answer Question  Select Best Answer

visitor

• Jul 4th, 2007

If you do not want any executable statement within the begin-end block, you can include a null statment.eg:begin     null;end;

Chiranjit Dutta

• Feb 27th, 2007

We cant havedeclarebeginendwe must have something between the begin and the end keywordsotherwise a compilation error will be raised.

Answer Question  Select Best Answer

Meera

• Jul 4th, 2007

The internal table is a table which is getting filled during the run time of the program...and after that there wont exist any value in that internal table whereas table is the one which contains data permanently

Answer Question  Select Best Answer

jamesravid

 Profile Answers by jamesravid Questions by jamesravid

• Jul 5th, 2007

You can use SYSDATE oracle.lv_today := sysdate;or select sysdate into lv_today from dual;both the statements assign sysdate to lv_today

Answer Question  Select Best Answer

 Mutation of table occurs.

rudra pratap

• Jul 5th, 2007

In order to avoid mutation table error we need to have a row level as well as a statment level trigger.

Sunil Yadav

• Oct 26th, 2005

To avoid the mutation table error ,the procedure should be declared as an AUTONOMOUS TRANSACTION.By this the procedure will be treated as an separate identity.

Answer Question  Select Best Answer