-
What happens when commit is given in executable section and an error occurs ?please tell me what happens if exception block is committed at the last?
ae_sathis
- Feb 24th, 2008
SQL> create table d ( sl number(4), name varchar2(5)); now do the following operation:SQL> begin insert into d values ('1223123123','geekinterview'); --making insert to fail here commit; exception ...
geetha24
- Feb 9th, 2007
Whenever the exception is raised ..all the transaction made before will be commited. If the exception is not raised then all the transaction will be rolled back.
-
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...
-
Oracle - Could not find the Program unit
While i compiled my code , it got compiled but while running my application i got the Oracle error "Could not find the Program unit ? "Can any one tell exactly why this happens.I had validated all the objects and even released all the Locked objects . But still this error persisted .
binurajnair
- Feb 20th, 2008
This error can occur in the following scenario also. The application has a constant connection with the database and a package which the application uses gets recompiled. And the recompiled package co...
raj_imr2000
- Jan 15th, 2008
Hi,ORA-06508:PL/SQL: could not find program unit being calledCause:An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or ...
-
What is the disadvantage of out paramter in functions
g_sidhu
- Feb 19th, 2008
We cant use functions with OUT parameter in sql statements because there is no way for you to declare a variable in a SQL query and in order to call an IN OUT -- you need a local variable (to supply a value to be read from and to supply a place to put the out value into).
ramaswamy
- Sep 26th, 2007
Functions with "out" parameters?cannot be used in?SQL statments however I disagree with you?rajeev because functions without parameters can also be called in select statments as long as they havea return()?(Eg: SYSDATE)
-
Pragma Init Exception
What is Pragma Init Exception ? what is difference between user defined exception and init pragma exception. what is the use of it.
g_sidhu
- Feb 19th, 2008
You trap a nonpredefined Oracle server error by declaring it first, or by using the OTHERS handler. The declared exception is raised implicitly. In PL/SQL, the PRAGMA EXCEPTION_INIT tells the compiler...
Sachin Kale
- Nov 21st, 2007
Pragma is a compiler Directive that is process at compile time, Not at run time.In pl/sql the Pragma Exception_init tells the compiler to asociate an exception name with an oracle error number.That le...
-
What is the difference between database trigger and schema trigger?
g_sidhu
- Feb 19th, 2008
Triggers on system events can be defined at the database or schema level. A trigger defined at the database level fires for all users, and A trigger defined at the schema or table level fires only when the triggering event involves that schema or table.
rampratap409
- Dec 5th, 2006
In database trigger: Trigger for a table /view is a database triggerlike ( before , insert * update, delete, insert * row level, statement level)2 * 3 * 2 = 12 + instead off tr...
-
HI,What is Flashback query in Oracle9i...?
g_sidhu
- Feb 19th, 2008
Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time...
joybanerjee
- Dec 26th, 2007
when ever a table is dropped the database does not release the space assotiated with it the table is kep...
-
What is pragma? can any one give me the example of autonomous Transaction ?can we change the order of procedure parameter while calling procedure?
g_sidhu
- Feb 18th, 2008
A pragma is compiler directive. Pragmas are processed at compile time, not at run time. They pass information to the compiler.
g_sidhu
- Feb 2nd, 2008
Using Named & Combination Parameter passing Methods you can change the order of parameter in a procedureNamed: List actual parameters in arbitrary order by associating each with its corresponding ...
-
What is Mutation of a trigger? why and when does it oocur?
g_sidhu
- Feb 18th, 2008
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement. A table is not considered mutating for STATEMENT triggers. The triggered table itself is a mutat...
Arun Arumugam
- Jul 1st, 2006
A table is said to be a Mutating table under the following three circumstances1) When u try to do delete, update, insert into a table through a trigger and at the same time u r trying to select the sa...
-
What type of binding is PL/SQL?
g_sidhu
- Feb 17th, 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 assig...
chaitu
- May 27th, 2006
pl/sql uses early binding.
-
What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.Some composite data types such as RECORD & TABLE.
g_sidhu
- Feb 16th, 2008
ScalarCHARVARCHAR2 LONGLONG RAWNUMBER [(precision, scale)]BINARY_INTEGERPLS_INTEGERBOOLEAN Composite TypesRECORDTABLEVARRAYLOB TypesBFILEBLOBCLOBNCLOBReference TypesREF CURSORREF object_type
rampratap409
- Oct 26th, 2006
Following are the datatype supported in oracle PLSQLScalar TypesBINARY_INTEGERDECDECIMALDOUBLE PRECISIONFLOATINTINTEGERNATURALNATURALNNUMBERNUMERICPLS_INTEGERPOSITIVEPOSITIVENREALSIGNTYPESMALLINTCHARC...
-
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 is the difference between database server and data dictionary
psiva_oracle
- Feb 12th, 2008
Hi,Database Server:It's contains the collection of objects (Table,procedure,trigger,sequence,index,etc)it's also contains the Schema and user detailswe can view the details which are in t...
g_sidhu
- Feb 4th, 2008
Database Server: A computer in a LAN dedicated to database storage and retrieval. The database server is a key component in a client/server environment. It holds the database management system (DBMS) ...
-
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.
-
What is HIGH WATERMARK?I got to know that it is reset when the TRUNCATE command is executed on a table.
Sudhir Sorout
- Feb 5th, 2008
Yes when we truncate instead of delete, for deleting records , truncate set the HWM to 'zero' but if we use only delete then HWM remain same ......Sudhir Sorout
g_sidhu
- Feb 2nd, 2008
Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.You cannot release space below the high water mark (even if there is no data ...
-
Hi,Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?Thanks.
g_sidhu
- Feb 4th, 2008
Triggers on system events can be defined at the database or schema level. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only whe...
Nasim
- Dec 17th, 2006
Hiif we have created a view that is based on join codition then its not possibe to apply dml operations like insert, update and delete on that view. So what we can do is we can create instead off trig...
-
What is difference b/w stored procedures and application procedures,stored function and application function..
g_sidhu
- Feb 4th, 2008
Application procedures or functions: Named PL/SQL blocks stored in an Oracle Forms Developer application or shared library; can accept parameters and can be invoked repeatedly by name and are availabl...
Nishant Patel
- Jun 13th, 2007
The difference between the two can be categorised via: Stored subprograms ans Application subprograms1) Stored subprograms: These are coded and compiled from?within the Oracle database via SQL Plus e...
-
Difference between Oracle 8i and Oracle 9i as a Developer
What are the diffrences between Oracle 8i and Oracle 9i as a Developer, not as a DBA??
bismitapadhy
- Feb 3rd, 2008
ISQl*plus and J-server is not there in oracle-8i which are there in 9iBismita
-
Details about FORCE VIEW why and we can use
g_sidhu
- Feb 1st, 2008
Force View: creates the view regardless of whether or not the base tables exist or whether the user has privileges on them. The user still can’t execute the view, but he or she can create it. No...
Lavanya Chowdary
- May 6th, 2007
Generally we are not supposed to create a view without base table. If you want to create any view without base table that is called as Force View or invalid view.?Syntax: CREATE FORCE VIEW AS < SELEC...
-
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.
PL/SQL Interview Questions
Ans