Interview Questions

what are exception in oracle

Some of the standard Oracle PLSQL Exception are as follow:

1	NO_DATA_FOUND
2	TOO_MANY_ROWS
3	CURSOR_ALREADY_OPEN
4	DUP_VAL_ON_INDEX
5	INVALID_CURSOR
6	INVALID_NUMBER
7	ZERO_DIVIDE

DUP_VAL_ON_INDEX - Attempted to insert a duplicate value

INVALID_CURSOR - Illegal cursor operation occurred

INVALID_NUMBER - Conversion of character string to number
fails

NO_DATA_FOUND - Single row SELECT returned no data

TOO_MANY_ROWS - Single-row SELECT returned more than one row

ZERO_DIVIDE - Attempted to divide by zero

what is the structure of plsql block

DECLARE
             Variable Declaration
              Cursor Declaration
              User Defined Exception
IS

BEGIN
Executable Statement

EXCEPTION
Exception Handling. Action to perform when error occurs.
END;

Declare Section is optional. Variable and Cursor declaration is defined in declare section.

Executable Statement is defined in Begin Section.

Exception Section is the section where exception can be handled. This is an optional section

difference between procedure and function

	Function must return value whereas procedure does not

	Function can be used in SQL statement whereas procedure can not. Function can be used in SQL statement, if it do not perform any DML operation

	USER_FUNCTIONS data definition stores function details. USER_PROCEDURES data definition stores procedure details

difference between procedure and function

	Function must return value whereas procedure does not

	Function can be used in SQL statement whereas procedure can not. Function can be used in SQL statement, if it do not perform any DML operation

	USER_FUNCTIONS data definition stores function details. USER_PROCEDURES data definition stores procedure details

what are build in package available in plsql

1	DBMS_OUTPUT
2	DBMS_SQL
3	DBMS_UTL
4	UTL_FILE
5	DBMS_STANDARD

how to create global temporary table in oracle

A temporary table has a definition and structure like that of a regular table, except that the data only exists for the duration of the current transaction or session.

CREATE GLOBAL TEMPORARY TABLE temp_table
     (a number,
      b varchar2(10)
     );

Temporary table is cleaned up atomically at session end or transaction end.

INSERT INTO temp_table
VALUES (1,'ABC');

If you fire select statement, it will display data.

SELECT * FROM temp_table;

OUTPUT
-------------------
A B
---------------
1 ABC

Once you commit, the data from temporary table is deleted.

COMMIT;

If you fire select statement, it will not display any data.

SELECT * FROM temp_table;

i.e., in same session once the data is committed it will delete data
from temporary table.

The default behaviour of temporary table is ON COMMIT DELETE ROWS, which means that rows are visible only within the transaction.

ON COMMIT PRESERVE ROWS - The rows are visible after the transaction, but within the session only.

create global temporary table temp1(a number, b varchar2(5))
ON COMMIT PRESERVE ROWS;

what are the different optimisation techniques

	Execute Plan - we can see the plan of the query and change it accordingly based on the indexes

	Optimizer_hint - set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS'); Select /+ First_Rows / Deptno,Dname,Loc,Rowid from dept where (Deptno > 25)

	Optimize_Sql - By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for ever time the SQL must be parsed whenever they are executed.

	Optimize_Tp - By setting the Optimize_Tp= No, Oracle Forms assigns separate cursor only for each query SELECT statement. All other SQL statements reuse the cursor. f45run module = my_firstform userid = scott/tiger optimize_Tp = No