-
Performance Issue: Gathering Statistics- dbms_stats
What are the advantages of Gathering Statistics?
-
Procedure Vs Function
Explain when do we use a Procedure and when do we prefer using functions.
-
Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.PL/SQL uses Implicit Cursors for queries.User defined cursors are called Explicit Cursors. They can be declared and used.
krishnaindia2007
- May 6th, 2008
Cursors are of two types1. Implicit Cursors: - Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used ...
Lavanya Chowdary
- May 6th, 2007
There r two types of cursors in the pl/sql1.Implici 2.explicit For the select stmt which is retrieving single record or all the dml operations in the pl/sql program system automatically allocates...
-
What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
fancyoracle
- Aug 15th, 2007
There are two ways of processing a cursor output. These mainly depend on the type of cursor used. Cursors can be1) Static : Declared in declarations section with a defina...
geetha24
- Feb 13th, 2007
FOR record_index IN [cursor_name | (SELECT statement)]LOOP executable_statement(s)END LOOP;
-
What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex ...
KSPRADEEP
- Oct 17th, 2012
Trigger is data base object .Trigger is block of code it is executed automatically when dml operations are fired .
by using trigger we can do the auditing and perform a operation(modifications) on a tables (using dml operation)krishnaindia2007
- May 6th, 2008
A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens. Data auditing , Implementing complex business rules, security are main uses of database triggers.
-
Where the Pre_defined_exceptions are stored ?
In the standard package. Procedures, Functions & Packages ;
-
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
divyesh
- Oct 13th, 2007
Ref cursors are used when you want cursor to hold any no. of columns in the row.If you are not sure about row type at complile time you use ref cursors.Sys_ref_cursors used when you want cursors to be passed from one procedures to another procedures or functions.
ddkdhar
- Feb 13th, 2007
If we declare in package body then what is difference b/w cusrsor..........?how many types of refcursors..............?whata re the advantages of ref cursors....................?
-
What is dense_rank function and it's usage ?
Teju_hassan
- Apr 20th, 2011
See the below example to understand better.select dense_rank,rank,salfrom empsorder by sal descdense_rank|rank|sal1 &...
g_sidhu
- Feb 5th, 2008
Rank Function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings.
-
How do you use cursor dynamically?
askvenki
- Aug 13th, 2007
By using cursor parameters we can pass the value dynamically. So that cursor can fetch data as per parameter
gopal
- Jan 11th, 2006
Dear sir, declare dep_num number; cursor c(x number) is select * from emp where deptno...
-
What is meant by tuning and tk proof?
vinit
- Jul 12th, 2007
tkproof is a oracle utility. it gives information about following parameters.********************************************************************************count = number of t...
madhug56
- Jun 12th, 2007
Can you tell me something more about tk proof?? I mean how do we give input and what is the output?
-
What happens when DML Statement fails?A.User level rollbackB.Statement Level RollbackC.Sustem Level Rollback
g_sidhu
- Feb 1st, 2008
If a single DML statement fails during execution of a transaction, it s effect is undone by a statement-level rollback, but the changes made by the previous DML statements in the transaction are not discarded. They can be committed or rolled back explicitly by the user.
Chiru
- Jun 21st, 2006
When a DML statement executes (fails/sucess) an automatic Commit is executed. Eg : Create a table t1. Insert a record in t1. Then again to create the same object t1.
-
Is it possible that we can make a query to search the data
Umair Tariq">Hello,I need some info, that is it possible to find or make query ,that if we know the data stored in a table but don't know the field name in which the required data is stored.ExampleTable having fields like columnA, columnB, columnC, columnD,..........columnN and all have the same dataType. and you dont know in which field your data exists.suppose some data like e.g. "BackMan" exists in any Field of the table but we don't know in which Field this information is store.Is it possible that we can make a query to search the dataUmair Tariq
-
What is a purity level? How it is should be taken into consideration when your writing any database objects i.e., trigger,function, procedure etc.,
asrafpa
- Sep 26th, 2010
The function purity level defines what structures the function reads or modifies. Following are the purity levels 1) WNDS - Writes No Database State i.e. Function does not modify any database tables ...
pari
- Mar 15th, 2007
pragma_restrict_references is a compiler directive to check the purity level of functions used in packages.The restriction on a function that it cannot do update, insert or delete when used in select ...
-
How can one view all the procedures,functions,triggers and packages created by the user
g_sidhu
- Feb 1st, 2008
SELECT object_name, object_typeFROM user_objectsWHERE object_type in ('PROCEDURE','FUNCTION')ORDER BY object_name;
Sphurti
- Aug 14th, 2006
Basically, here USER_OBJECTS will have all the objects which are created/own by a particular user whereas ALL_OBJECTS will have information of the objects for which a user has access, it may or may not be created by that user.
-
How can i get the procedure's, function's name from a package if it is wrapped(both spec & body).
Gaurav_Vashishtha
- Jul 27th, 2010
You can get the source code of the wrapped pakage from dba_source table.
ravikumarzee@gmail.com
- Mar 9th, 2007
Desc e.g., If u want to know the procedures and functions in a package called xyz thendesc xyz;
-
I want to insert the following information in userAction table:Which user execute which query on which date?the userAction table contains the foolowing attributes:USER DATE QUERYplease write to me how to resolve this problem?
harinicm
- Dec 11th, 2009
Use AUDIT is used to track the occurrence of the sql statements in the subsequent user sessions (not the current session). The audited record contains:Name of the userType of the operationObject...
-
-
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 *...
-
-
State the advatage and disadvantage of Cursor's
krishnaindia2007
- May 7th, 2008
Whenever we execute DML statements oracle assigns a work area called private sql area to store information. Cursors meachanism allows us to name that private sql area there by access information ...
Venkat9999
- Sep 22nd, 2006
I think your implict and explict cursor concent needs a correction:EXPLICIT CURSORS are used, when you are getting more than a single row of data that needs to be processed further. Explicit cur...
PL/SQL Interview Questions
Ans