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.

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

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 statements;
RETURN value;
END function name;

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 ;

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 , the second has , 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 < ;

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

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

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

ALTER table table_name DISABLE ALL TRIGGERS

How we can create a table through procedure?

You can create table from procedure using Execute immediate command.
CREATE procedure p is
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE temp AS SELECT * FROM emp ‘ ;
END;

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.

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.

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

Yes, such declaration is possible. Explanation with example.

Number (, ) means there are digits after decimal. However, as the max precision is so the rest are zero padded. Like .
Number (, -) means that there are digits whereas the rest is zero padded towards left of the decimal point Like .

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

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.

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.

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

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

user_objects
user_source
user_dependencise

State the advantage and disadvantage of cursor?

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 D 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

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.

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

Pl/sql is procedural language. It is an extension of sql with 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.

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.

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

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

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.
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 = THEN
:NEW.comm := :NEW.sal * .;
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 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=;
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;
/

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

What are the advantages of stored procedure?

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.

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.

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.

What is the difference between procedure and functions?

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 value only. Procedure can return multiple values (max ).

PROCEDURES:

Stored Procedure supports deferred name resolution. Example while writing a stored procedure that uses table named tabl and tabl 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.

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

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.

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.

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 the syntax:
Error_number : is a user specified number for the exception between – and -.
Message: is the user specified message for the exception.It is a character string up to 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.

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.

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 ;
Statement ;
…….
End loop;

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.

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

Where are predefined exception are stored?

Predefined exceptions were stored in the oracle server.

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.

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.

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.

What are the pl/sql statements used in cursor?

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

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.

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.

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.

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

What is the starting oracle error number ?

ORA- is the staring oracle error number.

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.

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.

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

Yes such declaration is possible .Explanation with example.

Number (, ) means there are digits after decimal .However as the max precision is so the rest are zero padded .Like .
Number (, -) means that there are digits whereas the rest is zero padded towards left of the decimal point Like .

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

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.

Explain the types of cursors?

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.

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

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;

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 ;

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 , the second has , 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 < ;

CURRVAL and NEXTVAL
LEVEL
ROWID
ROWNUM
TIMESTAMP
SYSTIME
USER
UID

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

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

ALTER table table_name DISABLE ALL TRIGGERS

How we can create a table through procedure?

You can create table from procedure using Execute immediate command.
CREATE procedure p is
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE temp AS SELECT * FROM emp ‘ ;
END;

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

What are integrity constraints?

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

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

user_objects
user_source
user_dependencise

State the advantage and disadvantage of cursor?

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 D 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

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.

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

What are the advantages of stored procedure?

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.

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.

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.

What is the difference between procedure and functions?

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 value only. Procedure can return multiple values (max ).

PROCEDURES:

Stored Procedure supports deferred name resolution. Example while writing a stored procedure that uses table named tabl and tabl 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.

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

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.

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.

What is the basic structure of pl/sql.

What will happen after commit statement?

What is trigger,cursor,functions with example.

CREATE OR REPLACE TRIGGER emp_comm_trig
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :NEW.deptno = THEN
:NEW.comm := :NEW.sal * .;
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=;
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;
/

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 attributes 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

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

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.

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 – and -.
Message: is the user specified message for the exception.It is a character string up to 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.

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.

What is cursor for loop?

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.

What is an exception? What are types of exception?

Predefined oracle server: One of approximately 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.

Where are predefined exception are stored?

Predefined exceptions were stored in the oracle server.

What is pragma_exception_init explain the usage?

What are the return values of function sqlcode and sqlerm ?

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.

What are the pl/sql statements used in cursor?

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

What are the datatype used in pl/sql?

What is the difference between Syntax error and Runtime error?

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

How many triggers can be used in one table?

Maximum triggers are allowed per table.

Tell us the difference between an anonymous block and subprograms?

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

What are the predefined packages available in the PLSQL?

Some of the predefined packages available in PLSQL are DBMS_OUTPUT, DBMS_RANDOM DBMS_SQL, DBMS_TRACE and UTL_FILE . How to create PL/SQL packages?
Packages have two parts
.Package Specification .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.

What is the use of PL/SQL Package?

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

What are the pre-defined exceptions?

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

When Triggers in PLSQL is invoked?

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

What is the difference between SYSDATE and USER keywords?

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.

How to create a function?

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;

What is database triggers and name some usage?

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.

What is the overloading of the procedure?

What is %ROWTYPE?

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.

What is %TYPE?

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.

What are the predefined functions available in PLSQL?

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

Difference between Stored Function and Stored Procedure in PLSQL?

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.

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

For hours format – alter session set NLS_DATE_FORMAT = ‘dd/MM/yy hh:mi:ss’;For hours format – alter session set NLS_DATE_FORMAT = ‘dd/ MM /yy hh:mi:ss am’;For hours format – alter session set NLS_DATE_FORMAT = ‘dd/ MM /yy hh:mi:ss pm’;

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

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.

What is the use of the DBMS_OUTPUT package in PLSQL?

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.

What is the use of the SET SERVEROUPUT ON?

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.

What is the use of || operator?

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;

Write the command to print the current system date?

SELECT SYSDATE FROM DUAL;

