-
TCL Statements in Trigger
Why are we not supposed to include TCL statements in a trigger?
manveshv
- Dec 30th, 2009
The trigger is fired when an event occurs while manipulating the data in table.So the trigger applies a lock on it restricting others to modify it.Locks are released when a TCL command is executed. Th...
Vansat
- Jun 16th, 2009
We cannot use TCL statements in triggers, because using them violates the integrity of the transaction. But if we want TCL statements to be used in triggers then that can be implemented by using Auton...
-
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...
-
Pipe Function and Mutating Trigger
1) Why we use pl/sql array rather than cursor?2) In which condition we use function overloading? 3) What is pipe function?4) What is the mutating trigger? What should be the condition?
renu_db_dev
- Jul 2nd, 2010
1) Why we use PL/SQL array rather than cursor?PL/SQL array is a collection through which we can iterate just the way we can through cursor. But in case of Cursor the context switching is for every row...
avis_sarkar
- Oct 14th, 2009
Triggers are PLSQL block or Procedures which get executed whenever:1) an INSERT, DELETE or UPDATE happens on a table. 2) DDL like ALTER or CREATE happens on a table. 3) an INSTEAD OF trigger on a view...
-
How to identify missing records in a single-column table?
How to identify missing records in a single-column table ?Column consists of numbers in a ordered manner but the some numbers are deleted from the table in random manner and need to identify those rows.p.s. This table consist of only 1 row.
-
Where would you use implicit & explicit cursors?
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......
-
Stored Procedure Vs Function
What is the impact of using a stored proc or a function on the performance? Which has a better performance? Also, why is it not possible to use DML in the functions called from a select query?
-
Transaction management in triggers
Can we give COMMIT or ROLLBACK within a trigger?
-
Compiling Specification Part and Body Part
If have 5 procedures in my specification part and used 4 in my body part while compiling will it throw error or not?
-
What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
g_sidhu
- Feb 16th, 2008
PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
Me
- Oct 5th, 2007
DeclarationBeginException HandlingEnd
-
What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part. Datatypes PL/SQL
satyam_Ora
- May 14th, 2008
All has given the right answer and I am repeating the same.There are the three component of PL/SQL program block1) Declarative( Where you can decalre the variable which would be used as a part of exce...
g_sidhu
- Feb 16th, 2008
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
-
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_...
-
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are fetched. % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.These attributes are proceeded with SQL for Implicit Cursors...
Lakshmi84
- Jul 31st, 2009
%ISOPEN - To check whether cursor is open or not.Returns true if cursor?or cursor variable is open or not otherwise?false.%FOUND - To check wheteher cursor is found or not. Returns true if fetch re...
samareshp
- Jul 30th, 2009
There are five cursor attributes: %isopen, %found, %notfound, %rowcount and %bulk_rowcount.%isopenWith %isopen it is possible to test whether a cursor was opened: %found%found returns true when the la...
-
What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line What is a package ? What are the advantages of packages ?
krishnaindia2007
- May 7th, 2008
Definining two or more procedures with same name is called overloading of procedures. But they must differ in no or order or datatype family of formal arguments.
g_sidhu
- Jan 31st, 2008
Overloading: Multiple subprograms of the same name• Enables you to use the same name for different subprograms inside a PL/SQL block, a subprogram, or a package• Requires the formal paramete...
-
Can e truncate some of the rows from the table instead of truncating the full table.
Vishal R Bhadange
- Aug 12th, 2012
Yes.
Yes we can truncate some of the rows from the table, But table should be partitioned & all the rows to be deleted should be present in one single partition... -
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 the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?
g_sidhu
- Feb 1st, 2008
Statement-level: Part of a transaction can be discarded by an implicit rollback if a statement execution error is detected. If a single DML statement fails during execution of a transaction, it s effe...
kameshakundy
- Nov 13th, 2006
Dear Kamini,May I know what do you mean by PG
-
What is the difference between In, Out, InOut Parameters. Can we pass value or reference or both to the In Out Parameter.
saishradha
- Nov 27th, 2008
Here is the answer in 1 lineIN-- value is used in a program. It cannot be a variable .COuld be literal,expression,value .OUT-- value is returned back from the progr...
raghav_sy
- Jan 15th, 2007
hi friends,To make it simple, keep in mind,IN : It is a CONSTANT in the sub-program and u can not modify its value in sub- program. if its value is modified in the s...
-
What is autonomous Transaction? Where are they used?
shinehere
- Feb 24th, 2008
An autonomous transaction is a transaction that is started within the context of another transaction, called the parent transaction, but is independent of it.An autonomous transaction can be committed...
g_sidhu
- Feb 18th, 2008
A autonomous transaction starts with the first sql statement of the pl/sql block and ends with a commit. It starts within the context of an another transaction called parent transaction and independen...
-
Why do we need to create a force view?what is its purpose?give some examples?
askvenki
- Jul 19th, 2007
The force view can be created before base table is created .when the table is createdit is automatically activated.we can create base table with any structure
Mohamed Irfan
- Dec 26th, 2006
Hi, Force views are created in real time environment as per the business needs because of two specific reasons1. If the structure of the base table is not completely known (ie) ...
-
How do i write a function that returnsmore than 1 records from a tablefor example in sql server there is a solution as create function f1() return table asselect * from emphow do i do the same in oracle. plz help
rajivgupta780184
- Nov 24th, 2008
If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for ...
pardhu323
- Jul 20th, 2008
create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for select * from emp; return(r1); end;call the funtion as :select f1 from dual;then it returns :CURSOR STATEMENT :...
PL/SQL Interview Questions
Read Best Answer
Ans