Special Offer - Enroll Now and Get 2 Course at ₹25000/- Only Explore Now!

All Courses
PL/SQL Interview Questions and Answers

PL/SQL Interview Questions and Answers

April 5th, 2019

In case you’re searching for PL/SQL Interview Questions and answers for Experienced or Freshers, you are at the correct place. GangBoard offers Advanced PL/SQL Interview Questions and answers that assist you in splitting your PL/SQL interview and procure dream vocation as PL/SQL Developer.

Q1) What is the starting oracle error number ?

Answer: ORA-00000 is the staring oracle error number.

Q2) What is meant by forward declaration in functions?

Answer:

  1. If you are defining a package body having two procedures, if you want to use second procedure in the definitions of first procedure.
  2. 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.

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

Answer:

  1. Triggers should not contain transaction control statements like commit, rollback, savepoint or set transaction.
  2. Because it is fired as part of the execution of the triggering statement.
  3. When the triggering statement is committed or rolled back, work in the trigger is committed or rolled back as well.

Q4) 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)

Answer: 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

Q5) Explain how procedure and functions are called in a pl/sql block ?

Answer: Procedure can be called in the following ways:

  1. CALL <procedure name> direct
  2. EXCECUTE <procedure name> from calling environment
  3. <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways:

  1. EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.
  2. As part of an SQL/PL SQL Expression.

Q6) Explain the types of cursor?

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

  1. Implicit: Implicit cursor are declared by pl/sql implicitly for all dml and pl/sql statements, including queries that return only one row.
  2. 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.

Q7) Explain the usage of WHERE CURRENT OF clause in cursors?

Answer: 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 include the for update clause in the cursor query on open.
Syntax: Where current of cursor;

Q8) Give the structure of the function?

Answer: 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;

Q9) Give the Structure of the procedure?

Answer: CREATE OR REPLACE PROCEDURE procedure_name(Optional Parameters)
IS / AS
[PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations] BEGIN
Executable statements [EXCEPTION exception handlers] END ;

Q10) Explain rowid,rownum ?What are the pseduocolumns we have?

Answer:

  1. 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.
  2. 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:

  1. CURRVAL and NEXTVAL
  2. LEVEL
  3. ROWID
  4. ROWNUM
  5. TIMESTAMP
  6. SYSTIME
  7. USER
  8. UID

Q11) How packaged procedures are called from the stored procedure or anonymous block?

Answer: PACKAGE NAME.PROCEDURE NAME (parameters);
Variable: = PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE

Q12) How to disable multiple triggers if a table at a time?

Answer: ALTER table table_name DISABLE ALL TRIGGERS

Q13) How we can create a table through procedure?

Answer: 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;

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

Answer: 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.

Q15) What are integrity constraints?

Answer: 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.

Q16) Name the table where characteristic of package,procedure and functions are stored?

Answer: user_objects
user_source
user_dependencise

Q17) State the advantage and disadvantage of cursor?

Answer: 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.

Q18) State the difference between implicit and explicit cursor?

Answer:
Implicit Cursor:

  1. When a query return s Single Row Value then Implicit Cursor is used. It’s return Only One Row. Cursor.
  2. Name is assigned implicitly.
  3. Implicit Cursor used for all SQL Statements, that, DECLARE, OPEN, FETCH, CLOSE.
  4. It’s defined by the Oracle Server where never the Operations Single Row.
  5. Implicit Cursors Automatically provides by Oracle which performs DML Statements. Qureies return only one row.
  6. We are able to handle NO_DATA_FOUND Exception in implicit Cursor.

 Explicit Cursor:

  1. A subquery returns more than one row Explicit Cursor is Created.
  2. The rows returned by the query are called Active Set. Its return multiple rows.
  3. Curosr is assigned explicitly. It’s used to process Multirow SELECT Statements.
  4. Retrieving multiple rows the Programmer declares cursors explicitly.
  5. Explicit Cursors defined by the User. Queries return more than rows.
  6. We are not able to handle NO_DATA_FOUND Exception.

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

