Oracle PL/SQL Interview Questions and Answers

1. What is the starting oracle error number ?

ORA-00000 is the starting oracle error number.

2. What is meant by forward declaration in functions?

Ø  If you are defining a package body having two procedures, if you want to use second procedure in the definitions of first procedure.

Ø  You have to declare the second package with its arguments (if have) before using in the definition of first procedure it is labeled as forward declaration.

3. Can commit, rollback, savepoint be used in database triggers? If yes then how? If no why? With reasons

Ø  Triggers should not contain transaction control statements like commit, rollback, savepoint or set transaction.

Ø  Because it is fired as part of the execution of the triggering statement.

Ø  When the triggering statement is committed or rolled back, work in the trigger is committed or rolled back as well.

4. Can we declare a column having number data type and its scale is larger than precision ex: column name NUMBER (10,100), column name NUMBER (10,-84)

Yes such declaration is possible .Explanation with example.

1. Number (9, 11) means there are 11 digits after decimal .However as the max precision is 9 so the rest are zero padded .Like 0.00999999999

2. Number (9, -11) means that there are 9 digits whereas the rest is zero padded towards left of the decimal point Like 99999999900000000000.0

5.Explain how procedure and functions are called in a pl/sql block ?

Procedure can be called in the following ways:

a) CALL <procedure name> direct

b) EXCECUTE <procedure name> from calling environment

c) <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways:

a) EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.

b) As part of an SQL/PL SQL Expression.

6. Explain the types of cursor?

Cursor is a sql private work area. It opens an area of memory where the query is parsed and executed.

             Types:

Ø  Implicit: Implicit cursor are declared by pl/sql implicitly for all dml and pl/sql statements, including queries that return only one row.

Ø  Explicit: For queries that return more than one row, explicit cursors are declared and named by the programmer and manipulated through specific statements in the block's executable actions.

7. Explain the usage of WHERE CURRENT OF clause in cursors?

When referencing the current row from an explicit cursor,use the where current of clause.This allows you to apply updates and delete to the row currently being addressed,without the need to explicitly reference the rowid.You must inlclude the for update clause in the cursor query on open.

Syntax: Where current of cursor;

8. Give the structure of the function?

CREATE OR REPLACE FUNCTION function_name(formal parameters list with only IN mode)

RETURN datatype

IS/AS

local variable declarations

BEGIN

executable statments;

RETURN value;

END function name;

9. Give the Structure of the procedure?

CREATE OR REPLACE PROCEDURE procedure_name(Optional Parameters)

 IS / AS          

 [PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations]

BEGIN

Executable statements [EXCEPTION exception handlers]

END ;

11. Explain rowid,rownum ?What are the pseduocolumns we have?

Ø  Row id:  Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

Ø  Rownum: For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example:

 SELECT * FROM employees WHERE ROWNUM < 10;

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

The lists of pseudocolumns are:

- CURRVAL and NEXTVAL

- LEVEL

- ROWID

- ROWNUM

-TIMESTAMP

-SYSTIME

-USER

-UID

12. How packaged procedures are called from the stored procedure or anonymous block?

PACKAGE NAME.PROCEDURE NAME (parameters);

Variable: = PACKAGE NAME.FUNCTION NAME (arguments);

EXEC SQL EXECUTE

13. How to disable multiple triggers if a table at a time?

ALTER table table_name DISABLE ALL TRIGGERS

14. How we can create a table through procedure?

You can create table from procedure using Execute immediate command.

CREATE procedure p1 is

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE temp AS SELECT * FROM emp ' ;

END;

15. In pl/sql what is the use of out parameter even though we have return statement.

Without parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions.

16. What are integrity constraints?

 Data integrity allows defining certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn't meet these requirements, Oracle will not allow so.

Constraint types

There are five integrity constraints in Oracle.

Not Null:

A column in a table can be specified not null. It’s impossible to insert a null in such a column. The default is null.

Unique Key:

The unique constraint doesn't allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed.

Primary Key:

On a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key.

Foreign key:

A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table. If a table has a foreign key that references a table, that referenced table can be dropped with a drop table. Cascade constraints. It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Check constraints:

A check constraint allows stating a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.

17. Name the table where characteristic of package,procedure and functions are stored?

user_objects

user_source

user_dependencise

18. State the advantage and disadvantage of cursor?

Cursor is nothing but it's a memory area of executing sql or oracle statements.

Advantage:

1. We can retrieve more than one record by one query.

2. Its use our RAM so retrieval is fast.

3. By using Collection we can retrieve the bundle of data in cursor.

4. A cursor is an oracle variable.

5. It is similar to a 2D Array.

6. It is a forward only and read only variable type.

Disadvantage:

No such of disadvantage of cursor. When we use cursor one thing is necessary. When I have select only one record then we should use implicit cursor and when more record then we should use explicit cursor.

19. State the difference between implicit and explicit cursor?

Implicit Cursor:

Ø  When a query return s Single Row Value then Implicit Cursor is used.  It's return Only One Row. Cursor.

Ø  Name is assigned implicitly.

Ø  Implicit Cursor used for all SQL Statements, that, DECLARE, OPEN, FETCH, CLOSE.

