PLSQL interview questions

In cursors, there exists a clause named WHERE CURRENT OF, explain its usage.

In cursors, the term or clause WHERE CURRENT OF implies that the cursor points to the exact row where the cursor is presently.

Give the name of the tables where characteristics of packages, functions and procedures are usually stored.

The above tables are the user_objects, User_errors and the User_source.

Name the two virtual table columns that are usually available during the trigger execution of a particular database databases.

During this process, two columns are usually available namely the OLD.column_name and the NEW.column_name.

When it comes to triggers that are related to the INSERT function, the only available ones are the NEW.column_namevalues whereas for Update related triggers, the following are the only available ones OLD.column_name and the NEW.column_name

Explain the difference between a procedure and a function.

The difference is that a function is expected to return a value always using a given statement whereas for the procedure, there is no mandate, it can either return a value or fail to return a value at all.

Name the two parts of a package.

The two parts of a package are the package specification and the package body. In the package body, the following can be found: The declarations, both global to the package and local to the schema.

In the package body, the following are prevalent: the actual procedures as well as the local declarations pertaining to the procedures as well as the cursor declarations.

Describe what you understand by the term Raise_application_error.

This is a term referring to a procedure of the package DBMS_STANDARD, responsible for allowing the issue of the user_defined error messages from stored subprograms or a given database trigger.

State whether or not one can use transaction control statements such as the COMMIT or ROLLBACK in a database trigger. Give a reason to support your answer.

Under normal circumstances, it is not possible for one to use the above mentioned control statements this is because by definition, triggers are particularly defined for each table thus if you use these transactions, they will definitely interrupt the transaction processing so that it doesn’t happen in a logical manner.

What are the advantages and the disadvantages of cursors?

Advantages

Cursors maybe used in to perform actions with more then one record in a pl/sql system.

Records can easily be moved and procedures exited from with the help of cursors.

Disadvantages

Implicit and explicit cursors depend on the given situation thus it would be advised that for a result set with less than fifty or 100 records, one goes for implicit cursors, else for a larger result set, one should always go for the explicit otherwise, there will be so much processor cycles required, thus inefficient resource utilization.

Show how one can call the functions and procedures in a PL/SQL block.

In a PL/SQL block, functions are called as part of an expression as shown below

sal := calculate_sal ('a822');

Whereas the procedures are called as pl/sql statements as shown below

calculate_bonus ('A822');

Show the basic structure of a pl/sql.

The following is the basic structure of a pl/sql

DECLARE
--all the variables that will be used in the program should be declared here
---
BEGIN
--here is where you put the application logic
--
EXCEPTION HANDLING
--very important as here is where exception handling is performed, a very important facet in case of an unpredicted event.
END

Explain how you would debug a procedure.

A procedure can be debugged with the help of DBMS_DEBUG package or the oracle supplied package called the DBMS_OUTPUT.

Describe the terms cursor, trigger and function as used in pl-sql, provide sample programs about them.

Cursor

The term cursor refers to a private work area in sql. It is notable that each and every statement in sql which is executed by oracle server usually has a certain cursor associated with it, there exists two types of cursors namely the implicit and the explicit cursors, Implicit cursors are the cursors which are usually declared by the pl/sql in an implicit way in the instance of the DML statement and select statements in the pl-sql system and includes queries which return single rows.

Four attributes exists in cursors as shown below

- SQL%FOUND

- SQL%NOTFOUND

- SQL%ISOPEN

- SQL%ROWCOUNT

The following is the narration regarding the aforementioned cursor attributes

SQL%FOUND

This attribute will always evaluate a true as one or more rows will always be affected by the sql statements which are recent.

SQL%NOTFOUND

This one will evaluate true as long as no row has been affected by the present sql statement

SQL%ISOPEN

This one evaluates false always. This is because the there is always an automatic closure of implicit cursors after execution of sql statements

SQL%ROWCOUNT

This one basically returns a number referring to the number of rows

Below is an example of an explicit cursor

DECLARE
v_empno employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cur IS
SELECT employee_id,last_name
FROM employees
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_name ;
EXIT WHEN emp_cur%ROWCOUNT>10 OR
emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('employee_id:'||TO_CHAR(v_empno) || 'employee_name:'||'v_name');

END LOOP;
CLOSE emp_cur;
END;

The trigger

This is a block or a procedure in the pl/sql system which is normally associated with the schema, table, views and the databases. They are executed immediately in response to an occurrence of some particular events; there exists two types of triggers, namely Application trigger which is automatically fired in case an event occurs regarding a particular application and the database trigger which fires in cases where the data like the DML operation occur at a particular time.

Still under DML operations exists two types of triggers namely the Statement level trigger and the Row level trigger.

The statement level trigger implies that the trigger body executes only ones for the triggering event; this is the default, the statement level fires once even if no row has been affected at all.

The trigger body in the row level executes only once for each and every row affected by the triggering event, this implies that in an event driven that no row has been affected, then the trigger body does not execute.

