-
-
What is the syntax for using triggers?
vaibhav Tyagi
- Sep 26th, 2007
FOR- Sql ServerCreate trigger TestTriggeron tableaNameinstead of insertasbegin transactiondeclare @aid intset @aid=0select @aid = max([columnName]) from tableNameif @aid 0--do operationcommit transaction
madhuk17
- Feb 18th, 2007
Create or replace trigger {Before/After} {Insert/Update/Delete} on [Referencing [ New as ] [ Old as ][For each row [When ]]
-
1.What is bulk collect?2.What is instead trigger3.What is the difference between Oracle table & PL/SQL table?4.What R built in Packages in Oracle?5.what is the difference between row migration & row changing?
askvenki
- Jul 20th, 2007
rowchaining works for varchar2 datatype which will allocate memory dynamicallysuppose thier is column name varchar2(20);first value-- venki which is 5changed to venki12? which is 7then? oracle will cr...
rampratap409
- Oct 13th, 2006
Hi Shashank,Can you explain instead off trigger, DML Trigger and views..
-
-
HiWhile creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)?
Baji Shaik
- Jun 5th, 2011
Varchar2(80) accept upto 80 characters,varchar2(80 byte) accept upto 80*8=640 characters
psingla
- Apr 28th, 2010
If system parameter NLS_LENGTH_SEMANTICS is set to defaultthen no differenceIf system parameter NLS_LENGTH_SEMANTICS is changed from default behavior BYTE to CHAR.then VARCHAR2(80) means VARCHAR...
-
Why DUAL table is not visible?
Read Best Answer
Editorial / Best Answer
krishnaindia2007- Member Since Sep-2007 | May 13th, 2008
DUAL is a part data dictionary and owned by SYS. You should not make modifications to this table.
It contains only one row and one column of VARCHAR2 datatype.
Used to refer an object which does not have any pysical reference in database table.
Ex:- Select sysdate from dual.
Aditya Kumar
- Dec 17th, 2017
If it contains only one column with varchar2 data type then how can it return a date through sysdate.
krishnaindia2007
- May 13th, 2008
DUAL is a part data dictionary and owned by SYS. You should not make modifications to this table. It contains only one row and one column of VARCHAR2 datatype.Used to refer an object which does not have any pysical reference in database table.Ex:- Select sysdate from dual.
-
What are optimizer hints?
Art11
- Sep 27th, 2011
A hint is an instruction to the optimizer. You can use optimizer hints with SQL statements to alter execution plans.
muraligrandhi
- Jun 23rd, 2011
The optimizer hints determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified ...
-
Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers
g_sidhu
- Jan 31st, 2008
When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explic...
Shefali
- Aug 16th, 2006
"WHERE CURRENT OF" clause is used for updating or deleting rows selected by the cursor with FOR UPDATE cluase.It explicitily locks the row for updating or deleting.
-
What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.For triggers related to INSERT only NEW.column_name values only available.For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.For triggers related to DELETE only OLD.column_name values only available.
krishnaindia2007
- May 6th, 2008
OLD and NEW are two virtual tables available during database trigger execution.UPDATE statement has access to both old and new values.INSERT statement has access only to new...
-
What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
g_sidhu
- Jan 31st, 2008
PRAGMA EXCEPTION_INIT statement associate the declared exception with the standard Oracle server error number. PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle er...
-
Give the structure of the procedure ?
PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end;
krishnaindia2007
- May 6th, 2008
[CREATE [OR REPLACE]] PROCEDURE procedure_name[(Optional Parameters)] {IS | AS} [PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
KiranKW
- May 3rd, 2007
Create or replace procedure proc_name (optional parameters)as beginexecutable statements;exception handlingend;end proc_name;
-
How PL SQL is different from T-SQL
psingla
- Apr 28th, 2010
PL/SQL is procedural language.It is built upon sql with extra added functionalities.It is used by ORACLE.robust and consistentexception handling is great the concept of package is greatdisallow bad co...
Yaseen
- May 12th, 2007
SQL (Structures Query Language)PL/SQL (Procedure Language / SQL) is the Oracle's version with lots of additional proprietary functions.T-SQL : Transact-SQL is Microsoft's proprietary version of SQL.
-
What is the difference between database trigger and application trigger?
g_sidhu
- Feb 6th, 2008
Application trigger: Fires whenever an event occurs with a particular application Database trigger: Fires whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database
Sumit Tiwary
- May 2nd, 2007
Database triggers are backend triggeres and perform as any event occurs on databse level (ex. Inset,update,Delete e.t.c) wheras application triggers are froentend triggers and perform as any event taken on application level (Ex. Button Pressed, New Form Instance e.t.c)Rgds,Sumit Tiwary.
-
When using a count(distinct) is it better to use a self-join or temp table to find redundant data, and provide an example?
manish.baehal
- Apr 5th, 2009
COUNT(Distinct) is better than using Self Join to find redundant data as it is faster and shorter. Beside this COUNT(Distinct) provide more flexibility.
-
What are the characteristics of autonomous transactions?
ajeeteng
- May 5th, 2010
Autonomous transactions refer to the ability of PL/SQL temporarily suspend the current transaction and begin another transaction. The second transaction is known as an autonomous transaction, an...
uday
- Jun 29th, 2007
Autonomous transaction defined and executed?independent of calling program. u need to define PRAGMA AUTONOMOUS_TRANSACTION at declare section. stored procedure, function can be defined as autonomous transaction.
-
What is the difference between private packages and public package . what is the difference in declaration of these 2 packages.
mosam
- May 21st, 2008
U can not create thr private packages in oracle becoze packages can not be nasted
KiranKW
- May 3rd, 2007
hi , i dont think there is something called as public package / private package. But yes the methods defined in a package can either be public or private.Below is a simple expln.Variables and co...
-
Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain specific business reason of it's use
berly.george
- Dec 1st, 2009
Instead of triggers are used:1) To control the DML operations on Views(Insert/Update/Delete)2) Makes the non updatable views updatable
hindol_saha
- Apr 9th, 2007
Conceptually, INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger...
-
-
What are Hash, Nested loop and merge-sorted Join.
psingla
- Apr 27th, 2010
There are three types of alogrithm used by SQL compiler for joining purpose:1) Nested join algo2) Hash join algo3) Merge join algoComplier choose any one of these algo based on the best suited for per...
norman
- May 8th, 2007
In hash join hash table gets build using the inputs. Nested loops as the name implies uses looping construct strategy for storing and retrieving. In terms of performance comparison while retrieving da...
-
What is PRAGMA RESTRICT_REFERENCES:
saishradha
- Dec 4th, 2008
Pragma RESTRICT_REFERENCES is a compiler directive.It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.There are 4 values for it.WNDS,RNDS,WNPS,RNPS.ThanksJai Sainath
Kullai Naidu
- Mar 5th, 2007
By using pragma_restrict_references we can give the different status to functions,Like WNDB(WRITE NO DATA BASE),RNDB(read no data base),Write no package state,read no packge state.W
PL/SQL Interview Questions
Read Best Answer
Ans