Habib Ali

• May 10th, 2016

PRAGMA is instruction to compiler
AUTONOMOUS TRANSACTION means running an SQL transaction inside a PLSQL block of code independently i.e Making changes to DB regardless of other transactions & their locks/latches

sujata

• Jan 19th, 2016

Pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the PL/SQL block following the pragma as autonomous (independent) from the calling transaction. Pragma can be applied on:- 1) Anonymous PL...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 6th, 2008

---

A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

Pradeep

• Feb 20th, 2016

Basically 5 main types in Oracle Triggers
1. DML Trigger (3 statement * 2 timing * 2 level = 12 types).
2. DDL Trigger.
3. Instead of Trigger (Fired Complex View).
4. System Trigger.
5. Compound Trigger (Introduced by 11g)

tarini sankar das

• Feb 7th, 2014

Actual answer is we have 14 types of triggers in pl sql. For statement level :- 1-before insert 2-before update 3-before delete 4-after insert 5-after update 6-after delete For row level :- 7-before ...

Improve Answer

suvijyo

• Feb 2nd, 2015

"c declare s1 char(10):=name; s2 varchar2(10):=name; begin if s1=s2 then dbms_output.put_line(both are equal); else dbms_output.put_line(both are equal); end if; dbms_ou...

chinnu

• Jan 27th, 2015

Char datatype is fixed,but it cant be reused. eq: char(10) -- elena,first 5 characters will be filled remaining field are padded.
varchar2 datatype fixed,but it can be reused. eq: varchar2(10) -- elena, first 5 characters will be filled and remaining fields are reused.

Answer Question  Select Best Answer

What is Force View. Why Oracle Give Facility to create force view? What is the use of force View

sonam kanungo

• Oct 15th, 2011

As per my knowledge when view is created without base table.then the view is called as forced view.

in this type of view is created with compilation error.

mohan

• Oct 14th, 2011

If we create a view without having base table, that view called as forced view. it will create with compile error. it will be in invalid till base table create.

Answer Question  Select Best Answer

Abdul

• Oct 1st, 2016

We can use DBMS_job.isubmit procedure to schedule the job automatically..

Pran

• Jul 12th, 2016

I am trying this, but it says that procedure name is already exist.

Answer Question  Select Best Answer

What are the advantages of packages over standalone procedure ?

Habib Ali

• May 9th, 2016

Packages can be cached inside the SGA, resulting in faster execution than a normal stored procedure.

abhijit musale

• Feb 18th, 2015

You can wrap/encrypt the package code .

Answer Question  Select Best Answer

sandeshshinde000

 Profile Answers by sandeshshinde000 Questions by sandeshshinde000

• Jun 4th, 2011

begin  DML 1  '  '  '  '  '  DML 10exceptionwhen others thandbms_output('error at location '||dbms_utility.format_error_backtrace);e...

BakulPatel

 Profile Answers by BakulPatel Questions by BakulPatel

• May 7th, 2011

To simplify  Remove exception block. Oracle will show line number with error thrownOther method:beginlocation :=1; dml1 location :=2; dml2exception    when others than&nb...

Answer Question  Select Best Answer

pankti

• Apr 6th, 2012

Nested table Unbounded ...

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 6th, 2008

A Table is a basic unit of storage in oracle.A nested table is a collection type. The main advantage of collections is instead of processing data sequentially, we may process all the date&nb...

Answer Question  Select Best Answer

Explain what is Simple Cursor and What is Parameterized Cursor? And Difference between both??

sandeep

• Feb 1st, 2018

In real time we are using more than one cursor in PL/SQL block. so in this we are sending one cursor values into another cursor so the receiving cursor must be parameterised cursor

Akash Mohan singh

• Dec 26th, 2017

A parameterized cursor are static cursor that can accept passed in parameter values when they are opened. Parameterized can only reference to its own parameter parameterized cursor cannot reference to local variable.

Answer Question  Select Best Answer

Is Dual Table updatable? If we update, will it impact the health of the database?

Habib Ali

• May 10th, 2016

DUAL table is owned by SYS, and is updatable only under SYS account. But never make any changes to DUAL, as it is frequently used by Oracle optimizer to test Execution Plans.

vishal

• Mar 29th, 2016

No we can't update dual table because it is a dummy table. But it is possible to create a table named dual and you can insert, update and delete that table.

Answer Question  Select Best Answer

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 23rd, 2011

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. For example, if program A calls program B and program B calls program A,&nb...

