-
How to handle exception in Bulk collector?
mariashylaja
- Aug 12th, 2006
During bulk collect you can save the exception and then you can process the exception.Look at the below given example:DECLARE TYPE NumList IS TABLE OF NUMBER; num_tab NumList := NumList(10,0,11,1...
mariashylaja
- Aug 12th, 2006
DECLARE TYPE NumList IS TABLE OF NUMBER; num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1); errors NUMBER;BEGIN FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS DELETE *...
-
Dear friend, my question is assume that we have created on table,on that table we are creating one view and one synonym know i have drop the table then status of view is invalid but status of synonym is not invalid pls give me the answer
gangadharam.p
- Jan 6th, 2007
hi, when u do so the view gets error and synonym becomes invalid. If u create a table again with same name but with different cols the synonym will work but view wont.bcz view is the stored...
raghgu
- Nov 16th, 2006
Hi friend, As per RDBMS rule we cannot have invalid synoym, but we can have a invalid view.
-
-
-
-
-
-
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...
-
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 ...
-
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...
-
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...
-
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;
-
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.
-
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_...
-
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...
-
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...
-
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...
-
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 ...
-
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 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; ...
PL/SQL Interview Questions
Read Best Answer
Ans