Ø  It's defined by the Oracle Server where never the Operations Single Row.

Ø  Implicit Cursors Automatically provides by Oracle which performs DML Statements. Qureies return only one row.

Ø  We are able to handle NO_DATA_FOUND Exception in implicit Cursor.

Explicit Cursor:

Ø  A subquery returns more than one row Explicit Cursor is Created.

Ø  The rows returned by the query are called Active Set. Its return multiple rows.

Ø  Curosr is assigned explicitly. It's used to process Multirow SELECT Statements.

Ø  Retrieving multiple rows the Programmer declares cursors explicitly.

Ø  Explicit Cursors defined by the User. Queries return more than rows.

Ø  We are not able to handle NO_DATA_FOUND Exception.

20. What are %type and %row type? What are the advantages of using these over data types.

%TYPE is used to declare a variable with the same type as that of a database table column. TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime.

ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table.

 In this case columns selected with SELECT statement must match with variables inside the rowtype variable. If not then induvidually refer these variables inside the ROWTYPE variables.

These two provides data independence and allows you to adopt database changes due to new business requirements. You need not know datatype and size in advance.

21. What are the advantages of stored procedure?

a. The procedures/functions are stored in the database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;

b. The code is stored in a pre-compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;

c. Each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;

d. As the procedures/functions are stored in the database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients.

This results in much less network traffic and again improves scalability;

e. When using PL/SQL packages, as soon as one object in the package is accessed, the whole package is loaded into memory which makes subsequent access to objects in the package much faster stored procedures/functions can be compiled into “native” machine code making them even faster.

22. Difference between %type and %row type. 

%TYPE is used to declare a variable with the same type as that of a database table column. TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime.

ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table. In this case columns selected with SELECT statement must match with variables inside the rowtype variable. If not then individually refer these variables inside the ROWTYPE variables.

23. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?

·         A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

·         A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.

24. What is the difference between procedure and functions?

FUNCTIONS:

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.

2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. You can have DML (insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024).

PROCEDURES:

6. Stored Procedure supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..But actually not exists in database is allowed only in during creation but runtime throws error Function won’t support deferred name resolution.

7. Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.

9. A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

25. What is pl/sql. Advantages of pl/sql.

v  Pl/sql is procedural language. It is an extension of sql with design feature of programming languages.

v  Pl/sql offers modern software engineering features such as data encapsulation, exception handling information hiding, and object orientation.

v  pl/sql reduces the network traffic, it provides you with ability to control the flow of constructs.

v  Pl/sql application can run on any platform on which oracle runs.

v  Pl/sql is not an oracle product in its own write.

Advantages:

v  You can program with procedural language with control structures.

v  Pl/sql can handle errors.

v  Easy maintenance.

v  Improved data security and integrity.

v  Improved performance.

v  Improved code clarity.

v  Pl/sql is portable.

v  You can declare variables.

v  It reduces network traffic.

26. What is ref cursor?

Ref Cursors also known as Dynamic cursors can be associated with as many SELECT statements you want at different times.

You can associate with different SELECT statements at dynamic time.

27. What is the basic structure of pl/sql.

Declare---------------------------Optional

Variable declaration

Cursor declaration

User defined exceptions

Begin---------------------------Mandatory

Sql statements

Application or business logic

Exception ---------------------------Optional

Handling Errors

End; ---------------------------Mandatory

/

28. What will happen after commit statement?

Commit in this context will not do anything except the committing the changes into database, done using DML statements.

However, if the cursor is created with FOR UPDATE clause, it will raise runtime exception as commit, in that case, would also release all locks and thus close the cursor implicitly and user will get ORA-1002 "fetch out of sequence " error as after the loop is executed one time, with the commit statement, the cursor will be closed and fetch into statement will fail.

29. What is trigger,cursor,functions with example.

Trigger: Trigger is a pl/sql block structure which is fired when DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Example for creating a trigger is:

CREATE OR REPLACE TRIGGER emp_comm_trig

   BEFORE INSERT

   ON emp

   FOR EACH ROW

BEGIN

   IF :NEW.deptno = 30

   THEN

      :NEW.comm := :NEW.sal * .4;

   END IF;

END;

Functions:  Function can accept a parameter and must return a value.Function can be called as an part of an expression. Function is a named pl/sql block that is stored in the database for repeated execution. Function must have at least one return statement.

Example:

CREATE OR REPLACE FUNCTION nthsal (i NUMBER)

   RETURN NUMBER

C number;

BEGIN

   SELECT salary

     INTO c

     FROM (SELECT salary, ROWNUM r

             FROM (  SELECT salary

                       FROM employees

                   GROUP BY salary))

    WHERE r = i;

   RETURN c;

END;

/

Cursor: Cursor is a sql private work area.it opens an area of memory where the query is parsed

is parsed and executed.

DECLARE

   v_employeeID   employee.id%TYPE;

   v_FirstName    employee.first_name%TYPE;

   v_LastName     employee.last_name%TYPE;

   CURSOR c_employee

   IS

      SELECT id, first_name, last_name

        FROM employee

       WHERE department_id = 80;