Below is an example of a trigger:

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
TO_CHAR(SYSDATE,'DY')IN('SUN','SAT') OR TO_CHAR((SYSDATE,'HH24:MI')NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROR(-20253,'u may insert employee information at business hrs'

State the number of database triggers that can be specified on a table and mention the respective triggers.

I think the number of database triggers that one can specify in a table is five of whose naming and descriptions are as described below.

- Insert Update Delete

- After Row o.k. o.k. o.k.

- Before Row o.k. o.k. o.k.

- After Statement o.k. o.k. o.k.

- Before Statement o.k. o.k. o.k.

In an event that the FOR EACH ROW clause has been specified, then the trigger for each row is affected by the given statement while if the WHEN clause is the one which has been specified, then the trigger fires in respect to the returned Boolean value

Describe the restrictions on cursor variables.

Currently, the following are the restrictions to pertinent to cursor variables

- It is not possible for one to declare a cursor variable in a package spec, for instance, the following declaration would be invalid: CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- not allowedEND emp_stuff.

- It is not possible for one to pass a cursor variable to a procedure which has been called via the database link.

- When one passes a host cursor to the pl-sql, it is not possible for him to fetch from it on the server side except in a situation whereby he has also opened it there on the same server call.

- It is not possible for one to use comparison operators when testing a cursor variable, be it for equality, nullity or inequality.

- It is not allowed that one assigns a null to the cursor variable

- You can not store values of data variables in the database columns

- There exist no type equivalent to be used in CREATE TABLE statement

- Cursor variables can not be stored in the associative array, nested table or the varray.

- There exist no interoperability between the cursors and the cursor variables, i.e. you can not substitute one to work in a situation where the other one is expected. E.g. it is not possible for one to reference a cursor variable in a cursor FOR loop

Explain what you understand by the term database trigger and give some usages of the database trigger.

By definition, a database trigger is pl/sql program unit which has been stored in the system and is associated with a specific database table or view and of whose code defines the action to be undertaken by the database in an event that a certain manipulation takes place in the database, be it of INSERT, DELETE, or UPDATE in nature.

It differs from the stored procedures and functions in that much as the stored procedures and functions have to (must) be called explicitly, the database triggers are fired or executed or called implicitly in an event that the table has been affected by any of the above mentioned DML operations.

Until the oracle version 7.0, the only number of triggers which could be associated with a given database table was twelve but higher versions do not contain such limitations and one can have as many triggers as possible, the only consideration is that the database trigger does not fire with the privileges of the user but with the privileges of the owner.

The following three parts do exist in a database trigger:

1) The trigger constraint (Optional)

2) The trigger action and the

3) Trigger event

The trigger events may consist of either update, insert or delete statements or even an instance regarding shut down and startup event, this trigger fires automatically when there is an occurrence of any of these events.

The trigger constraint is responsible for the specification of what has to be true in order for the firing by the given trigger to take place, usually a condition specified in the WHEN clause. The trigger action by definition is simply the procedure which contains the particular code to be executed in the event that the specified trigger fires.

Describe the cursor for loop.

Cursor for loop is a facet which is used to implicitly declare the %ROWTYPE as a loop index then opens the cursor and then fetches the row of values from the active set to place them in the field in records and finally closes when all the records have been processed.

Below is an example of the cursor for loop

FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP

Describe your understanding regarding the term PL/SQL.

The term PL/SQL is an abbreviation of Programming Language/Structured Query Language. This is a procedural language having both the interactive features of the Structured Query Language as well as those of the procedural Programming Language such as control of flow like the conditional branching as well as the iteration etc.

Describe the implicit cursors and the explicit cursors.

The above are the two types of cursors available in the PL/SQL system; implicit cursors are used for queries while the explicit cursors, also called the user defined cursors, are declared and used by the users.

Explain how you would create a table with the help of a procedure.

This will be done by the help of the immediate command. The following is the create procedure:

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

What do you understand by the term stored procedure?

A stored procedure is a term which refers to a set of predefined instructions which collectively performs a given task. Usually these are stored in the database as the schema object and can be executed in a repetitive manner; it can be invoked, given parameters or even nested.

Describe the following as used in the pl/sql system stating their advantages and the disadvantages.

1) % TYPE

2) % ROWTYPE

The % TYPE is responsible for the provision of data types of given table columns

The % ROWTYPE attribute has the following usefulness:

It is useful in the situations when we are the need to fetch the entire row from the table. When we are not aware of the data types of some columns, the% ROWTYPE becomes useful and finally in an event of a change of any datatype of a given column, the % ROWTYPE automatically changes the given datatype for the value which the user creates.

Give the difference between the application procedure, stored procedure, stored function and the application function.

The difference is that stored function/procedure is an object compiled from the database used to provide fast response from the Oracle Engine. The difference is that the stored procedure must return a multiple value and the functions must return a single value.

Describe the term procedure overloading.

Procedure overloading is like function overloading in C++ and here, here the name of the procedure is the same, but the number of parameters vary thus the system will choose which one to execute basing on the number of parameters which are equivalent to the number of parameters provided in the query, with this the procedure is regarded as an overloaded procedure.

In an event that the number of parameters are the same, then the then the datatypes involved in the procedure have to be different and as a result the system will still be able to choose which particular procedure to execute from the ones with the same name by selecting the one with the datatypes which match the datatypes in question. With this, still the procedure will be considered overloaded.

State the use of out parameters in the pl/sql functions in the situations where return statement is available.

Out parameters are used in getting more than one out of the values in a program which is calling. Under normal circumstances, it is usually not advised that one should use the out parameters in functions. It is recommended that in a situation whereby one needs more than out values then he better seek refuge to the procedures and not to the functions.

Differentiate between a cursor which has been declared in a given procedure and the ones (cursors) which have been declared in a package specification.

Usually, a cursor declared in a given package specification is one which is global in that it can be accessed by all other procedures in the given package whereas the one declared in procedure is local and only accessed within that procedure, i.e. can not be accessed outside that given procedure This is an equivalent analogy to the declaration regarding other programming languages such as C++, or even Java whereby the declarations which are local to a given object are only accessible in that particular object whereas the ones pertaining to the entire program, i.e. the global ones can be accessed by all classes, objects and functions in that particular program.

photo