These questions will be updated on regular basis, so stay tuned and subscribe to RSS Feed.
Q. How many max triggers can be defined in a Table?
A. 12 Triggers
Q. Can we use equal sign for a Null value?
A. No we can’t, Null is not equals to another null. Thats
why we use (COL1 IS NULL, rather than COL1=NULL);
Q. What is IS NULL and IS NOT NULL
A. You can use this syntax to check specifically to see if a
variable's value is NULL or NOT NULL.
Q. How to encrypt a PL/SQL application?
A. Using WRAP
command.
Q. What is the difference between a PROCEDURE, FUNCTION and
PACKAGE.
A. Procedure does not have a return type in its
specification and should not return a value, but it can have a return statement
that simply stops its execution and returns to the caller procedure. A function has a
return type in its spec and must return a value specified in that type. A package is consists of logically connected
procedure and/or functions.
Q. How to handle NULL value in SQL?
A. NVL - Converts a NULL to another specified value, as in:
Select NVL(COL1, ‘Abcd’) from TAB1; TAB1 is the table where COL1 is a column.
This query will return the exact value of COL1 if its not null and if its null,
then ‘Abcd’ will appear.
Q. Briefly tell the
compilation process of a PL/SQL block?
A. The compilation process steps are syntax check, binding, &
p-code generation. Syntax checking involves checking PL/SQL code for
compilation errors. After syntax errors have been corrected, an internal
storage address is assigned to the variables that are used to hold data for
Oracle. This process is called binding. After this, p-code is generated for the
PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named
blocks, p-code is stored in the database, and it is used the next time the
program is executed.
Q. What is difference between Runtime error and Syntax error?
A. A syntax error can be Tracked by the PL/SQL compiler. A
runtime error occurs only when the program is running and cannot be detected by
the PL/SQL compiler.
For quick idea - A misspelled keyword is an example of a
syntax error. For example, this script:
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is a Syntax test');
ENDD;
contains a syntax error, ENDD should be END only.
For runtime error:
Declare
Abc number;
Begin
Select Col1 into Abc from TAB1 where col1 like ‘%A%’;
End;
As this will return a character into Abc, so it’ll give
runtime error.
Q. What is difference of Commit, Rollback and Savepoint.
A. 1. When a COMMIT statement is triggered to the database,
the transaction has ended, and the following results are true:
a. All work done by
the transaction in the session becomes permanent.
b. Other session can see changes in data made by the transaction.
c. Any locks acquired by the transaction are released.
2. When a ROLLBACK statement is triggered to the database,
the transaction has ended, and the following results are true:
a. All work done by the transaction is undone, as if it
hadn’t been issued.
b. Any locks acquired by the transaction are released.
c. The ROLLBACK statement undoes all the work done by the
user in a specific transaction/session. With the SAVEPOINT command, however,
only part of the transaction can be undone.
