Give an example for calling procedure with user and system exception

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 29th, 2008

Simple example for predefined exception CREATE OR REPLACE PROCEDURE TEST_PROC AS A NUMBER ; BEGIN A := 'TEST'; DBMS_OUTPUT.PUT_LINE('THE VALUE OF A = '||A); EXCEPTION WHEN VALUE_ERROR ...

ravenkumar

 Profile Answers by ravenkumar Questions by ravenkumar

• Mar 17th, 2008

set serveroutput on;create or replace procedure proc1 (emp_id in emp.employee_id%type, emp_na out emp.last_name%type, emp_sal out emp.salary%type, emp_job out emp.job_id%type) is &...

Answer Question  Select Best Answer

How will implement the PL/SQL tables?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 29th, 2008

- Pl/sql is in memory representation of a table.- It is just a data type. ( Collection type)- It may store any number of rows from table.Here is  a simple exampleCREATE OR REPLACE PROCEDURE TEST_...

tonyrobert

 Profile Answers by tonyrobert Questions by tonyrobert

• Mar 27th, 2008

INDEX By Tables composed of two components1.? Primary key of data type BINARY_INTEGER2.? Column of scalar or record data type.Since this table is unconstrained, it can increase its size dynamically....

Answer Question  Select Best Answer

ashishdixit

 Profile Answers by ashishdixit Questions by ashishdixit

• May 29th, 2008

desc has two meanings and depends on the usage.when used in sql plus as in the below stt.desc <tablename>;it is taken as a sqlplus keyword and describes the table, dispalying column, constraints...

Sebastian Thomas

• Aug 17th, 2007

DESC will display the values in DESCending order when used in an ORDER BY clause.For example:SELECT * FROM empORDER BY empno DESC;

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

What exactly the difference between 'TABLE OF' and 'REF CURSOR' in PL/SQL?For what purposes these both might be used?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

A ref cursor is a dynamic cursor in which the contents of cursor can be changed dynamically at run time depending upon our requirement.A ref cursor is basically a data type. A variable declared based ...

Answer Question  Select Best Answer

What are the types of cursors apart from explicit and implicit cursors ? Explain when and where they are used ?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

There are only two types of cursors in oracle Pl/sql.1. Implicit cursors2. Explicit cursors    We can define an explicit cursor as static or dynamic (REF CURSOR).   ...

Manoharan

• Oct 25th, 2007

1. Implicit Cursor2. Explicit Cursor3. Ref CursorImplicit Cursor like SQL%rowcount,SQL%rowtype...Explicit   cursors are user defined cursorRef Cursor is object name of the cursor type. Its mainly used for dynamic purpose

Answer Question  Select Best Answer

Database has 100 tables and some of the tables dont have any data. I want to pullout those table names from database in Oracle.

sr_2407

 Profile Answers by sr_2407 Questions by sr_2407

• Jun 4th, 2008

Select  table_name ,num_rows from user_tableswhere num_rows=0

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

Analyze and perform statistics on whole schema.  This will populate the statistics details about each table.  You can pull NUM_ROWS column from all_tables to check whether it ...

Answer Question  Select Best Answer

Hi,There is problem with ult_file.fopen. When I run from the server side, there is no issue with utl_file and it is working fine.If I run the same procedure from Client machine, I am facing problem. It throws an exception Invalid_operation.I am using Oracle 8.2 and Unix server.Can you please let me know what change needs to be done? Is this related to permission issue.Thanks & Regards,Ananth P

stshekhar

 Profile Answers by stshekhar Questions by stshekhar

• Jun 22nd, 2008

the parameter set in configuration file init.oraand UTL_FILE_DIR belongs to the directory structure in oracle server machine. And the directory structure should be on same machine as oracle server. Th...

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 23rd, 2008

Pragma is a compiler directive. It passes information to compiler. These are processed at compile time not at runtime.             &nbs...

rajakumar_na

• Nov 2nd, 2007

Pragma is a compiler directive. Using SQLCODE & SQLERRM we know that which ERROR code is associate to what error.

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 23rd, 2008

Collection is an ordered group of elements all are of same time. Each element in the collection has unique subscript to identify its position in the collection.Pl/sql offers three collection type...

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