How to view the predefined functions and procedures in PLSQL?

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’;

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

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’;

What are the data types available in PLSQL?

Two types of datatype available in PLSQL:

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

What are exceptions and its types?

Exception is used to handle the error part of the PLSQL.
They are two types of Exceptions available.

Predefined Exception.
user-defined Exception.

What is the syntax to disable the trigger?

ALTER TRIGGER TRIGGER_NAME DISABLE;

What is PL/SQL?

A procedural language where code can be executed in blocks. It is an extension of SQL.

What are the differences between PL/SQL and SQL?

SQL	PL/SQL
SQL is a query language to interact with the database.	It is an extension of SQL which supports procedures, functions and many more features.
Supports only simple queries that can perform insert, update, delete on tables.	Can perform complex tasks like a high-level programming language, for example, while loop, if-else statements, etc…
SQL statements can be executed only one at a time, thereby making it a time-consuming process.	The entire block of statements is sent to the database server at once to be executed, saving time and increasing efficiency.
No provision for error handling.	Customized error handling is possible.

What is the basic structure of PL/SQL?

[

DECLARE

]

--declaration statements (optional)

BEGIN

--execution statements

[

EXCEPTION

]

--exception handling statements

END

;

Define cursor and its use.

A cursor is a pointer to a memory area assigned by Oracle to process SQL statements. The cursor is used to hold records returned by the SQL query. There are types of cursors – implicit and explicit.

Why do we use database triggers? Give the syntax of a trigger.

The trigger is a stored procedure that is automatically invoked when an event happens. The event could be: insert, update, delete, etc…

Syntax –

create

trigger

[trigger_name]

[

before

|

after

]

on

[table_name]

[

for

each

row

]

[trigger_body]

How do you compile PL/SQL code?

Firstly, the syntax check is performed. When the developer corrects any syntax errors, Oracle binds all the variables holding data with a storage address. Finally, the p-code generation process takes place.

Explain exception handling in PL/SQL.

PL/SQL offers customized exception handling. When an error occurs, an error handling code is included in the program itself. There are types of exceptions –

Pre-defined exceptions – common errors that are already defined. Example – NO_DATA_FOUND
Undefined exceptions – the errors that do not have predefined names.
User-defined exceptions – handled by the code written by the user.

Tell about a few data types in PL/SQL.

There are many data types –

Scalar types – primitive data types like CHAR, DATE, LONG, VARCHAR etc…
Composite – these are made up of other data types and can be easily updated. Example, RECORD, TABLE etc…
Reference data types like CURSOR
Large object types – BLOB, CLOB etc…

What is the difference between %TYPE and %ROWTYPE? Give an example.

%TYPE	%ROWTYPE
The attribute that declares a variable of the same data type as of a table column.	The attribute that declares a variable of type RECORD having the same structure as a table row. The row is the RECORD that contains fields having the same data types and names as the columns of a table or view.
Example – DECLARE studentId students.student_id%TYPE;	Example – DECLARE stud_rec students.%ROWTYPE;

What constitutes a PL/SQL package?

Packages are schema objects that place functions, procedures, variables, etc… in one place. Packages should have –

Package specifications
Package body

List some schema objects that are created using PL/SQL.

Database links, triggers, stored procedures, functions and packages, views, synonyms, external procedure libraries, sequences, etc…

What are the various predefined exceptions?

Predefined exceptions are internally defined exceptions that occur during the execution of a program. For example, PL/SQL raises NO_DATA_FOUND when there are no rows returned upon a select operation, and if more than one row is returned using a select statement, TOO_MANY_ROWS error is generated. Some more examples:

COLLECTION_IS_NULL: when a collection is null
CURSOR_ALREADY_OPEN: When a cursor is already open
LOGIN_DENIED: When login is incorrect or permission is not there.

What is the difference between syntax and runtime errors?

Syntax error	Runtime error
These are compile-time errors found by the compiler.	These are not detected by the compiler and cause the program to give an incorrect result.
The code doesn’t build and run until these issues are resolved.	The code is compiled and run, and if an error occurs, the program stops halfway.
Some examples are missing semicolons or brackets (;, {}), incorrect spelling of classes, keywords etc.	Examples are null pointer exceptions, dividing a number by zero, array index out of bounds, etc.
int x = String name = null; In the first line, a semicolon is missing which the compiler will catch	String name = null; if(name.equals(“hackr.io”)){….} Since name is null, the exception will be caught during runtime when the code is executed

What are the various packages available for PL-SQL Developers?

The several packages available for PL/SQL developers are:

DBMS_ALERT	alert an application using triggers when particular database values change. The alerts are transaction-based and asynchronous.
DBMS_OUTPUT	display output from PL/SQL blocks, packages, subprograms and triggers. Mostly used for displaying PL/SQL debugging information.
DBMS_PIPE	different sessions communicate over named pipes using this package. The procedures PACK_MESSAGE and SEND_MESSAGE pack a message into a pipe, then send it to another session.
HTF and HTP	allow PL/SQL programs to generate HTML tags.
UTL_FILE	lets PL/SQL programs read and write OS text files.
UTL_HTTP	allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in HTML format.
UTL_SMTP	allows PL/SQL programs to send emails over SMTP.