Answer: %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.
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.

Q20) What are the advantages of stored procedure?

Answer: 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;

  1. 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;
  2. 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;
  3. 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;

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

Q21) Difference between %type and %row type.

Answer: %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.

Q22) What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?

Answer:

  1. A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
  2. A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.

Q23) What is the difference between procedure and functions?

Answer:
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:

  1. 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.
  2. Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
  3. Stored procedure is pre-compiled execution plan where as functions are not.
  4. 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.

Q24) What is pl/sql. Advantages of pl/sql.

Answer:

  1. Pl/sql is procedural language. It is an extension of sql with design feature of programming languages.
  2. Pl/sql offers modern software engineering features such as data encapsulation, exception handling information hiding, and object orientation.
  3. pl/sql reduces the network traffic, it provides you with ability to control the flow of constructs.
  4. Pl/sql application can run on any platform on which oracle runs.
  5. Pl/sql is not an oracle product in its own write.

Advantages: 

  1. You can program with procedural language with control structures.
  2. Pl/sql can handle errors.
  3. Easy maintenance.
  4. Improved data security and integrity.
  5. Improved performance.
  6. Improved code clarity.
  7. Pl/sql is portable.
  8. You can declare variables.
  9. It reduces network traffic.

Q25) What is ref cursor?

Answer: 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.

Q26) What is the basic structure of pl/sql.

Answer: Declare——Optional
Variable declaration
Cursor declaration
User defined exceptions
Begin———-Mandatory
Sql statements
Application or business logic
Exception ——-Optional
Handling Errors
End; ———-Mandatory

Q27) What will happen after commit statement?

Answer: 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.

Q28) What is trigger,cursor,functions with example.

Answer: 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;
/

Q29) What is the cursor attributes used in pl/sql.

Answer: 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 Evaluates to true if the cursor is open
%NOTFOUND Boolean Evaluates to true if the most recent fetch does not return a row
%FOUND Boolean Evaluates to true if the most recent fetch returns a row; complement of % not found
%ROWCOUNT Boolean Evaluates to the total number of rows returned so far

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

Answer: 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.

Q31) What are two parts of package?

Answer: 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.

Q32) What is raise_application_error?

Answer: 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.

Q33) What is cursor .why it is recquired ?

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

Q34) What is cursor for loop?

Answer: 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;

Q35) What is a stored procedure?

Answer:

  1. A procedure is a named pl/sql block that can accept parameters and be invoked. You can use it to perform an action.
  2. A procedure has a header, a declaration section, an executable section, and an optional exception handling section.
  3. A procedure can be compiled and stored in the data base as a schema object.
  4. Procedures promote reusability and maintainability. When validated they can be used an any number of applications.
  5. If the requirements change, only the procedure needs to update.

Q36) What is an exception? What are types of exception?

Answer: 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

  1. Predefined oracle server: One of approximately 20 errors that occur most often in pl/sql code.
  2. Non-predefined oracle server: Any other standard orale server error.

Explicity raised

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

Q37) Where are predefined exception are stored?

Answer: Predefined exceptions were stored in the oracle server.

Q38) What is pragma_exception_init explain the usage?

Answer: 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.

Q39) What are the return values of function sqlcode and sqlerm ?

Answer: 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.

Q40) Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why?

Answer: 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.

Q41) What are the pl/sql statements used in cursor?

Answer: Declaring a CURSOR
Open the CURSOR
FETCH the rows from the CURSOR
CLOSE the CURSOR

Q42) What are the datatype used in pl/sql?

Answer: 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.

Q43) What is database triggers and name some usage?

Answer: 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.

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

Answer: 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.

Q45) What is overloading of procedure?

Answer: 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.
Example: DBMS_OUTPUT .PUT_LINE.

Q46) What is the starting oracle error number ?

Answer: ORA-00000 is the staring oracle error number.

Q47) What is meant by forward declaration in functions?

Answer:

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

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

Answer:

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

Q49) 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)

