Radha

• Jul 13th, 2011

Flash Back query is used to get back the deleted rows & dropped tables from the database even they are committed. To get back the dropped table: Flashback table to before drop; To get back...

murthy 1053

 Profile Answers by murthy 1053 Questions by murthy 1053

• Jul 2nd, 2010

Flash back query is used to retrive the data.For ex if a table is dropped and we want the table again so at that time.syn:=FLASHBACK TABLE table_name TO BEFORE DROP.This will work only if recycled bin is not purged.

Answer Question  Select Best Answer

How will you debug PL/SQL Code?

geek.hari

 Profile Answers by geek.hari Questions by geek.hari

• Dec 14th, 2009

One conventional and popular method of debugging a pl/sql code is through printing the program execution path using a printable statement. For this you have to enable the output mode using the command...

OraclePassport

 Profile Answers by OraclePassport Questions by OraclePassport

• Apr 17th, 2009

The Best way to debug is by using an AUTONOMOUS TRANSACTION. Create a dummy table and insert into the table using the AT. This wont affect the main code, but one could know which part of the code the execution has reached while the program is running itself.

Answer Question  Select Best Answer

What is the main purpose of RETURN in functions when we can calculate a value and pass it through using OUT mode in procedure or functions?

Deepika S Verma

• Mar 14th, 2014

Its a myth that function returns value whereas procedure does not, actually procedure also return value (i.e. 0 or 1 ) at the time of exiting which indicates the success or error. Whereas Function ret...

shivigupta

 Profile Answers by shivigupta Questions by shivigupta

• Oct 9th, 2012

create or replace procedure proc1 as begin dbms_output.put_line(Before Return Clause); return; dbms_output.put_line(After Return Clause); end; set serveroutput on; begin proc1; dbms_output.put_line(I...

Answer Question  Select Best Answer

neerajalatha

 Profile Answers by neerajalatha Questions by neerajalatha

• Jul 7th, 2010

Disadvantages:It is slow when compared to native dynamic SQL and coding is also difficult when compared to native dynamic SQL.

stshekhar

 Profile Answers by stshekhar Questions by stshekhar

• Jun 22nd, 2008

Disadvantage : 1) Executing DDL statements through dbms_sql sometime can result in deadlock. example : we are trying to drop one object at the time it is being accessed by other user.

Answer Question  Select Best Answer

msenthil19

 Profile Answers by msenthil19 Questions by msenthil19

• Mar 18th, 2009

Error will be like "Cursor already open"

ratna82

 Profile Answers by ratna82 Questions by ratna82

• Oct 10th, 2008

CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.

Answer Question  Select Best Answer

What is Pragma Init Exception ? what is difference between user defined exception and init pragma exception. what is the use of it.

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 19th, 2008

You trap a nonpredefined Oracle server error by declaring it first, or by using the OTHERS handler. The declared exception is raised implicitly. In PL/SQL, the PRAGMA EXCEPTION_INIT tells the compiler...

Sachin Kale

• Nov 21st, 2007

Pragma is a compiler Directive that is process at compile time, Not at run time.In pl/sql the Pragma Exception_init tells the compiler to asociate an exception name with an oracle error number.That le...

Answer Question  Select Best Answer

Nand Kumar Sahu

• Jul 13th, 2017

Bulk binding is very useful in performance tunning scenarios. BULK COLLECT and FORALL are tow main key of bulk binding. in bulk binding any DML operation is performed in a single execute so context swiching is not going to happening.

Rajan

• Mar 17th, 2017

Within a PL SQL code if there is any DML statement, it involves context switching. Every time there is a context switch, the cost is more. To handle this we have a functionality called bulk binding w...

Answer Question  Select Best Answer

Radha

• Jul 14th, 2011

Triggers fire when any condition on such trigger is satisfied...but we don't know when that trigger is going to be fired and if it is fired where can we see output of DBMS_output.put_line.

If we want to see output of trigger when it is fired we should mention Raise_application_error in trigger.

pawan_kr2007

 Profile Answers by pawan_kr2007 Questions by pawan_kr2007

• Oct 11th, 2009

If you 'set serveroutput on' and execute the statement which fires the trigger the message will display on prompt otherwise it will not.

Answer Question  Select Best Answer

ravikumarzee@gmail.com

 Profile Answers by ravikumarzee@gmail.com Questions by ravikumarzee@gmail.com

• Mar 8th, 2007

begin for i in 1..10 loopif i 5 then -- do stuff.end if;end loop

madhu

• Feb 9th, 2007

I hope this will work..
declare
i number(2);
Begin
for i in 1.. 10
loop
if (i ==5) then

else
exit;
end loop;
end;

Answer Question  Select Best Answer

kvsubramanian

 Profile Answers by kvsubramanian Questions by kvsubramanian

• Feb 2nd, 2010

Try to Replace "AND A.ITEM like 'OA%'" with " AND substr(A.ITEM,1,2) = 'OA' ".

nagarajbng

 Profile Answers by nagarajbng Questions by nagarajbng

• Oct 3rd, 2009

If you are using the UNION keyword then there is no need of using the distinct keyword in your query or else you can use UNION ALL keywordorBelow I am sending the modified queryIn your query which col...

