What special operators does Oracle provide for dealing with NULLS?
NVL: Convert NULL to another specified value as in:
my_var:= NVL(your_var,'Hello');
IS NULL and IS NOT NULL
- To check whether variable value is NULL or NOT NULL.
Three different rules that apply to NULLs when doing comparison:
- If value in an expression is NULL, that expression evaluate to NULL
- NULL is never equal to or not equal to another value.
- NULL is never TRUE or FALSE.
What command you use to encrypt PL/SQL application?
- WRAP
Diff between function, procedure and package.
- A function has a return type in its specification and must return value specified in that type.
- Procedure has no return type and does not return any value
Compilation process of PL/SQL block?
- Includes syntax checking, binding and p-code generation
- Syntax checking is checking PL/SQL code for compilation errors.
- After syntax errors have been corrected, storage address is assigned to variables that are used to hold data for Oracle. This is binding.
- Next, p-code is generated for PL/SQL block.
- P-Code is the list of instructions to PL/SQL engine.
- For named blocks, p-code is stored in database. and it is used the next time the program is executed.
Difference between syntax error and runtime error?
- syntax error can be detected by PL/SQL compiler.
- runtime error occurs while program is running and cannot be detected by PL/SQL compiler.
Define Commit, Rollback and Savepoint
- When COMMIT statement is issued to database, transaction has ended and following results are true:
All work done by transaction becomes permanent.
Other users can see changes in data made by transaction
- ROLLBACK statement is issued to database, transaction has ended, and following results are true:
All work done by transaction is undone, as if it has not been issued.
Any locks acquired by transaction are released.
With Save point only part of transaction can be undone.
Explain implicit and explicit cursors:
- Any given PL/SQL block issues an implicit cursor whenever SQL statement is executed, as long as an explicit cursor does not exist for SQL statement.
- cursor is automatically associated with every DML(update, delete, insert).
- UPDATE and DELETE have cursors that identify set of rows that will be
affected by operation.
- INSERT statement needs a place to receive data that is to be inserted into database, implicit cursor fulfills this need.
- Most recently opened cursor is called SQL cursor.
Explicit cursor working:
- Declaring the cursor: This initializes the cursor in memory
- Opening the cursor: declared cursor is opened and memory is allotted.
- Fetching the cursor: declared and opened cursor can now retrieve data.
- Closing cursor: declared, opened and fetch cursor must be closed to release memory allocation.
three PL/SQL exceptions:
- Too_many_rows.
- No_Data_Found
- Value_Error
- Zero_Error
- Others
PL/SQL cursor exception:
- Cursor_Already_Open, Invalid_Cursor
What is maximum number of trigger, can apply to single table?
- 12 triggers.
Describe use of PL/SQL tables:
- PL/SQL tables are scalar arrays that can be referenced by binary integer.
- They can be used to hold values for use in later queries or calculations.
When is declare statement needed?
- It is used in PL/SQL blocks such as with stand alone, non stored PL/SQL procedures.
- It must come first in PL/SQL stand alone file if it is used.
In what order open/fetch/loop set of commands in PL/SQL block be implemented if you use the
%NOTFOUND cursor variable in the exit when statement? why?
- OPEN then FETCH then LOOP followed by exit when.
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
- SQLCODE returns value of error number for last error encountered.
- SQLERRM returns actual error message for last error encountered.
- They can be used in exception handling to report, or, store in an error log table, the error that occured in code.
How to find within PL/SQL block, if cursor is open?
- Use the %ISOPEN cursor status variable.
Generate debugging output from PL/SQL:
- Use the DBMS_OUTPUT package.
- Just use the SHOW ERROR command.
- DBMS_OUTPUT package can be used to show intermediate results from
loops and status of variables as procedure is executed.
- The new package UTL_FILE can also be used.
Types of triggers:
- 12 types of triggers in PL/SQL that consist of combinations of
BEFORE
AFTER
ROW
TABLE
INSERT
UPDATE
DELETE and ALL keywords:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
How to define two dimensional array of numbers in PL/SQL?
- Create a collection of associative arrays:
CREATE OR REPLACE PACKAGE twodim_aa
IS
TYPE data_t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE array_t IS TABLE OF data_t
INDEX BY PLS_INTEGER;
END twodim_aa;
DECLARE
I_2d_grid twodim_aa.array_t;
BEGIN
I_2d_grid(1)(1):= 100;
I_2d_grid(1)(2):= 120;
NVL: Convert NULL to another specified value as in:
my_var:= NVL(your_var,'Hello');
IS NULL and IS NOT NULL
- To check whether variable value is NULL or NOT NULL.
Three different rules that apply to NULLs when doing comparison:
- If value in an expression is NULL, that expression evaluate to NULL
- NULL is never equal to or not equal to another value.
- NULL is never TRUE or FALSE.
What command you use to encrypt PL/SQL application?
- WRAP
Diff between function, procedure and package.
- A function has a return type in its specification and must return value specified in that type.
- Procedure has no return type and does not return any value
Compilation process of PL/SQL block?
- Includes syntax checking, binding and p-code generation
- Syntax checking is checking PL/SQL code for compilation errors.
- After syntax errors have been corrected, storage address is assigned to variables that are used to hold data for Oracle. This is binding.
- Next, p-code is generated for PL/SQL block.
- P-Code is the list of instructions to PL/SQL engine.
- For named blocks, p-code is stored in database. and it is used the next time the program is executed.
Difference between syntax error and runtime error?
- syntax error can be detected by PL/SQL compiler.
- runtime error occurs while program is running and cannot be detected by PL/SQL compiler.
Define Commit, Rollback and Savepoint
- When COMMIT statement is issued to database, transaction has ended and following results are true:
All work done by transaction becomes permanent.
Other users can see changes in data made by transaction
- ROLLBACK statement is issued to database, transaction has ended, and following results are true:
All work done by transaction is undone, as if it has not been issued.
Any locks acquired by transaction are released.
With Save point only part of transaction can be undone.
Explain implicit and explicit cursors:
- Any given PL/SQL block issues an implicit cursor whenever SQL statement is executed, as long as an explicit cursor does not exist for SQL statement.
- cursor is automatically associated with every DML(update, delete, insert).
- UPDATE and DELETE have cursors that identify set of rows that will be
affected by operation.
- INSERT statement needs a place to receive data that is to be inserted into database, implicit cursor fulfills this need.
- Most recently opened cursor is called SQL cursor.
Explicit cursor working:
- Declaring the cursor: This initializes the cursor in memory
- Opening the cursor: declared cursor is opened and memory is allotted.
- Fetching the cursor: declared and opened cursor can now retrieve data.
- Closing cursor: declared, opened and fetch cursor must be closed to release memory allocation.
three PL/SQL exceptions:
- Too_many_rows.
- No_Data_Found
- Value_Error
- Zero_Error
- Others
PL/SQL cursor exception:
- Cursor_Already_Open, Invalid_Cursor
What is maximum number of trigger, can apply to single table?
- 12 triggers.
Describe use of PL/SQL tables:
- PL/SQL tables are scalar arrays that can be referenced by binary integer.
- They can be used to hold values for use in later queries or calculations.
When is declare statement needed?
- It is used in PL/SQL blocks such as with stand alone, non stored PL/SQL procedures.
- It must come first in PL/SQL stand alone file if it is used.
In what order open/fetch/loop set of commands in PL/SQL block be implemented if you use the
%NOTFOUND cursor variable in the exit when statement? why?
- OPEN then FETCH then LOOP followed by exit when.
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
- SQLCODE returns value of error number for last error encountered.
- SQLERRM returns actual error message for last error encountered.
- They can be used in exception handling to report, or, store in an error log table, the error that occured in code.
How to find within PL/SQL block, if cursor is open?
- Use the %ISOPEN cursor status variable.
Generate debugging output from PL/SQL:
- Use the DBMS_OUTPUT package.
- Just use the SHOW ERROR command.
- DBMS_OUTPUT package can be used to show intermediate results from
loops and status of variables as procedure is executed.
- The new package UTL_FILE can also be used.
Types of triggers:
- 12 types of triggers in PL/SQL that consist of combinations of
BEFORE
AFTER
ROW
TABLE
INSERT
UPDATE
DELETE and ALL keywords:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
How to define two dimensional array of numbers in PL/SQL?
- Create a collection of associative arrays:
CREATE OR REPLACE PACKAGE twodim_aa
IS
TYPE data_t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE array_t IS TABLE OF data_t
INDEX BY PLS_INTEGER;
END twodim_aa;
DECLARE
I_2d_grid twodim_aa.array_t;
BEGIN
I_2d_grid(1)(1):= 100;
I_2d_grid(1)(2):= 120;
I_2d_grid(200)(206):= 200;
IF I_2d_grid(1)(2)
To update rows with opposite gender in SQL:
In emp table there is column which consists of gender(male or female)?
How should one can update values of male to female and female to male
with single query?
- SELECT Decode(gender,m,f,f,m,null) from table
Can we write commit or rollback in trigger?
- We can use commit or rollback statements in trigger by using AUTONOMOUS_TRANSACTION pragma.
Delete duplicate records without using subquery:
- USING "ROWID" PSEUDO COLUMN YOU CAN DELETE
DUPLICATE RECORDS.
Can we insert multiple nulls through unique constraints?
- Yes, we can insert multiple nulls as each nulls have different ROWID.
When use before and after trigger?
- Before trigger used while performing DML operations and After Trigger
is used for after performing DML operations in banking systems.
To avoid using cursors:
- Use MERGE statements in Oracle to avoid looping through conventional cursors.
- It will save lot of time.
Components of PL/SQL block:
- Set of declarations and procedural statements is called block.
PL/SQL Block contains:
- Declare: Optional Variable declaration
- Begin: Mandatory Procedural statements
- Exception: Optional any errors to be trapped
- End: Mandatory
So only begin and end keywords are required, as needed to process PL/SQL block
Database trigger: Name some usages of database trigger?
- Trigger is database object.
- It is block of code that is executed automatically when DML operation are fired.
By using trigger we can do auditing and perform an operation(modifications) on tables(using dml operation).
No comments:
Post a Comment