Explain Character Functions?

Character functions are functions that manipulate character data. These are more popularly called as string functions. Example:

LEFT	Returns the mentioned number of characters from left of a string. LEFT(value, NoOfChars). Example	LEFT(‘Hackr’, ) will give Hack.
RIGHT	return specified number of characters from right. RIGHT(value, NoOfChars). Example	RIGHT(‘banker’, ) will return er.
SUBSTRING	selects data from any part of the string. SUBSTRING(value, StartPosition, NoOfChars). Example	SUBSTRING(‘hackr.io’,,) will return hackr.
LTRIM	trims white spaces from the left. Example	LTRIM(‘ hackr.io’) will return hackr.io.
RTRIM	trims white spaces from the right. Example	RTRIM(‘hackr.io ‘) will return hackr.io.
UPPER	converts all the characters to uppercase. Example	UPPER(‘hackr.io’) returns HACKR.IO.
LOWER	converts all the characters to lowercase. Example	LOWER(‘HACKR.IO’) returns hackr.io.

What is the use of SYSDATE and USER keywords? Explain with examples.

SYSDATE:

returns the current date and time on the local database server. The syntax is SYSDATE. If we have to extract part of the date, then we use the TO_CHAR function. Examples:

SELECT SYSDATE FROM dual;
select customer_id, TO_CHAR(SYSDATE,  'yyyy/mm/dd') from customer where customer_id < ;

USER:

USER returns the user_id of the current session.

Example:

select USER from dual;

What is the difference between SGA and PGA?

SGA	PGA
System Global Area	Program Global Area
Contains data and control information for one Oracle database instance	Contains data and control information exclusively for a single Oracle process
Shared memory region for components	Non-shared memory region
example: cached data blocks and SQL areas	Example: session memory, SQL work area

Explain the uses of Merge with Syntax in PL-SQL.

Merge reduces the number of table scans and performs parallel operations if required. MERGE inserts or updates data conditionally from one table to another. For example,

MERGE INTO  orders o

USING customer c

ON (o.cust_id = c.cust_id)

WHEN MATCHED THEN

UPDATE SET o.del_address = c.address

WHEN NOT MATCHED

THEN INSERT (cust_id, address)

VALUES (c.emp_id, c.address);

In this example, if a record with the matching condition is found, then the address of the same record is updated, else a new row is inserted.

Explain the benefits of PL-SQL packages.

There are many benefits of using PL/SQL packages:

Better code management as packages provide a container for sub-programs
Top-down application design approach with separation of implementation and specifications (interface)
If there are any changes to the subprograms, there is no need to change the dependent objects or recompile the entire package
Accessibility can be specified (private/public) thus maintaining the security of the code
Data can be maintained across transactions for the entire session without storing in database
At the first call of the subprogram, the entire package is loaded, so there is no need for disk I/O for subsequent calls, thus giving better performance.

Explain the difference between ROLLBACK and ROLLBACK TO statements?

ROLLBACK command rolls back all the changes from the beginning of the transaction. In ROLLBACK TO, the transaction is rolled back (or undone) only till a point known as the SAVEPOINT. The transactions before the SAVEPOINT cannot be undone, and the transaction remains active even when the command is given.

Explain the difference between procedure and function.

Function	Procedure
The function is compiled every time it is called for execution.	Procedures are pre-compiled and saved. They execute the pre-compiled code whenever called.
Can be called from SQL statements.	Can not be called from SQL statements.
The function has to return a value.	Need not return any value.
Generally used for computation purpose.	Used for executing complex business logic.
Can return multiple values using other methods, otherwise, return only a single value.	Can return multiple values
Returns scalar data types.	Returns an int by default.
A stored procedure can not be called from a function	The procedure can call any function
Functions can be embedded in a select statement	Inside a select statement, a procedure cannot be called.
Exception handling is not possible	Try/catch block can be defined inside a procedure

Explain the difference between procedure and trigger.

PROCEDURE	TRIGGER
Called explicitly by a user, trigger or an application	Executed by the DBMS whenever an event occurs in the database.
Can have parameters	Doesn’t have parameters
Cannot be inactive	Can be enabled or disabled on need basis
Creation – CREATE PROCEDURE	Creation – CREATE TRIGGER

What are the different types of cursors in PL/SQL?

There are two types of cursors –

Implicit cursor – PL/SQL applies implicit cursors for INSERT, UPDATE, DELETE and SELECT statements returning a single row.
Explicit cursor – created by a programmer for queries returning more than one row. Syntax–

CURSOR is

SELECT statement;

OPEN ;

FETCH INTO ;

CLOSE ;

What are the different types of constraints?

Not NULL
Unique
Primary key
Foreign key
Check

What are the differences between triggers and constraints?

TRIGGERS	CONSTRAINTS
Stored as separate objects	A constraint on a table is stored along with the table definition
Triggers are fired upon an event; hence they are fired after constraints	Constraints are fired as soon as the the table is used.
Perform table to table comparison, hence faster	Performs memory location to table the comparison which is slow leading to low performance.
Trigger is for the entire table	The constraint is for a column of the table
They are just stored procedures that get automatically executed, hence don’t check for data integrity.	Prevent duplicate and invalid data entries

