-
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.
-
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 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.
-
What are ref cursors ?
g_sidhu
- Feb 1st, 2008
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. Thus, declaring a cursor variable creates a pointer, not an item. In P...
nisha
- Aug 24th, 2006
ref cursor is a simple cursor but it is a datatype that allow developers to declare cursor variableref cursor has 2 types:-strong ref cursor where we mention the return type with rowtype.weak cursor w...
-
What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.SQLERRM returns the relevant error message of the SQLCODE.
g_sidhu
- Jan 31st, 2008
• SQLCODE: Returns the numeric value for the error code• SQLERRM: Returns the message associated with the error numberSQLCODE Value &nbs...
acpsrinivas
- Feb 25th, 2006
Pl / Sql Provides Error Information via two Built-in functions, SQLCODE & SQLERRM.SQLCODE Returns the Current Error Code. &nb...
-
What are advantages fo Stored Procedures / Extensibility,Modularity, Reusability, Maintainability and one time compilation.
g_sidhu
- Jan 31st, 2008
In addition to modularizing application development, stored procedures and functions have thefollowing benefits:1) Improved performanceAvoid reparsing for multiple users by exploiting the shared SQL a...
deviji2000
- Oct 16th, 2007
procedures and function hv many benefits in addition to modularizing application development.1) easy maintenance : sub programs are lo...
-
What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema.Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
g_sidhu
- Jan 31st, 2008
A package usually has a specification and a body, stored separately in the database.The specification is the interface to your applications. It declares the types, variables, constants, exceptions, cu...
Mahesh Argade
- Jun 28th, 2006
In addition to this Both Package Spec and Package Body are stored separately in the Data basePackage specifications act as an interface for the Package
-
What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.
g_sidhu
- Jan 31st, 2008
A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has ...
ramesh_resides
- Jun 23rd, 2007
Stored procedure is a sub program and stored in memory for repated execution
-
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
g_sidhu
- Jan 31st, 2008
COMMIT, ROLLBACK, and SAVEPOINT statements are not allowed within the trigger body. It is possible to commit or rollback indirectly by calling a procedure, but it is not recommended because of side effects to transactions.
jockey
- Aug 2nd, 2007
Using autonomous_transaction it is possible
-
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.
-
How do you compile a package?
Larry
- Nov 6th, 2007
Execute package_name;
PL/SQL Interview Questions
Ans