Answer: Yes such declaration is possible .Explanation with example.

  • 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
  • Number (9, -11) means that there are 9 digits whereas the rest is zero padded towards left of the decimal point Like 99999999900000000000.0

Q50) Explain how procedure and functions are called in a pl/sql block ?

Answer: Procedure can be called in the following ways:

  • CALL <procedure name> direct
  • EXCECUTE <procedure name> from calling environment
  • <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways:

  • EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.
  • As part of an SQL/PL SQL Expression.

Q51) Explain the types of cursor?

Answer: 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.

Q52) Explain the usage of WHERE CURRENT OF clause in cursors?

Answer: 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 include the for update clause in the cursor query on open.
Syntax: Where current of cursor;

Q53) Give the structure of the function?

Answer: 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;

Q54) Give the Structure of the procedure?

Answer: CREATE OR REPLACE PROCEDURE procedure_name(Optional Parameters)
IS / AS
[PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations] BEGIN
Executable statements [EXCEPTION exception handlers] END ;

Q55) Explain rowid,rownum ?What are the pseduocolumns we have?

Answer:

  • 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

Q56) How packaged procedures are called from the stored procedure or anonymous block?

Answer: PACKAGE NAME.PROCEDURE NAME (parameters);
Variable: = PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE

Q57) How to disable multiple triggers if a table at a time?

Answer: ALTER table table_name DISABLE ALL TRIGGERS

Q58) How we can create a table through procedure?

Answer: 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;

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

Answer: 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.

Q60) What are integrity constraints?

Answer: 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.

Q61) Name the table where characteristic of package,procedure and functions are stored?

Answer: user_objects
user_source
user_dependencise

Q62) State the advantage and disadvantage of cursor?

Answer: Cursor is nothing but it’s a memory area of executing sql or oracle statements.
Advantage:

  • We can retrieve more than one record by one query.
  • Its use our RAM so retrieval is fast.
  • By using Collection we can retrieve the bundle of data in cursor.
  • A cursor is an oracle variable.
  • It is similar to a 2D Array.
  • 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.

Q63) State the difference between implicit and explicit cursor?

Answer:
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.

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

Answer:

  • %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.
  • 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.

Q65) What are the advantages of stored procedure?

Answer: 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;

  • 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;
  • 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;
  • 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;
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.

Q66) Difference between %type and %row type.

Answer: %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.

Q67) What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?

Answer:

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

Q68) What is the difference between procedure and functions?

Answer:
FUNCTIONS:

  • 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.
  • Function can be called from SQL statements where as procedure can not be called from the sql statements
  • Functions are normally used for computations where as procedures are normally used for executing business logic.
  • You can have DML (insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.
  • Function returns 1 value only. Procedure can return multiple values (max 1024).

PROCEDURES:

  • 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.
  • Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
  • Stored procedure is pre-compiled execution plan where as functions are not.
  • 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.

Q69) What is pl/sql. Advantages of pl/sql.

Answer:

  • Pl/sql is a procedural language. It is an extension of SQL with a design feature of programming languages.
  • Pl/sql offers modern software engineering features such as data encapsulation, exception handling information hiding, and object orientation.
  • pl/sql reduces the network traffic, it provides you with ability to control the flow of constructs.
  • Pl/sql application can run on any platform on which oracle runs.
  • Pl/sql is not an oracle product in its own write.

Advantages: 

  • You can program with procedural language with control structures.
  • Pl/sql can handle errors.
  • Easy maintenance.
  • Improved data security and integrity.
  • Improved performance.
  • Improved code clarity.
  • Pl/sql is portable.
  • You can declare variables.
  • It reduces network traffic.

Q70) What is ref cursor?

Answer: 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.

Q71) What is the basic structure of pl/sql.

Answer: Declare——Optional
Variable declaration
Cursor declaration
User defined exceptions
Begin———-Mandatory
Sql statements
Application or business logic
Exception ——-Optional
Handling Errors
End; ———-Mandatory