KD09714

 Profile Answers by KD09714 Questions by KD09714

• Jun 26th, 2008

Example of forward declaration in package : CREATE OR REPLACE PACKAGE pkg_CBRef_Seq AS PROCEDURE sp_CBRCreateDropSeq_scd ( p_cod_CountryCode IN country.country_code%TYPE, ...

Answer Question  Select Best Answer

What is the difference between an explicit cursor and select into statement?

neerajalatha

 Profile Answers by neerajalatha Questions by neerajalatha

• Jul 5th, 2010

Select statement can return multiples rows at a time and there exists only one network round trip.Explicit cursors are memory areas which acts as a handle or pointer to context area and allows to fetc...

rashlikesu

 Profile Answers by rashlikesu Questions by rashlikesu

• May 12th, 2010

When select into query returns just one row, Oracle uses implicit cursor to execute and fetch the results for the query.But if the select statement returns more than one row, an Explicit cursor is created to fetch the data.

Answer Question  Select Best Answer

If a procedure is referring a table and if the structure of the table is changed, lets say data type of the any column is changed, then is it possible that the status of the procedure remains valid? Explain

Read Best Answer

Editorial / Best Answer

tar.goyal  

 Profile Answers by tar.goyal Questions by tar.goyal

• Member Since Aug-2010 | Aug 4th, 2010

---

It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.

This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.

tar.goyal

 Profile Answers by tar.goyal Questions by tar.goyal

• Aug 4th, 2010

It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.

anironic

 Profile Answers by anironic Questions by anironic

• Jul 21st, 2010

If PL/SQL stored SP %type and does not explicity declare a datatype, then it is quite possible that Stored procedure remains valid, even after changing the datatype in the table. Ex, you have a declar...

Improve 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

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

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

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 is call by value and call by reference in parameters (IN, OUT, INOUT)?

Read Best Answer

Editorial / Best Answer

lisha.varghese  

 Profile Answers by lisha.varghese Questions by lisha.varghese

• Member Since Nov-2008 | Nov 17th, 2008

---

Three types of parameter modes

1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.

2) OUT parameter mode -This mode is used to return a value to the main block.The value of OUT parameter can change anywhere in the program.

3)IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block.The value of IN OUT parameter can change anywhere in the program.

In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.

In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here  no copying is required.
 
The IN parameter is passe by reference,so we can't change the value of IN parameter inside the module,It acts as a constant,But the OUT and IN OUT parameters are passed by value,we can change the values of OUT &  IN OUT paremeters

samareshp

 Profile Answers by samareshp Questions by samareshp

• Apr 17th, 2009

In Parameter:: call by reference OUT Parameter: call by valueIN OUT Parameter: call by valueif you use the hint NOCOPY  with OUT Parameter and IN OUT Parameter then ::: call by reference

lisha.varghese

 Profile Answers by lisha.varghese Questions by lisha.varghese

• Nov 17th, 2008

Three types of parameter modes1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.2) OUT parameter mo...

Improve Answer

Malar somu

 Profile Answers by Malar somu Questions by Malar somu

• Sep 10th, 2013

Implicit Cursor:- It is Oracle implicit type.. when we fetch only one record then we need to use implicit cursor i.e when ever we performing select query or any dml operations termed as implicit curso...

hira

• Aug 8th, 2013

When ever any query execute by the server in database sever itself create a implicit cursor ,which can not any control to the programmer but in case explicit cursor the programmer has to create it.and used for the data manipulation by following some rule......

Answer Question  Select Best Answer

 Objects  of  type  TABLE are called "PL/SQL  tables", which are modeled as (but  not the same as) database tables,  PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.  Cursors

Read Best Answer

Editorial / Best Answer

rkanth18  

 Profile Answers by rkanth18 Questions by rkanth18

• Member Since Sep-2005 | Nov 27th, 2005

---

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2³¹-1 to 2³¹-1, so you have an awful lot of rows with which to work

 

neerajalatha

 Profile Answers by neerajalatha Questions by neerajalatha

• Jul 5th, 2010

A PL/SQL table is a database table having one column and rows are stored not in predefined order. Associative arrays and nested tables comes under category of PL/SQL. 

babu.sridharan

 Profile Answers by babu.sridharan Questions by babu.sridharan

• Jul 14th, 2008

Example for PL/SQL table:==================In the below block "typlsql" and "ty_plsql2" are PL/SQL tablesdeclaretype ty_plsql is record ( empno number, ename varchar2(50));TYPE ty_...

Improve Answer