Explain the PL/SQL block with an example.

PL/SQL block consists of three sections: declaration, executable and exception-handling sections. The executable section is mandatory. There are two types of blocks: named and anonymous.

Named blocks are functions and procedures which are stored in the database server and can be reused. Anonymous blocks are for one time use and are not stored in the server. Example:

DECLARE

message VARCHAR():= 'Welcome to PL/SQL';
byzero NUMBER;

BEGIN

DBMS_OUTPUT.put_line (message);
byzero := /;

EXCEPTION

WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);

END

;

Explain PL/SQL Records.

Records contain a set of data of various data types that can be related to each other as fields. Three types of records that are supported in PL/SQL are table-based records, programmer-based records, and cursor-based records.

Explain the difference between commit and savepoint.

COMMIT – is used to make the database changes permanent. All the save points are erased and the transaction ends. Once committed, a transaction cannot be rolled back.

SAVEPOINT – is used to set points during a transaction to which a programmer can roll-back later. it is helpful when there is a series of transactions that can be divided into groups having a savepoint.

What is the difference between actual and formal parameters?

The parameters that are used to call a procedure are called as actual parameters. Example –

get_student_details(stud_name, dob); -- here stud_name and dob are actual parameters.

The variables declared in a procedure header used in the body are called formal parameters. Example –

PROCEDURE get_student_details (dob DATE) IS – here stud_name is a formal parameter.

How is a DECLARE statement used?

DECLARE is used as the first statement for stand-alone files that consist of anonymous block of code which are not stored procedures, functions or triggers. Example –

DECLARE

num NUMBER();

num NUMBER();

BEGIN

-- logic goes here

END

;

Write a simple procedure to select some records from the database using some parameters.

Example code –

CREATE PROCEDURE get_customer_details @agenvarchar(), @citynvarchar()

AS

BEGIN

SELECT* FROM customers WHERE age = @age ANDcity = @city;

END

;

Explain the error ORA-.

The error end-of-file on communication channel ORA- means that there is a broken connection between the client and server channels. It could be a timeout because of which the connection was lost. You can troubleshoot by pinging the server and checking the connectivity.

Can you use IF statement inside a SELECT statement? How?

Yes, we can do so using the DECODE keyword in versions and above. Example –

SELECT day_of_week,

DECODE (number, , 'Sunday',

, 'Monday',

, 'Tuesday',

, 'Wednesday',

, 'Thursday',

, 'Friday',

, 'Saturday',

'No match') result FROM weekdays;

Tell us about SQLCODE and SQLERRM.

SQLCODE and SQLERRM are used to trace exceptions that are not explicitly handled in the program. These are globally defined variables. SQLCODE returns the error code while SQLERRM returns the corresponding error message.

What is rollback? How is it different from rollback to statement?

Rollback erases all the database changes including all the savepoints. It ends a transaction.

‘Rollback to’ rollbacks the changes up to the savepoint mentioned in the code. The transaction will still be active.

What is IN OUT parameter?

IN OUT parameter mode passes a value to a subprogram and returns an updated value.

Is it possible to accept user inputs during runtime? How?

Yes, it is possible. Use ACCEPT keyword to take inputs from the user. Example –

ACCEPT age number prompt ‘Enter your age:’

Give a simple way to run a query faster.

By using ROWID. It is not a physical column but the logical address of a row. It contains the block number, file number and row number thereby reducing I/O time hence making query execution faster.

What are some of the pre-defined exceptions in PL/SQL?

ZERO_DIVIDE, NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, DUP_VAL_ON_INDEX etc…

How do you trace the PL/SQL code?

You can trace through DBMS_* methods like

DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR

How to restrict string length in PL/SQL?

Use CHAR (NUMBER) to get fixed length for a variable. Example – CHAR (). If the length of the string is less than the specified number, it will be padded with white spaces.

What is the purpose of the UTL_FILE package in PL/SQL?

By using this package, developers can get the code read and write files to and from the computer. For doing this, the developer will need access grant from DBA user.

What are DBMS_OUTPUT and DBMS_DEBUG?

Both can be used for debugging the code. DBMS_OUTPUT prints the output to console whereas DBMS_DEBUG prints it to a log file.

List some cursor attributes in PL/SQL.

%ISOPEN: Check if the cursor is open
%ROWCOUNT: Get the number of rows that are updated, deleted or fetched.
%FOUND: Checks if the cursor has fetched any row, returns Boolean.
%NOT FOUND: Checks if the cursor has fetched any row. Returns Boolean.

What is the purpose of NVL?

NVL lets the programmer substitute a value for a NULL value. Example –

NVL (occupation, ‘default')

On a table, how many triggers can be applied?

is the maximum number.

How can we achieve consistency using PL/SQL?

We can achieve consistency by setting the appropriate isolation level. For example, to give read consistency, the isolation level can be set to READ COMMITTED.

What is SYS.ALL_DEPENDENCIES?

SYS.ALL_DEPENDENCIES describes all the dependencies between packages, procedures, functions, triggers accessible to the current user. It shows columns like name, type, dependency_type, referenced_owner, etc…

What is PL SQL ?

PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.

Differentiate between % ROWTYPE and TYPE RECORD.

% ROWTYPE is used when a query returns an entire row of a table or view.

TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.

Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)