Q72) What will happen after commit statement?

Answer: 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.

Q73) What is trigger,cursor,functions with example.

Answer: 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;
/

Q74) What is the cursor attributes used in pl/sql.

Answer: 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 Evaluates to true if the cursor is open
%NOTFOUND Boolean Evaluates to true if the most recent fetch does not return a row
%FOUND Boolean Evaluates to true if the most recent fetch returns a row; complement of % not found
%ROWCOUNT Boolean Evaluates to the total number of rows returned so far

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

Answer: 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.

Q76) What are two parts of package?

Answer: 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.

Q77) What is raise_application_error?

Answer: 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.

Q78) What is cursor .why it is recquired ?

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

Q79) What is cursor for loop?

Answer: 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;

Q80) What is a stored procedure?

Answer:

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

Q81) What is an exception? What are types of exception?

Answer: 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

  1. Predefined oracle server: One of approximately 20 errors that occur most often in pl/sql code.
  2. Non-predefined oracle server: Any other standard orale server error.

Explicity raised

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

Q82) Where are predefined exception are stored?

Answer: Predefined exceptions were stored in the oracle server.

Q83) What is pragma_exception_init explain the usage?

Answer: 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.

Q84) What are the return values of function sqlcode and sqlerm ?

Answer: 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.

Q85) Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why?

Answer: 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.

Q86) What are the pl/sql statements used in cursor?

Answer: Declaring a CURSOR
Open the CURSOR
FETCH the rows from the CURSOR
CLOSE the CURSOR

Q87) What are the datatype used in pl/sql?

Answer: 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.

Q88) What is database triggers and name some usage?

Answer: 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.

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

Answer: 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.

Q90) What is the overloading of the procedure?

Answer: 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.
Example: DBMS_OUTPUT .PUT_LINE.

Q91) What is %ROWTYPE?

Answer: The %ROWTYPE attribute is used to declare the variable which has the structure of the records in the particular table. The data type of the variable is the same as the columns in the table.
Example: student_rec student%ROWTYPE;
For example, here variable is student_rec which has the data type of all the columns in table student.

Q92) What is %TYPE?

Answer: The %TYPE attribute is used to declare the which contains the value of the particular column in a table. The data type of the variable is the same as the column in the table.
Example : stud_var student.student_id%TYPE;
For example, here variable is student_var which has the data type of the column student_id from table student.

Q93) What is the difference between Syntax error and Runtime error?

Answer: The syntax error is detected by the PL/SQL compiler while typing or before executing the code. Some of the Syntax error are incorrect spelling, missing semicolon etc.
The runtime error is detected by exception handing in PL/SQL Block. This error occurs after executing the programs

Q94) How many triggers can be used in one table?

Answer: Maximum 12 triggers are allowed per table.

Q95) Tell us the difference between an anonymous block and subprograms?

Answer: The anonymous blocks are not stored into the database and it is executed without naming the blocks or unsaved. The subprograms are saved with a specific name and stored into the database. Some of the subprograms are functions, procedure

Q96) What are the predefined packages available in the PLSQL?

Answer: Some of the predefined packages available in PLSQL are DBMS_OUTPUT, DBMS_RANDOM DBMS_SQL, DBMS_TRACE and UTL_FILE         7. How to create PL/SQL packages?
Packages have two parts
1.Package Specification 2.Package Body.
Package Specification is a must whereas Package Body is optional. First, we need to create a Package Specification then Package Body. Package Specification contains only the definition of a package such as declare a variable, constant and define the functions and procedure inside the package. The Package body contains the actual implementation of the functions or procedure.

Q97) What is the use of PL/SQL Package?

Answer: PL/SQL package is a schema object that contains the definitions for a group of functionalities. Package includes constants, variables, Exception, function, and procedures, the packages are compiled and stored in the database. The main use of package is to manage authorization easily, Improve application performance and make code more modular

Q98) What are the pre-defined exceptions?

