-
What will happen after commit statement ?
Cursor C1 is Select empno, ename from emp; Begin open C1; ...
Vivek
- Jun 29th, 2012
After committing first oracle will commit the transaction into redo log file and then Data file. The data block will remain into the memory until the logical reads are going on. Oracle use LRU algorit...
-
What steps should a programmer should follow for better tunning of the PL/SQL blocks?Difference between procedure and function?What is the use of ref cursor return type?
Aniruddha Das
- Jun 11th, 2012
If you want to take care of performance tuning you need to take care of sub-queries as they takes a lot of time to executing in side the pl-sql blocks.
ptmich
- May 28th, 2012
Ref cursor is used to declare a pointer variable. When you return a ref cursor, you are returning a pointer variable.
-
What is nested table in Oracle and difference between table and nested table?
pankti
- Apr 6th, 2012
Nested table Unbounded ...
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...
-
Force View
What is Force View. Why Oracle Give Facility to create force view? What is the use of force View
-
-
Which type of binding does PL/SQL use?
Read Best Answer
Editorial / Best Answer
g_sidhu- Feb 1st, 2008
Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.
How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of database objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of database objects can remain unknown until then.Art11
- Sep 25th, 2011
This is the best answer I found:
Early binding is checking for existence of objects referred in the program unit during the compilation phase. Late binding will not check the existence of objects till the runtimepramodh holla
- Sep 21st, 2010
It uses deferred binding...in the sense it uses both static and dynamic binding.
-
Can we have same trigger with different names for a table?eg: create trigger trig1after insert on tab1;andeg: create trigger trig2after insert on tab1;If yes,which trigger executes first.
MURTHY
- Sep 7th, 2011
the question is very nice. yes we can have 2 triggers with same body but with different names. ex: create trigger trg1 .................... ............................... end; create tri...
Baji Shaik
- Jun 5th, 2011
yes, we cant said upto 10g oracle server executes the triggers randomly,but 11g we are having a feature follows clause,by using this we have to control the execution of triggers explicitly by the deve...
-
What is the diff between %Rowtype and %type?
manoj_lanser
- Jul 20th, 2011
%type is used when we have to define the datatype of a variable similar to another existing column data-type. eg- v_id employess.employee_id%type. In this example we have make a variable v_id similar...
Radha
- Jul 14th, 2011
We use %type and %rowtype in PL/SQL programming part. %Type: The variable will assign the datatype which contains in the column of that particular table we mentioned. Ex: v_eno employee.eno%type; ...
-
2)what is meant by forward declaration in functions?">
1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?
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
- 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, ...
-
What is diff between strong and weak ref cursors
Baji Shaik
- Jun 5th, 2011
Strong ref cursor return value, but seek ref cursor it can't return any value
askvenki
- Jul 20th, 2007
A strong REF CURSOR type definition specifies a return type, but a weak definition does not.DECLARE TYPE EmpCurTyp IS REF ...
-
Suppose thr are 10 DMLs(insert,update,delete ) in the main section of the PL/SQL block .The exception in them is handled as a whole in the exception handling section .....The error may occur in any of this DMLs ,so how can we understand that which DML has failed ??
sandeshshinde000
- Jun 4th, 2011
begin DML 1 ' ' ' ' ' DML 10exceptionwhen others thandbms_output('error at location '||dbms_utility.format_error_backtrace);e...
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...
-
Bulk Data Operations
You are developing a PL/SQL block designed for bulk data operations. When attemping to store table data for multiple rows in a PL/SQL variable, which one of the following choices identifies the mechanism best suited for this task (choose one)? A. cursorB. varchar2C. recordD. table of records
rahulg5211
- Oct 25th, 2010
Table of record type is best option to perform this operation.becouse first thing pl/table has unlimited size.second thing for fetching bunch of record into pl/table,besides of INTO clause we ne...
shukla.prashant76
- Apr 28th, 2010
Answer is D. We need multiple rows on a variable so we use here Table of records .it uses just like as collection of records.Cursor is used for selecting multiple rows from stored table.Var...
-
Status of Procedure
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- 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
- 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
- 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...
-
What is PL/SQL table ?
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- 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 -231-1 to 231-1, so you have an awful lot of rows with which to work
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
- 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_...
-
Explicit cursor and Select into statement
What is the difference between an explicit cursor and select into statement?
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
- 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.
-
When we need to use USING clause in the sql?For example in this below:SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations l USING (location_id) WHERE salary > 10000; Can anyone explain what is mean of USING in this sql statement? or USING concept in brief? Thank you.
hmounir
- Nov 17th, 2009
In natural joins USING clause can be used to specify only those culomns that should be used for an equijion
ravikumarzee@gmail.com
- Mar 9th, 2007
USING is also used while executing Dynamic SQL.e.g.,EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;
-
Indexed Select Statement
How can we find the select statement is indexed or not?
Read Best Answer
Editorial / Best Answer
promisinganuj- Member Since Sep-2009 | Sep 13th, 2009
1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
FROM emp
WHERE emp_name = 'ABC';
2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
FROM emp
WHERE emp_name = 'ABC';
The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.promisinganuj
- Sep 13th, 2009
1. using AUTOTRACESQL> SET AUTOTRACE ON TRACEONLYSELECT * FROM emp WHERE emp_name = 'ABC';2. Using EXPLAIN PLANSQL> EXPLAIN PLAN forSELECT * FROM...
-
Procedure Parameters
What is call by value and call by reference in parameters (IN, OUT, INOUT)?
Read Best Answer
Editorial / Best Answer
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 paremeterssamareshp
- 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
- 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...
-
Analytical functions
What are analytical functions and how are they used?
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 ...
-
Oracle triggers
Can we issue rollback, commit in the trigger body. if we issue what is the result
Read Best Answer
Editorial / Best Answer
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 codeDECLARE
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 tableselect * from test_table;
As expected 2 rows will be retrievedA
--------------------------------------------------
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 rowA
--------------------------------------------------
Autnomous InsertConclusion
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
- 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
- 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...
PL/SQL Interview Questions
Read Best Answer
Ans