Technicalsymposium.com - New Updates Alerts-Subscribe


Our Telegram Channel - Join Us


Our Free Email Alerts - Join Us



Important Note:Login & Check Your
Email Inbox and Activate Confirmation Link

Our Whatsapp Group - Join Us


Technical Interview Materials PDF-Free Download





Technical Interview Q & A PDF





Question #11) What is a PL/SQL block?

Answer: In PL/SQL, statements are grouped into units called Blocks. PL/SQL blocks can include constants, variables, SQL statements, loops, conditional statements, exception handling. Blocks can also build a procedure, a function or a package. 

Broadly, PL/SQL blocks are two types 

1) Anonymous blocks: PL/SQL blocks without header are known as anonymous blocks. These blocks do not form the body of a procedure, function or triggers. 

Example:

DECLARE

num NUMBER(2);

sq NUMBER(3);

BEGIN

num:= &Number1;

sq := num*num;

DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);

END;

2) Named blocks: PL/SQL blocks having header or labels are known as Named blocks. Named blocks can either be subprograms (procedures, functions, packages) or Triggers. 

Example:

FUNCTION sqr (num IN NUMBER)

RETURN NUMBER is sq NUMBER(2);

BEGIN

sq:= num*num;

RETURN sq;

END;

Question #12) Differentiate between syntax and runtime errors?

Answer: Syntax errors are the one which can be easily identified by a PL/SQL compiler. These errors can be the spelling mistake, etc. Runtime errors are those errors in PL/SQL block for which exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows. 

Question #13) What are COMMIT, ROLLBACK, and SAVEPOINT?

Answer: COMMIT, SAVEPOINT, and ROLLBACK are three transaction specifications available in PL/SQL. 

COMMIT statement: When DML operation is performed, it only manipulates data in database buffer and the database remains unaffected by these changes. To save/store these transaction changes to the database, we need to COMMIT the transaction. COMMIT transaction saves all outstanding changes since the last COMMIT and the following process happens 

• Affected rows locks are released

• Transaction marked as complete

• Transaction detail is stored in the data dictionary.

Syntax: COMMIT;

ROLLBACK statement: When we want to undo or erase all the changes that have occurred in the current transaction so far, we require rolling back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK. 

Syntax to rollback a transaction fully

ROLLBACK;

SAVEPOINT statement: The SAVEPOINT statement gives a name and marks a point in the processing of the current transaction. The changes and locks that have occurred before the SAVEPOINT in the transaction are preserved while those that occur after the SAVEPOINT are released. 

Syntax:

Question #14) What is the mutating table and constraining table?

Answer: A table which is currently being modified by a DML statement like defining triggers in a table is known as a Mutating table. 

A table that might need to be read from for a referential integrity constraint is known as constraining table. 

Question #15) What are actual parameters and formal parameters?

Answer: The variables or an expression referred to as parameters that appear in the procedure call statement is known as Actual parameters. 

For example: raise_sal(emp_num, merit+ amount);

Here in the above example, emp_num and amount are the two actual parameters.

The variables that are declared in the procedure header and are referenced in the procedure body are called as Formal parameters. 

For example:

PROCEDURE raise_sal( emp_id INTEGER) IS

curr_sal REAL:

………..

BEGIN

SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;

…….

END raise_sal;

Here in the above example, emp_id acts as a formal parameter.

Question #16) What is the difference between ROLLBACK and ROLLBACK TO statements?

Answer: The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undoes a transaction and release all locks. On the other hand, a transaction is still active and running after ROLLBACK TO command as it undoes a part of the transaction up till the given SAVEPOINT. 

Question #17) Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?

Answer

SET SERVER OUTPUT ON

DECLARE

BEGIN

FOR i IN REVERSE 1..99

LOOP

IF Mod(i,3) = 0 THEN

DBMS_OUTPUT.PUT_LINE(i);

END IF;

END LOOP;

END;

/ Question #18) What are the 3 modes of parameter?

Answer: 3 modes of the parameter are IN, OUT, IN OUT. These can be explained as follows

IN parameters: IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value. OUT parameters: OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression. IN OUT parameters: IN OUT parameters passes initial values to a procedure and returns updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value. 

Question #19) Why is %ISOPEN always false for an implicit cursor?

Answer: An implicit cursor, SQL%ISOPEN attribute is always false because the implicit cursor is opened for a DML statement and is closed immediately after the execution of DML statement. 

Question #20) When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?

Answer: The outcome of the statement is saved in 4 cursor attributes. These are

• SQL%FOUND

• SQL%NOTFOUND

• SQL%ROWCOUNT

• SQL%ISOPEN

Question #21) What are the ways on commenting in a PL/SQL code?

Answer: Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed. There are two ways to comment in PL/SQL

Source: Contents are provided by Technicalsymposium Google Group Members. 
Disclaimer: All the above contents are provided by technicalsymposium.com Google Group members. 
Further, this content is not intended to be used for commercial purpose. Technicalsymposium.com is not liable/responsible for any copyright issues.


Technicalsymposium.com-All Quick Links & Study Notes PDF- Free Download