e_rec smp %ROWTYPE

Cursor c is select smpno,dept from smp;

e_rec c %ROWTYPE

Explain uses of cursor.

Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.

Show code of a cursor for loop.

Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.

Eg. FOR smp_rec IN C LOOP

totalsal=totalsal+smp_recsal;

ENDLOOP;

Explain the uses of database trigger.

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :

) Audit data modifications.

) Log events transparently.

) Enforce complex business rules.

) Maintain replica tables

) Derive column values

) Implement Complex security authorizations

What are the two types of exceptions.

Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.

Show some predefined exceptions.

DUP_VAL_ON_INDEX

ZERO_DIVIDE

NO_DATA_FOUND

TOO_MANY_ROWS

CURSOR_ALREADY_OPEN

INVALID_NUMBER

INVALID_CURSOR

PROGRAM_ERROR

TIMEOUT _ON_RESOURCE

STORAGE_ERROR

LOGON_DENIED

VALUE_ERROR

etc.

Explain Raise_application_error.

It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.

Show how functions and procedures are called in a PL SQL block.

Function is called as a part of an expression.

total:=calculate_sal(‘b’)

Procedure is called as a statement in PL/SQL.

calculate_bonus(‘b’);

Explain two virtual tables available at the time of database trigger execution.

Table columns are referred as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are available only.

For DELETE related triggers, THEN.column_name values are available only.

For UPDATE related triggers, both Table columns are available.

What are the rules to be applied to NULLs whilst doing comparisons?

) NULL is never TRUE or FALSE

) NULL cannot be equal or unequal to other values

) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)

How is a process of PL SQL compiled?

Compilation process includes syntax check, bind and p-code generation processes.

Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.

Differentiate between Syntax and runtime errors.

A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.

A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.

Explain Commit, Rollback and Savepoint.

For a COMMIT statement, the following is true:

Other users can see the data changes made by the transaction.
The locks acquired by the transaction are released.
The work done by the transaction becomes permanent.

A ROLLBACK statement gets issued when the transaction ends, and the following is true.

The work done in a transition is undone as if it was never issued.
All locks acquired by transaction are released.

It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.

Define Implicit and Explicit Cursors.

A cursor is implicit by default. The user cannot control or process the information in this cursor.

If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.

Explain mutating table error.

It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.

When is a declare statement required?

DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.

How many triggers can be applied to a table?

A maximum of triggers can be applied to one table.

What is the importance of SQLCODE and SQLERRM?

SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.

If a cursor is open, how can we find in a PL SQL Block?

the %ISOPEN cursor status variable can be used.

Show the two PL/SQL cursor exceptions.

Cursor_Already_Open

Invaid_cursor

What operators deal with NULL?

NVL converts NULL to another specified value.

var:=NVL(var,’Hi’);

IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.

Does SQL*Plus also have a PL/SQL Engine?

No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.

What packages are available to PL SQL developers?

DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

Explain basic parts of a trigger.

A triggering statement or event.
A restriction
An action

What are character functions?

INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.

Explain TTITLE and BTITLE.

TTITLE and BTITLE commands that control report headers and footers.

Show the cursor attributes of PL/SQL.

%ISOPEN : Checks if the cursor is open or not

%ROWCOUNT : The number of rows that are updated, deleted or fetched.

%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched

%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.

What is an Intersect?

Intersect is the product of two tables and it lists only matching rows.

What are sequences?

Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.

How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?

Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.

What are the uses of SYSDATE and USER keywords?

SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.

How does ROWID help in running a query faster?

ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.

What are database links used for?

Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.

What does fetching a cursor do?

Fetching a cursor reads Result Set row by row.

What does closing a cursor do?

Closing a cursor clears the private SQL area as well as de-allocates memory

Explain the uses of Control File.

It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.

Explain Consistency

Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.

Differ between Anonymous blocks and sub-programs.

Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.

Differ between DECODE and CASE.

DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.

select decode(totalsal=,’high’,,’medium’) as decode_tesr from smp where smpno in (,,,);

This statement returns an error.

CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.

Explain autonomous transaction.

An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.

There are several situations to use autonomous transactions like event logging and auditing.

Differentiate between SGA and PGA.

SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated % RAM size, but SGA is given % RAM size.

What is the location of Pre_defined_functions.

They are stored in the standard package called “Functions, Procedures and Packages”

Explain polymorphism in PL SQL.

Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.

What are the uses of MERGE?

MERGE is used to combine multiple DML statements into one.

Syntax : merge into tablename

using(query)

on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command

Can queries be executed simultaneously in a Distributed Database System?

Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the phase commit.

Explain Raise_application_error.

It is a procedure of the package DBMS_STANDARD that allow issuing a user_defined error messages from the database trigger or stored sub-program.

What is out parameter used for eventhough return statement can also be used in pl/sql?

Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.

How would you convert date into Julian date format?

We can use the J format string :

SQL > select to_char(to_date(‘-Mar-‘,’dd-mon-yyyy’),’J’) as julian from dual;