The following bullet points can be referred to when deciding what collection best suits a particular solution.VarrayUse to preserve ordered list Use when working with a fixed set, with a known number ...

Answer Question  Select Best Answer

How to skip columns while loading data with SQLLDR

lokeshRao

 Profile Answers by lokeshRao Questions by lokeshRao

• Jun 23rd, 2008

Specify Filler Load datatruncate into table empfields treminated by'|'(field1,field2 filler,field 3)

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Oct 20th, 2006

You should put your question here.  This forum is not a library.  Please take care.

Answer Question  Select Best Answer

How to create temporary table? Give some example

ravi.8311

 Profile Answers by ravi.8311 Questions by ravi.8311

• Jun 28th, 2008

Create table temp as select*from base table where 1=1;

venkat82.k

 Profile Answers by venkat82.k Questions by venkat82.k

• Mar 20th, 2008

Hi guys........Temporary tables means which tables are hold data temporarlyi mean these holds the data certain time mainly only that session.exp:-create global temporary table employ(emp_id number(10)...

Answer Question  Select Best Answer

Write a PL/SQL fuction named say TRUN, which does the same work as the oracle predefined function "TRUNCATE".

ajaz_11i

 Profile Answers by ajaz_11i Questions by ajaz_11i

• Jul 9th, 2008

It will truncate/delete all the rows in the table emp very fast as compared of delete command, but the table must have no Foriegn keyMr. Ajaz Ahmad Kumar

sgbang

 Profile Answers by sgbang Questions by sgbang

• Jul 7th, 2008

Hi,Please check out this function:create or replace function trun(p_table_name varchar2) return number asbeginexecute immediate ' truncate table '|| p_table_name;return 0 ;end; /How to ex...

Answer Question  Select Best Answer

How many columns you can create in table from this type "long raw"?

krishnaveni_g

 Profile Answers by krishnaveni_g Questions by krishnaveni_g

• Jul 17th, 2008

We can use only one long column in a table

madhupriya.nukala

 Profile Answers by madhupriya.nukala Questions by madhupriya.nukala

• Jun 25th, 2008

Only single column is allowed per a table of data type LONG or LONG RAW

Answer Question  Select Best Answer

How can i choose which query is best?i.e On which factors i can choose the best query?

madanuprashanth

 Profile Answers by madanuprashanth Questions by madanuprashanth

• Jul 18th, 2008

By forcing the SQL engine to use indexes.If query is too much nested then break it into two or three queries using temp tables and then write one query by joining these temp table to perform the task.

Answer Question  Select Best Answer

PraGop

 Profile Answers by PraGop Questions by PraGop

• Jul 21st, 2008

Can u please give more explanation on this NOCOPY parameter

raghav_sy

 Profile Answers by raghav_sy Questions by raghav_sy

• Jan 15th, 2007

hi one more thing i wan to add abt NOCOPY, it is that its a hint to compiler, so its compiler wish whether to follow this thing or not, so before using this just go through the conditions when this th...

Answer Question  Select Best Answer

What are analytical functions and how are they used?

singh.neerajin

 Profile Answers by singh.neerajin Questions by singh.neerajin

• Jul 22nd, 2008

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and ...

Answer Question  Select Best Answer

How to fill a refcursor with dataset in C#.i want to pass a dataset as parameter and fill a refcursor with the same dataset.

viv_mca

 Profile Answers by viv_mca Questions by viv_mca

• Jul 31st, 2008

you need to use ODP.Net connector.In OracleCommand object add OracleParameter object with following propertiesOracleDbType = OracleDbType.RefCursor;Direction= ParameterDirection.Output;give the name of your stored procedures's refcursor parameter name

Answer Question  Select Best Answer

binurajnair

 Profile Answers by binurajnair Questions by binurajnair

• Aug 2nd, 2008

We can overload functions/procedures inside packages.

jagadeesh9

 Profile Answers by jagadeesh9 Questions by jagadeesh9

• Feb 24th, 2008

Package: It is an collection of related variables, cursors,procedures and functions stored at one location. They supports OOPS features like function over loading, encapsulation.ADV:   ...

Answer Question  Select Best Answer