Answer: Some of the predefined exceptions are NO_DATA_FOUND, ZERO_DIVIDE, TOO_MANY_ROWS, CASE_NOT_FOUND, and ROWTYPE_MISMATCH

Q99) When Triggers in PLSQL is invoked?

Answer: Trigger is a stored procedure that is automatically invoked when any event occurs.

Q100) What is the difference between SYSDATE and USER keywords?

Answer: The keyword SYSDATE is refers to current system date whereas USER is used to refer the timing of current user logged into the session.This is used to monitor the changes happening in the tables.

Q101) How to create a function?

Answer: The Standalone function is created using the create function command . The syntax to create a Create function as below.
CREATE OR REPLACE FUNCTION function_name
RETURN return_datatype
{IS | AS}
DECLARE
VARIABLE DATATYPE;
BEGIN
function_body
END function_name;

Q102) What are the predefined functions available in PLSQL?

Answer: Some of the predefined functions available in CONCAT, REPLACE, SUM, MIN, SYDATE, CURRENT_DATE, etc.

Q103) Difference between Stored Function and Stored Procedure in PLSQL?

Answer: In Stored function, it is mandatory to return the value whereas it is optional in a stored procedure for returning the value.
A stored procedure can have both input and output parameter but stored function will have only input parameter.
In the Stored procedure, we can use exception handling and it is not possible in Stored functions.

Q104) What is the command to change the date format by altering the session?

Answer: For 24 hours format – alter session set NLS_DATE_FORMAT = ‘dd/MM/yy hh24:mi:ss’;For 12 hours format – alter session set NLS_DATE_FORMAT = ‘dd/ MM /yy hh12:mi:ss am’;For 12 hours format – alter session set NLS_DATE_FORMAT = ‘dd/ MM /yy hh12:mi:ss pm’;

Q105) Does rollback is possible, after committed the changes in the database?

Answer: No, it is not possible to do the rollback after committed the changes. The rollback should be done for the specific or group of command before committing the changes in a database.

Q106) What is the use of the DBMS_OUTPUT package in PLSQL?

Answer: The DMS_OUTPUT package is used to display the PLSQL output to the screen. Some of the sun programs in the DBMS_OUPUT is get_line , put_Line , new_line etc. The put_line is a procedure available in the DBMS_OUPUT package, which is used to display the information in the line.

Q107) What is the use of the SET SERVEROUPUT ON?

Answer: The command SET SERVEROUPUT ON is used to enable the DBMS_OUTPUT. By using this command, the output will be printed in a script out panel instead of DBMS output panel.

Q108) What is the use of || operator?

Answer: The || operator is used to concate the strings. The || operator is used in select statements and DBMS_OUPUT.put_line.
For example: select address_one || ‘ ,’ || address_two as Employee_Address
from Employee;

Q109) Write the command to print the current system date?

Answer: SELECT SYSDATE FROM DUAL;

Q110) How to view the predefined functions and procedures in PLSQL?

Answer: The Predefined functions and procedure will be saved under the table ALL_SOURCE. The function and procedure name should be given in the uppercase(in select command) to view them. The command to view the prefined function and procedure as below
Select * from all_source where name=’FUNCTION_NAME’;

Q111) How to view the user-defined functions and procedures in plsql?

Answer: The user-defined functions and procedures will be saved under the table USER_SOURCE. The function and procedure name should be given in the uppercase(in select command) to view them. The command to view source code of the user-defined function and procedure as below
Select text from user_source where name=’PROCEDURE_NAME’;

Q112) What are the data types available in PLSQL?

Answer: Two types of datatype available in PLSQL:

  1. Scalar data types – int, number, DATE, VARCHAR, CHAR, Boolean, etc
  2. Composite datatype – Records, TABLES, etc.

Q113) What are exceptions and its types?

Answer: Exception is used to handle the error part of the PLSQL. They are two types of Exceptions available.
1.Predefined Exception.
2.user-defined Exception.

Q114) What is the syntax to disable the trigger?

Answer: ALTER TRIGGER TRIGGER_NAME DISABLE;