JULIAN

Explain SPOOL

Spool command can print the output of sql statements in a file.

spool/tmp/sql_outtxt

select smp_name, smp_id from smp where dept=’accounts’;

spool off;

Mention what PL/SQL package consists of?

A PL/SQL package consists of

PL/SQL table and record TYPE statements
Procedures and Functions
Cursors
Variables ( tables, scalars, records, etc.) and constants
Exception names and pragmas for relating an error number with an exception
Cursors

Mention what are the benefits of PL/SQL packages?

It provides several benefits like

Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
Guaranteeing transaction integrity: It provides a level of transaction integrity
Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.

Mention what are different methods to trace the PL/SQL code?

Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes

DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR
trcsess and tkproof utilities

Mention what does the hierarchical profiler does?

The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes

Distinct reporting for SQL and PL/SQL time consumption
Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
Multiple interactive analytics reports in HTML format by using the command line utility
More effective than conventional profiler and other tracing utilities

Mention what does PLV msg allows you to do?

The PLV msg enables you to

Assign individual text message to specified row in the PL/SQL table
It retrieves the message text by number
It substitutes automatically your own messages for standard Oracle error messages with restrict toggle
Batch load message numbers and text from a database table directly PLV msg PL/SQL table

Mention what is the PLV (PL/Vision) package offers?

Null substitution value
Set of assertion routines
Miscellaneous utilities
Set of constants used throughout PL vision
Pre-defined datatypes

) Mention what is the use of PLVprs and PLVprsps?
PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality
PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.

Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?

With a single program call – “fcopy procedure”, you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program “filepstab”.

Explain how exception handling is done in advance PL/SQL?

For exception handling PL/SQl provides an effective plugin PLVexc. PLVexc supports four different exception handling actions.

Continue processing
Record and then continue
Halt processing
Record and then halt processing

For those exceptions that re-occurs you can use the RAISE statement.

Mention what problem one might face while writing log information to a data-base table in PL/SQL?

While writing log information to a database table, the problem you face is that the information is only available only once the new rows are committed to the database. This might be a problem as such PLVlog is usually deployed to track errors and in many such instances the current transaction would fail or otherwise needed a rollback.

Mention what is the function that is used to transfer a PL/SQL table log to a database table?

To transfer a PL/SQL table log a database log table function “PROCEDURE psdb” is used.

When you have to use a default “rollback to” savepoint of PLVlog?

The default “rollback to” savepoint of PLVlog is used when the users has turned on the rollback activity and has not provided an alternative savepoint in the call to put_line. The default savepoint is initialized to the c none constant.

Why PLVtab is considered as the easiest way to access the PL/SQL table?

The PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy.

Mention what does PLVtab enables you to do when you showthe contents of PL/SQL tables?

PLVtab enables you to do following things when you show the contents of PL/SQL tables

Display or suppress a header for the table
Display or suppress the row numbers for the table values
Show a prefix before each row of the table

Explain how can you save or place your msg in a table?

To save msg in a table, you can do it in two ways

Load individual messages with calls to the add_text procedure
Load sets of messages from a database table with the load_from_dbms procedure

Mention what is the use of function “module procedure” in PL/SQL?

The “module procedure” enables to convert all the lines of code in a definite program unit with one procedure call. There are three arguments for modules

module_in
cor_in
Last_module_in

Mention what PLVcmt and PLVrb does in PL/SQL?

PL/Vision offers two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.

PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing
PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL

Differentiate PL/SQL and SQL?

Difference between SQL and PL/SQL can be categorized as follows:

SQL	PL/SQL
SQL is a natural language which is very useful for interactive processing.	PL/SQL is a procedural extension of Oracle – SQL.
No procedural capabilities like condition testing, looping is offered by SQL.	PL/SQL supports procedural capabilities as well as high language features such as conditional statements, looping statements, etc.
All SQL statements are executed by the database server one at a time, thus it is a time-consuming process.	PL/SQL statements send the entire block of statements to the database server at the same time, thus network traffic is reduced considerably.
No error handling procedures are there in SQL.	PL/SQL supports customized error handling.

Enlist the characteristics of PL/SQL?

Characteristics of PL/SQL are as follows:

PL/SQL allows access and sharing of the same subprograms by multiple applications.
It is known for the portability of code as code can be executed on any operating system provided that Oracle is loaded on it.
With PL/SQL users can write their own customized error handling routines.
Improved transaction performance with integration to Oracle data dictionary.

What are the data types available in PL/SQL?

Data types define the ways to identify the type of data and their associated operations.

There are types of predefined data types explained as follows:

Scalar Data Types: A scalar data type is an atomic data type that does not have any internal components.
For example
- CHAR (fixed-length character value range between and , characters)
- VARCHAR (variable length character value range between and , characters)
- NUMBER ( fixed-decimal, floating-decimal or integer values)
- BOOLEAN ( logical data type for TRUE FALSE or NULL values)
- DATE (stores date and time information)
- LONG (character data of variable length)
Composite Data Types: A composite data type is made up of other data types and internal components that can be easily used and manipulated. For example, RECORD, TABLE, and VARRAY.
Reference Data Types: A reference data type holds values, called pointers that designate to other program items or data items. For example, REF CURSOR.
Large Object Data Types: A Large Object datatype holds values, called locators, that defines the location of large objects( such as video clips, graphic image, etc) stored out of line.
For example
- BFILE (Binary file)
- BLOB (Binary large object)
- CLOB ( Character large object)
- NCLOB( NCHAR type large object)