BEGIN

   OPEN c_employee;

   LOOP

      FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;

      DBMS_OUTPUT.put_line (v_employeeID);

      DBMS_OUTPUT.put_line (v_FirstName);

      DBMS_OUTPUT.put_line (v_LastName);

      EXIT WHEN c_employee%NOTFOUND;

   END LOOP;

   CLOSE c_employee;

END;

/

30. What is the cursor attributes used in pl/sql.

 As with explicit cursor there are four attributes for obtaining status information about a cursor. When appended to the cursor variable name these attribute return useful information about the execution of a data manipulation statement.

Attribute                  Type                                 Description

%ISOPEN              Boolean                   Evaluvates to true if the cursor is open.

%NOTFOUND      Boolean                   Evaluvates to true if the most recent fetch does not return a row.

%FOUND              Boolean                   Evaluvates to true if the most recent fetch returns a row;   complement of % not found.

%ROWCOUNT     Boolean                   Evaluvates to the total number of rows returned so far.

31. What are the modes of parameters that can be passed to the procedure.

There are three modes of parameters:

In (default): Passes a constant value from the calling environment to the procedure. Formal parameter acts as a constant. Actual parameter can be literal, expression, constant or initialized variable. Can be assigned a default value.

Out: Passes a value from the procedure to the calling environment. Initialized variable, must be a variable, cannot be assigned a default value.

In Out: Passes a value from the calling environment in to the procedure and a possibly different value from the procedure back to the calling environment using the parameter. Initialized variable must be a variable cannot be assigned a default value.

32. What are two parts of package.

Package body:

A package body usually has a specification and a body stored separately in the data base.

The specification is the interface to your applications. It declares the types, variables, constants and subprograms available for use.

Package specification:

The package specification may also include PRAGMAs, which are directives to the compiler.

The body fully defines cursors and subprograms and to implement the specification.

33. What is raise_application_error.

You can use this procedure to issue user_defined error messages from stored subprograms. You can report errors to your application and avoid returning unhandled exceptions. It is used in two different places executable section and exceptional section.

Syntax:  raise_application_error(error_number,message[,{true|false}];

In th syntax:

Error_number :  is a user specified number for the exception between -20000 and -20999.

Message:  is the user specified message for the exception.It is a character string up to 2048 bytes.

True|False :  is an optional Boolean parameter(if true the error is placed on the stack of previous error .if false ,the default ,the error replaces all previous errors.

34. What is cursor .why it is recquired ?

A cursor is a private sql work area where the statements are executed and the processing information is stored.

35. What is cursor for loop.

A cursor for loops processes rows in an explicit cursor. It is a shortcut because the cursor is opened, rows are fetched once for each iteration in the loop, the loop exists when the last row is processed, and the cursor is closed automatically. The loop itself is terminated at the end of the iteration where the last row is fetched.

Syntax: for record_name in cursor_name  loop

Statement 1;

Statement 2;

.......

End loop;

36. What is a stored procedure?

A procedure is a named pl/sql block that can accept parameters and be invoked. You can use it to perform an action.

A procedure has a header, a declaration section, an executable section, and an optional exception handling section.

A procedure can be compiled and stored in the data base as a schema object.

Procedures promote reusability and maintainability. When validated they can be used an any number of applications.

If the requirements change, only the procedure needs to update.

37. What is an exception? What are types of exception.

An exception is an identifier in pl/sql block that is raised during the execution of a block that terminates its main body of actions. A block always terminates when pl/sql raises an exception,but can you perform an exception handler to perform final actions.

There are two types of exceptions:

Implicitly raised

ü  Predefined oracle server: One of approximately 20 errors that occur most often in pl/sql code.           

ü  Non-predefined oracle server: Any other standard orale server error.

Explicity raised

ü  User defined: A condition that the developer determines is abnormal.

38. Where are predefined exception are stored.

Predefined exceptions were stored in the oracle server.

39. What is pragma_exception_init explain the usage.

In pl/sql,the pragma_exception_init tells the compiler to assiociate an exception name with an oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it.

40. What are the return values of function sqlcode and sqlerm ?

Sqlcode returns the numeric value for the error code or error message by using two functions.Based on the value of the code or message,you can decide which subsequent action to take based on the error.

Sqlerrm returns character data containing the message associated with the error number.

41.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.

42. What are the pl/sql statements used in cursor.

Declaring a CURSOR

Open the CURSOR

FETCH the rows from the CURSOR

CLOSE the CURSOR

43. What are the datatype used in pl/sql.

Scalar:

Single values with no internal components.

Composite:

Data items that have internal components that can be accessed individually.

Reference:

Pointers to other data items.

Large Object (LOB):

Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.

44. What is database triggers and name some usage.

A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens.

Data auditing, implementing complex business rules, security are main uses of database triggers.

45. What happens if a procedure that updates a column of table X is called in a database trigger of the same table?

Mutation of table occurs.

To avoid the mutation table error, the procedure should be declared as an AUTONOMOUS TRANSACTION. By this the procedure will be treated as a separate identity.

46. What is overloading of procedure.

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

E.g.:

DBMS_OUTPUT .PUT_LINE.