Sudhir Sorout

 Profile Answers by Sudhir Sorout Questions by Sudhir Sorout

• Feb 5th, 2008

Yes when we truncate instead of delete, for deleting records , truncate set the HWM to 'zero' but if we use only delete then HWM remain same ......Sudhir Sorout

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 2nd, 2008

Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.You cannot release space below the high water mark (even if there is no data ...

Answer Question  Select Best Answer

dineshchaini

 Profile Answers by dineshchaini Questions by dineshchaini

• Sep 9th, 2006

Dear Maria...   I traied with the DBA previlage but... again the same prob.. proc created but table is not thea... ok I tried u r proc and mail back to you...

tdas2004

 Profile Answers by tdas2004 Questions by tdas2004

• Sep 7th, 2006

You are right Maria Antony, but the question is, why is it not possible to create the table thru the procedure while you can create the sametable using create table SQL command.The answer is, to creat...

Answer Question  Select Best Answer

madhaviguthula

 Profile Answers by madhaviguthula Questions by madhaviguthula

• Mar 8th, 2007

Hi Hemant, Please go through the following code for better understanding.First, create and populate a test table with a concatenated index.CREATE TABLE test_objects ASSELECT * FROM all_objects;CREATE...

hemant.agarwal03

• Mar 8th, 2007

Hi Madhvi..How we will use that one special feature?Regards,Hemant

Answer Question  Select Best Answer

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 8th, 2008

Sql> update set col_name='value' where rownum<=500;Note: rowid will not work instead of rownum for updating the 500 records.

askvenki

 Profile Answers by askvenki Questions by askvenki

• Jul 20th, 2007

hi guys i think rownum work only for at the time of select statement

Answer Question  Select Best Answer

There are 2 tables, A and B having 3 rows each. What will be result on executing the following query? : SQL> select * from A,B

Raju TT

• Jun 11th, 2013

Cross join -cartesian product :Table a no. of rows (3) * Table b no.of rows (3)= cross join query returns 9 rows

Code
 

Arpit Agrawal

• May 24th, 2013

Here simply answer would be 6. Since a is having 2 rows and b is having 3 rows. And we go for Cartesian join, which means 3*2=6.

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