Explain the purpose of %TYPE and %ROWTYPE data types with the example?

PL/SQL uses the %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.

For example, the variable m_empno has the same data type and size as the column empno in table emp.

m_empno emp.empno%TYPE;

%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view. For example,

dept_rec dept%ROWTYPE;

This declares a record that can store an entire row for the DEPT table.

What do you understand by PL/SQL packages?

PL/SQL packages are schema objects that group functions, stored procedures, cursors and variables at one place.
Packages have mandatory parts:

Package Specifications
Package body

What do you understand by PL/SQL cursors?

PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements.

PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a program.

Explain cursor types.

There are two types of cursors.

They are explained as follows:

a) Explicit Cursors:

For queries that return more than one row, an explicit cursor is declared and named by a programmer. In order to use explicit cursor in PL/SQL, steps are followed

Declare the cursor
Syntax:
CURSOR <cursor_name> is
SELECT statement;
Here, <cursor_name> is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.

Open the cursor
Syntax:
OPEN <cursor_nam>;
Where, <cursor_name> is the name of the previously defined cursor.

Fetch rows from the cursor
Syntax:
FETCH <cursor_name> INTO <record_list>;
Here, <cursor_name> refers to the name of the previously defined cursor from which rows are being fetched.
<record_list> represents the list of variables that will receive the data being fetched.

Closing the cursor
Syntax:
CLOSE <cursor_name>;Here, <cursor_name> is the name of the cursor being closed.

b) Implicit cursors:

When any SQL statement is executed, PL/SQL automatically creates a cursor without defining such cursors are known as implicit cursors.

For the following statements, PL/SQL employs implicit cursors

INSERT
UPDATE
DELETE
SELECT ( queries that return exactly one row)

When do we use triggers?

The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when the database-related event is performed.

Triggers are mainly required for the following purposes:

To maintain complex integrity constraints
Auditing table information by recording the changes
Signaling other program actions when changes are made to the table
Enforcing complex business rules
Preventing invalid transactions

Explain the difference in the execution of triggers and stored procedures?

A stored procedure is executed explicitly by issuing a procedure call statement from another block via a procedure call with arguments.

The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements happens.

Explain the difference between Triggers and Constraints?

Triggers are different from constraints in the following ways:

Triggers	Constraints
Only affect those rows added after the trigger is enabled.	Affect all rows of the table including that already exist when the constraint is enabled.
Triggers are used to implement complex business rules which cannot be implemented using integrity constraints.	Constraints maintain the integrity of the database.

What is a PL/SQL block?

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

Broadly,

PL/SQL blocks are two types:

(i) Anonymous blocks:

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

DECLARE

num NUMBER();

sq NUMBER();

BEGIN

num:= &Number;

sq := num*num;

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

END;

(ii) Named blocks:

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

Example:

FUNCTION sqr (num IN NUMBER)

RETURN NUMBER is sq NUMBER();

BEGIN

sq:= num*num;

RETURN sq;

END;

Differentiate between syntax and runtime errors?

Syntax errors are the one which can be easily identified by a PL/SQL compiler. These errors can be a spelling mistake, etc.

Runtime errors are those errors in PL/SQL block for which an exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows.

What are COMMIT, ROLLBACK, and SAVEPOINT?

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

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

Affected rows locks are released
Transaction marked as complete
Transaction detail is stored in the data dictionary.

Syntax:

COMMIT;

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

Syntax to rollback a transaction completely:

ROLLBACK;

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

Syntax:

SAVEPOINT <savepoint_name>;

What is the mutating table and constraining table?

Mutating table: A table that is currently being modified by a DML statement like defining triggers in a table.
constraining table: A table that might need to be read from for a referential integrity constraint.

What are INSTEAD OF triggers?

The INSTEAD OF triggers are the triggers written especially for modifying views, which cannot be directly modified through SQL DML statements.

What are expressions?

Expressions are represented by a sequence of literals and variables that are separated by operators. In PL/SQL, operations are used to manipulate, compare and calculate some data. An expression is a composition of ‘Operators’ and ‘Operands’.

Operands:These are an argument to the operators. Operands can be a variable, function call or constant.
Operators:These specify the actions to be performed on operators.Example: ‘+’, ‘*’, etc.

List different type of expressions with the example.

Expressions can be as mentioned below:

Numeric or Arithmetic expressions : * +
Boolean expressions: ‘spot’ LIKE ‘sp%t’
String expressions: LENGTH (‘NEW YORK’|| ‘NY’)
Date expressions: SYSDATE>TO_DATE(’-NOV-’, “dd-mm-yy”)

Write a program that shows the usage of the WHILE loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number ?

DECLARE

n NUMBER;

avg NUMBER := ;

sum NUMBER := ;

count NUMBER := ;

BEGIN

n := &enter_a_number;

WHILE(n<>)