Answer Question  Select Best Answer

todeepakmalhotra

 Profile Answers by todeepakmalhotra Questions by todeepakmalhotra

• Nov 10th, 2008

You cannot use the database name before the table name like db1.t1.we can only use a user name?and then table name. eg. let we have a user "deepak" then we can write select * from deepak.t1 .? We ca...

oozaifa

 Profile Answers by oozaifa Questions by oozaifa

• Nov 6th, 2008

Type schema name before table name or your id dont have the access to view the data from that table

Answer Question  Select Best Answer

psingla

 Profile Answers by psingla Questions by psingla

• Apr 27th, 2010

In Oracle 9i and above this can be achived in single query (merge query)

ss

• Sep 25th, 2007

Insert the row you want you as if it doesn't exist,?in the exception? write the update statementBegin//Inser the rowExceptionwhen< row already exists>//update the rowEnd

Answer Question  Select Best Answer

ae_sathis

 Profile Answers by ae_sathis Questions by ae_sathis

• Feb 24th, 2008

SQL> create table d ( sl number(4), name varchar2(5)); now do the following operation:SQL> begin insert into d values ('1223123123','geekinterview'); --making insert to fail here commit; exception ...

geetha24

 Profile Answers by geetha24 Questions by geetha24

• Feb 9th, 2007

Whenever the exception is raised ..all the transaction made before will be commited. If the exception is not raised then all the transaction will be rolled back.

Answer Question  Select Best Answer

Abhijit Musale

• Mar 23rd, 2015

We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

veera

• Nov 25th, 2013

It is possible by using Dynamic SQL.
By using EXECUTE IMMEDIATE.

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

Habib Ali

• May 9th, 2016

Stand Alone Proc: Block of PLSQL code but not defined in the data dictionary, hence not reusable

Stored Procedure: Block of PLSQL code Defined in data dictionary, implements reusability. Check in user_source table

Praveen kumar

• Jan 8th, 2016

Both are same

Answer Question  Select Best Answer

ammupriyaa

 Profile Answers by ammupriyaa Questions by ammupriyaa

• Nov 21st, 2008

Ans : A & D

AshfaqueS

 Profile Answers by AshfaqueS Questions by AshfaqueS

• Nov 20th, 2008

A and D

Answer Question  Select Best Answer

Can we issue rollback, commit in the trigger body. if we issue what is the result

Read Best Answer

Editorial / Best Answer

binurajnair  

 Profile Answers by binurajnair Questions by binurajnair

• Member Since Feb-2008 | Feb 28th, 2008

---

      An autonomous transaction is an independent transaction which can be committed independent of other transactions. An autonomous transaction will be committed with out committing the other non-autonomous transactions are committed.

The following exercise will help you to understand the difference between autonomous and normal transactions.

Create a table for the exercise.

create table test_table (a varchar2(50));

Create a non-autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
   INSERT INTO test_table
   VALUES      ('Non Autnomous Insert');

   COMMIT;
END;

Now execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Non Autnomous Insert');

   non_autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Non Autnomous Insert');

   ROLLBACK;
END;

Now query the table

select * from test_table;

As expected 2 rows will be retrieved

A
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert

Now we will see the case of autonomous transaction.

Clear the test_table

delete from test_table;
commit;

Create an autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO test_table
   VALUES      ('Autnomous Insert');

   COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Autnomous Insert');

   autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Autnomous Insert');

   ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autnomous Insert

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

We can use commit and rollback in trigers using autonomous transactions.- An atonomous transaction is an independent transaction initiated by another transaction.- When an atonomous transaction is cal...

arbind1982

 Profile Answers by arbind1982 Questions by arbind1982

• Mar 12th, 2008

we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration sectionCREATE TRIGGER anniversary_triggerBEFORE INSERT ON employees FOR EACH RO...

Improve Answer

How to find the max value in a Sequence?

mstreete

 Profile Answers by mstreete Questions by mstreete

• Sep 30th, 2008

If you don't own the sequence, you may look at all_sequences, which shows sequences in other schemas that you have access to:select max_valuefrom sys.all_sequenceswhere sequence_owner = '&sequence_owner' and sequence_name = '&sequence_name'

nandk.sharma

 Profile Answers by nandk.sharma Questions by nandk.sharma

• Jun 30th, 2008

We can get the MAX value of a sequence by using the data dictionary view 'USER_SEQUENCES' .i.e.  SELECT MAX_VALUE FROM USER_SEQUENCES       WHERE SEQUENCE_NAME='NAME_OF_SEQUENCE'

Answer Question  Select Best Answer

Procedure in package perfomes fastly over normal procedure, Explain.

monoranjang

 Profile Answers by monoranjang Questions by monoranjang

• Jan 24th, 2011

A Storeprocedure is a Set of transactional SQL statements which will do some operation. Storedprocedure is precomplied . When we use sp's in our application it will reduce network traffic because ...

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 8th, 2010

It's true. Procedures in the packages are faster than the stand alone procedures, because all the procedures in the package are compiled once and stored in the SGA at shared pool area. If we call the ...

Answer Question  Select Best Answer