LOOP

count := count+;

sum := sum+n;

n := &enter_a_number;

END LOOP;

avg := sum/count;

DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);

END;

What do you understand by PL/SQL Records?

A PL/SQL records can be referred as a collection of values or say, a group of multiple pieces of information, each of which is of simpler types and can be related to one another as fields.

There are three types of records supported in PL/SQL:

Table based records
Programmer based records
Cursor based records

What are actual parameters and formal parameters?

Actual parameters: The variables or an expression referred to as parameters that appear in the procedure call statement.
Example:
raise_sal(emp_num, merit+ amount);

Here in the above example, emp_num and amount are the two actual parameters.Formal parameters: The variables that are declared in the procedure header and are referenced in the procedure body are called as .
Example:
PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
Here in the above example, emp_id acts as a formal parameter.

What is the difference between ROLLBACK and ROLLBACK TO statements?

The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undo a transaction and release all locks.

On the other hand, a transaction is still active and running after ROLLBACK TO command as it undo only a part of the transaction up till the given SAVEPOINT.

Write a PL/SQL script to display the following series of numbers: ,,……,,?
```
SET SERVER OUTPUT ON

DECLARE

BEGIN

FOR i IN REVERSE ..

LOOP

IF Mod(i,) = THEN

DBMS_OUTPUT.PUT_LINE(i);

END IF;

END LOOP;

END;
```
What are the modes of parameter?

modes of the parameter are IN, OUT, IN OUT.

These can be explained as follows:
- IN parameters: IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value.
- OUT parameters: OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression.
- IN OUT parameters: IN OUT parameters passes initial values to a procedure and return updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value.
Why is %ISOPEN always false for an implicit cursor?

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

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

The outcome of the statement is saved in cursor attributes.
These are:
- SQL%FOUND
- SQL%NOTFOUND
- SQL%ROWCOUNT
- SQL%ISOPEN
What are the ways of commenting in a PL/SQL code?

Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed.

There are two ways to comment in PL/SQL:

) Single line comment:

This comment starts with double –.

Example:
```
DECLARE
num NUMBER();        — it is a local variable.
BEGIN
```
) Multi-line comment:

This comment starts with /* and ends with */.

Example:
```
BEGIN
num := &p_num;         /* This is a host variable used in program body */
……….
END
```
What do you understand by Exception handling in PL/SQL?

When an error occurs in PL/SQL, the exception is raised. In other words, to handle undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION section.

There are types of EXCEPTION:
- Predefined Exceptions: Common errors with predefined names.
- Undefined Exceptions: Less common errors with no predefined names.
- User-defined Exceptions: Do not cause runtime error but violate business rules.
Enlist some predefined exceptions?

Some of the predefined exceptions are:
- NO_DATA_FOUND: Single row SELECT statement where no data is returned.
- TOO_MANY_ROWS: Single row SELECT statement where more than one rows are returned.
- INVALID_CURSOR: Illegal cursor operation occurred.
- ZERO_DIVIDE: Attempted to divide by zero.
What is PL/SQL cursor exceptions?

The exceptions related to PL/SQL cursors are:
- CURSOR_ALREADY_OPEN
- INVALID_CURSOR
Explain the difference between cursor declared in procedures and cursors declared in the package specification?

The cursor declared in the procedure is treated as local and thus cannot be accessed by other procedures.

The cursor declared in the package specification is treated as global and thus can be accessed by other procedures.

So, this brings us to the end of the Oracle PL-SQL Interview Questions blog. This Tecklearn ‘ Top Oracle PL-SQL Interview Questions and Answers’ helps you with commonly asked questions if you are looking out for a job in Oracle PL-SQL or Database Domain. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period.

https://www.tecklearn.com/course/oracle-pl-sql-training/

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?
- The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
- PL-SQL has a market share of 23% globally.
- IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.
What you will Learn in this Course?

Introduction to Oracle SQL
- Database Models
- RDBMS
- Components of SQL
- DataTypes
- DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
- Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
- DML Commands-Insert, Update, Delete
- Order by Clause
- Group Functions
- SET Operators- Union All, Union, Intersect, Minus
- TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
- Types of Views
- Synonyms
- Types of Synonyms
- Indexes
- Types of Indexes
Using Subqueries to Solve Queries
- Subqueries
- Co-Related Subquery
OLAP Functions
- OLAP Features
- Roll Up
- Model Clause
- Dimension Modelling
Conditional Statement
- Block
- Variable Attributes
- Nested Blocks
- Conditional Control Statements
- Iterative Controls (Loop)
Cursor Management
- Types of Cursor
- Writing Explicit cursors
- Explicit cursor functions
- Advance Explicit cursor
- Cursor with parameters
Exception Handling
- Handling Exception
- Handling Exception with PL/SQL Predefined Exceptions,
- User Defined Exceptions
- Non-Predefined Error
- Function for trapping Exception
- Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
- Important Features of Sub-Programs
- Procedure
- Functions
Trigger Management
- Introduction to Triggers
- Types of Triggers
- Compound Triggers
Oracle Job Scheduling
Large Object Functions
- Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting
Got a question for us? Please mention it in the comments section and we will get back to you.

769