Explore topic-wise InterviewSolutions in .

The MAIN ADVANTAGES of using packages are given as follows:

• Encapsulation —Packages provide encapsulation of all the logically related constructs together in a single module, which is easy to code and understand.
• Better application design —Both modularity and encapsulation LEADS to better designing of applications. SEPARATE PACKAGE specification and body helps in simplifying the coding and better definition of the subprograms.
• Hiding —Public and private constructs can be defined and declared so that any definition can be hidden to protect integrity of the package. Any changes in the private constructs only need the package body to be recompiled without the need for the entire application to be recompiled.
• Better performance —A call to any construct within the package loads the entire package in the memory; therefore, enables better performance of the application.

The main advantages of using packages are given as follows:

A package needs to be recompiled COMPLETELY whenever any changes are MADE in the package specification. Any CHANGE to the package body does not require recompilation of the package.

A package needs to be recompiled completely whenever any changes are made in the package specification. Any change to the package body does not require recompilation of the package.

The STATEMENT is GIVEN as FOLLOWS:

The statement is given as follows:

The package specification consists of the declaration of all the global variables, cursors, types, procedures, and FUNCTIONS that are public and required to be called from within or outside the package.

The package body consists of the definition of all the public constructs that are declared within the package specification and the definition of all the PRIVATE variables, cursors, types, procedures, and functions that are required only within the package. FOLLOWING code snippet shows the IMPLEMENTATION of the package specification and the package body:

The package specification consists of the declaration of all the global variables, cursors, types, procedures, and functions that are public and required to be called from within or outside the package.

The package body consists of the definition of all the public constructs that are declared within the package specification and the definition of all the private variables, cursors, types, procedures, and functions that are required only within the package. Following code snippet shows the implementation of the package specification and the package body:

Yes, it is possible to write a package specification without a package body but not the vice VERSA. This is specifically USED if there is a package, which is only used for the declaration of PUBLIC variables, types, constants, and exceptions. In such case, there is no NEED for a package body and only package specification is ENOUGH.

Yes, it is possible to write a package specification without a package body but not the vice versa. This is specifically used if there is a package, which is only used for the declaration of public variables, types, constants, and exceptions. In such case, there is no need for a package body and only package specification is enough.

A local VARIABLE is defined within a package body and is only available to the constructs within the same package.

A GLOBAL variable is one that is declared in the package specification. This can be referenced outside the package and is visible, to external OBJECTS that have ACCESS PRIVILEGE on the package.

A local variable is defined within a package body and is only available to the constructs within the same package.

A global variable is one that is declared in the package specification. This can be referenced outside the package and is visible, to external objects that have access privilege on the package.

No, a complete package is not POSSIBLE to CALL, invoke, or PARAMETERIZE; however, any CONSTRUCT from the package can be called or INVOKED.

No, a complete package is not possible to call, invoke, or parameterize; however, any construct from the package can be called or invoked.

• PUBLIC package constructs are declared in the package specification but defined in the package body and can be invoked from any other OBJECT in the oracle environment.
• PRIVATE package constructs are those that are declared and defined only WITHIN the package body. They are not declared in the package specification and can only be referenced by other constructs, which are part of the same package.
• Private package construct is always defined before a public package construct within the package body.

A package is a group of logically related PL/SQL SUBPROGRAMS BUNDLED together with PL/SQL types and other associated OBJECTS. It consists of the following two parts:

• Package specification —Includes declaration of all the objects and subprograms that are part of the package.
• Package body—Includes the total DEFINITION of all the parts of a package that have been declared in the specification.

The whole package is fully loaded into the memory when any package construct is called for the first time. Therefore, it does not require any DISK input/output on later calls to constructs in the same package.

A package is a group of logically related PL/SQL subprograms bundled together with PL/SQL types and other associated objects. It consists of the following two parts:

The whole package is fully loaded into the memory when any package construct is called for the first time. Therefore, it does not require any disk input/output on later calls to constructs in the same package.

Stored procedures are a collection of SQL, PL/SQL, and Java statements that allow a programmer to shift the code from the application side to the database side. This enables the business rules and application logic to be stored as procedures within the database. The main advantages of using stored procedures and functions are as follows:

•  The complex business rules processing is PERFORMED within the database on the server side. Therefore, this change of processing from the application on the client side to the database on the server side enhances and improves the performance of the application.
•  The procedural code stored within the database can be reused anywhere within the application.
•  When a stored PROCEDURE is executed for the first time, the shared SQL area in the System Global Area (SGA) of the database stores the parsed VERSIONS of the executed commands. After this, whenever the command is executed, it takes the advantage of the parsing, which was performed the first time, and improves the performance of the procedure's execution.
•  Developmental efforts are reduced by keeping the code in a stored procedure. As the business rules are part of the database, they need not be re-written in each code module of the application; and therefore, saves the creation time of the application.
•  As the code is stored in a common place, any change can be EASILY MANAGED; and therefore, reduces the maintenance effort.

Stored procedures are a collection of SQL, PL/SQL, and Java statements that allow a programmer to shift the code from the application side to the database side. This enables the business rules and application logic to be stored as procedures within the database. The main advantages of using stored procedures and functions are as follows:

The PARAMETER list for a FUNCTION or PROCEDURE can be displayed USING the DESCRIBE COMMAND, given as follows:

The parameter list for a function or procedure can be displayed using the DESCRIBE command, given as follows:

The SYNTAX for the SHOW_ERROR COMMAND is as FOLLOWS:

The syntax for the SHOW_ERROR command is as follows:

The TEXT of a PROCEDURE or function specification can be viewed using the USER_SOURCE DATA DICTIONARY VIEW.

The text of a procedure or function specification can be viewed using the USER_SOURCE data dictionary view.

SQL QUERY to view all the PROCEDURES WITHIN a SCHEMA is as follows:

SQL query to view all the procedures within a schema is as follows:

The USER_ERRORS DATA dictionary VIEW is USED to view the COMPILATION errors of the SUBPROGRAMS.

The USER_ERRORS data dictionary view is used to view the compilation errors of the subprograms.

COMPILED object code cannot be VIEWED, as it is not ACCESSIBLE to the user.

Compiled object code cannot be viewed, as it is not accessible to the user.

Database objects of another schema cannot be accessed in a procedure or FUNCTION through roles; however, explicit privileges have to be GRANTED to the USER for those database objects.

Database objects of another schema cannot be accessed in a procedure or function through roles; however, explicit privileges have to be granted to the user for those database objects.

The CREATE OR REPLACE FUNCTION command creates the function if it does not exist and replaces it with the NEW VERSION if it already exists. The DROP FUNCTION command deletes the function altogether from the DATABASE. Any PRIVILEGE assigned on the function remains the same when the CREATE OR REPLACE FUNCTION command is used; HOWEVER, in the case of the DROP FUNCTION command, all the privileges are revoked.

The CREATE OR REPLACE FUNCTION command creates the function if it does not exist and replaces it with the new version if it already exists. The DROP FUNCTION command deletes the function altogether from the database. Any privilege assigned on the function remains the same when the CREATE OR REPLACE FUNCTION command is used; however, in the case of the DROP FUNCTION command, all the privileges are revoked.

The main restrictions on stored FUNCTIONS that are CALLED from SQL statements are:

•  These functions can use only POSITIONAL METHODS for passing the parameters.
•  These functions can use only SQL specific datatypes. PL/SL datatypes, such as RECORD or TYPE, cannot be used.
•  User should have EXECUTE privilege on the function to use it within SQL statements
•  These functions cannot have the OUT and IN OUT parameter MODES

The main restrictions on stored functions that are called from SQL statements are:

Stored FUNCTIONS can USE VALID SQL datatypes, such as NUMBER, VARCHAR2, and DATE. However, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.

Stored functions can use valid SQL datatypes, such as NUMBER, VARCHAR2, and DATE. However, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.

YES, FUNCTIONS can be STORED as DATABASE OBJECTS in the database.

Yes, functions can be stored as database objects in the database.

Yes, USER defined functions can be CALLED in SQL STATEMENTS as shown in the following code:

Yes, user defined functions can be called in SQL statements as shown in the following code:

No, a function cannot be DEFINED without a RETURN statement because it has to return a value BACK to the calling environment. In CASE a RETURN statement is not defined, runtime error is RAISED.

No, a function cannot be defined without a RETURN statement because it has to return a value back to the calling environment. In case a RETURN statement is not defined, runtime error is raised.

The three different modes, IN, OUT, and IN OUT, can be USED in the function SPECIFICATION. HOWEVER, it is recommended to use only the IN MODE. This is because functions are supposed to accept values from the CALLING environment and return a single value.

The three different modes, IN, OUT, and IN OUT, can be used in the function specification. However, it is recommended to use only the IN mode. This is because functions are supposed to accept values from the calling environment and return a single value.

YES, it is POSSIBLE to define RETURN statement in the exception SECTION. This RETURN statement would be EXECUTED if an exception is raised and control is passed to the exception section of the code.

Yes, it is possible to define RETURN statement in the exception section. This RETURN statement would be executed if an exception is raised and control is passed to the exception section of the code.

Yes, there can be MULTIPLE RETURN statements within a FUNCTION THOUGH only one is executed. After the value is retuned, the CONTROL passes back to the calling environment and function PROCESSING is stopped.

Yes, there can be multiple RETURN statements within a function though only one is executed. After the value is retuned, the control passes back to the calling environment and function processing is stopped.

The BASIC SPECIFICATION for a FUNCTION is GIVEN as FOLLOWS:

The basic specification for a function is given as follows:

If an exception is unhandled within a procedure, the control is PASSED BACK to the calling environment. Any DML STATEMENTS, which were EXECUTED within the procedure, are ROLLED back.

If an exception is unhandled within a procedure, the control is passed back to the calling environment. Any DML statements, which were executed within the procedure, are rolled back.

REMOTE TABLES can be referenced within PROCEDURES through SQL STATEMENTS. A remote table can be queried through a database link in the procedure. The following CODE inserts a record in the STUDENT_MARKS table, which is in another schema, using the CONNECT_STUDENT database link:

Remote tables can be referenced within procedures through SQL statements. A remote table can be queried through a database link in the procedure. The following code inserts a record in the STUDENT_MARKS table, which is in another schema, using the CONNECT_STUDENT database link:

The SCOPE of any procedure that is defined within the PL/SQL BLOCK will be limited as it can be CALLED only within the PL/SQL block and not by any other procedure or calling ENVIRONMENT.

The scope of any procedure that is defined within the PL/SQL block will be limited as it can be called only within the PL/SQL block and not by any other procedure or calling environment.

YES, any procedure can be DECLARED and defined WITHIN a PL/SQL BLOCK.

Yes, any procedure can be declared and defined within a PL/SQL block.

There are following three different METHODS to pass values to a procedure:

• Positional —Refers to a method in which actual parameters of a procedure are PASSED in the same order as the FORMAL parameters.
• NAMED—Refers to a method in which actual parameters of a procedure are passed in any arbitrary order but are associated with their corresponding formal parameters.
• Combination—Refers to a method in which some parameters are passed as positional and some are passed as named parameters.

There are following three different methods to pass values to a procedure:

No, DEFAULT VALUES cannot be ASSIGNED to IN OUT PARAMETERS; only IN parameters can be assigned default values.

No, default values cannot be assigned to IN OUT parameters; only IN parameters can be assigned default values.

The IN OUT parameter mode can be used to initialize a parameter in a procedure, as any VALUE can be INITIALLY assigned to this parameter when the procedure is called from the CALLING ENVIRONMENT. The value of this parameter can be changed during the EXECUTION of the procedure and the result can be returned back to the calling environment from the procedure.

The IN OUT parameter mode can be used to initialize a parameter in a procedure, as any value can be initially assigned to this parameter when the procedure is called from the calling environment. The value of this parameter can be changed during the execution of the procedure and the result can be returned back to the calling environment from the procedure.

The OUT parameters can be viewed in the SQL PLUS ENVIRONMENT by DECLARING variables for the same, as shown in the following code:

On the SQL PLUS prompt, VARIABLE is created and the procedure is executed, as shown in the following code:

The OUT parameters can be viewed in the SQL PLUS environment by declaring variables for the same, as shown in the following code:

On the SQL PLUS prompt, variable is created and the procedure is executed, as shown in the following code:

There are three parameter modes for a procedure, given as follows:

•  IN —Refers to a constant value, which is assigned to this parameter from the calling environment. The IN parameter value cannot be modified within a procedure. It is a default MODE.
•  OUT— Refers to a return value, which is PASSED from the procedure back to the calling environment.
•  IN OUT —Refers to a value, which is assigned from the calling environment to this parameter and a same or DIFFERENT value is passed back from the procedure to the calling environment USING this parameter.

There are three parameter modes for a procedure, given as follows:

EXPLICIT datatypes, %TYPE and %ROWTYPE, without size SPECIFICATION can be USED for parameters in a PROCEDURE.

Explicit datatypes, %TYPE and %ROWTYPE, without size specification can be used for parameters in a procedure.

FORMAL parameters are variables declared as the parameter list in the subprogram SPECIFICATION.
Actual parameters are the values that are passed to the subprogram from the CALLING environment. The actual parameter values are evaluated and resultant values are ASSIGNED to the formal parameters when the program is executed. Both formal and actual parameters should be of compatible datatypes. Following code SHOWS the formal parameters of the function:

In the preceding function, p_student_id and p_percentage are the formal parameters.
In the following function code, v_student_id and v_percentage are the actual parameters:
proc_disp_percentage (v_student_id, v_percentage);

Formal parameters are variables declared as the parameter list in the subprogram specification.
Actual parameters are the values that are passed to the subprogram from the calling environment. The actual parameter values are evaluated and resultant values are assigned to the formal parameters when the program is executed. Both formal and actual parameters should be of compatible datatypes. Following code shows the formal parameters of the function:

In the preceding function, p_student_id and p_percentage are the formal parameters.
In the following function code, v_student_id and v_percentage are the actual parameters:
proc_disp_percentage (v_student_id, v_percentage);

No, the SIZE of the PARAMETER passed to the procedure cannot be restricted in the procedure DECLARATION. The datatype for the parameter has to be SPECIFIED only.

No, the size of the parameter passed to the procedure cannot be restricted in the procedure declaration. The datatype for the parameter has to be specified only.

The REPLACE option SPECIFIES that if the PROCEDURE with the same name exists in the DATABASE, it will be DROPPED and RECREATED in the database as a new version.

The REPLACE option specifies that if the procedure with the same name exists in the database, it will be dropped and recreated in the database as a new version.

A procedure is a named PL/SQL block (subprogram) that can have more than one parameter as an input from the calling ENVIRONMENT and it may return none, one, or more than one VALUE.
YES, it can be compiled and STORED in the database as a schema object and used for execution at any point of time.

A procedure is a named PL/SQL block (subprogram) that can have more than one parameter as an input from the calling environment and it may return none, one, or more than one value.
Yes, it can be compiled and stored in the database as a schema object and used for execution at any point of time.

The BLOCK STRUCTURE of a SUBPROGRAM is GIVEN as FOLLOWS:

The block structure of a subprogram is given as follows:

A subprogram has TWO parts: subprogram specification and subprogram BODY.

1. The subprogram specification is the header section, which contains the details such as program name, subprogram type, parameter list, and return clause (for functions).
2. The subprogram body is a PL/SQL block, which has the DECLARATION, executable, and exception SECTIONS.

An anonymous block has no specification or header section. It only consists of a PL/SQL block in the body that contains the declaration, executable, and exception sections.

A subprogram has two parts: subprogram specification and subprogram body.

An anonymous block has no specification or header section. It only consists of a PL/SQL block in the body that contains the declaration, executable, and exception sections.

A PROCEDURE is executed as a PL/SQL statement. It can accept more than one PARAMETER as an input from the CALLING environment and may return NONE, one, or more than one VALUE.

A function is invoked as a part of expression. It can have more than one parameter as an input from the calling environment and it should return a single value to the calling environment using the RETURN statement.

A procedure is executed as a PL/SQL statement. It can accept more than one parameter as an input from the calling environment and may return none, one, or more than one value.

A function is invoked as a part of expression. It can have more than one parameter as an input from the calling environment and it should return a single value to the calling environment using the RETURN statement.

The TWO different types of subprograms in PL/SQL are procedures and FUNCTIONS. Both can be called from an EXTERNAL environment or application. In ADDITION, they can accept parameters.

The two different types of subprograms in PL/SQL are procedures and functions. Both can be called from an external environment or application. In addition, they can accept parameters.

A named Procedural Language/Structured Query Language (PL/SQL) block is CALLED a subprogram. It has three sections: declarative, executable, and exception handling (optional). The main advantage of a subprogram is that it can be compiled and stored in a database. In addition, it has the FOLLOWING advantages:

• Modularization —Refers to a property, which allows you to break the large programs into smaller modules and make the program easy to WRITE and DEBUG.
• Easy maintenance —Refers to a property, which allows you to make the modifications in a code because the code is modular and written in one place.
• Reusability—Refers to a property, which allows you to reuse the same subprogram within any application. The code of the subprogram need not be re-written each time.
• Data integrity and security —Refers to a property, which prevents UNAUTHORIZED users from accessing the subprogram without the proper rights.
• Better performance —Helps in avoiding repeated parsing on subsequent calls to the same subprogram as the code is compiled, parsed, and available in the shared Structured Query Language (SQL) area. This reduces the number of calls to the database; and therefore, increases the network performance.
• Code clarity —Allows you to give proper names to the PL/SQL blocks. As a result, the code becomes simpler to understand and gets better clarity.

A named Procedural Language/Structured Query Language (PL/SQL) block is called a subprogram. It has three sections: declarative, executable, and exception handling (optional). The main advantage of a subprogram is that it can be compiled and stored in a database. In addition, it has the following advantages:

The ACTION_TYPE COLUMN of the USER_TRIGGERS data DICTIONARY view SHOWS that the trigger is a PL/SQL trigger.

The ACTION_TYPE column of the USER_TRIGGERS data dictionary view shows that the trigger is a PL/SQL trigger.

The ALTER STATEMENT is GIVEN as FOLLOWS:

The ALTER statement is given as follows:

Triggers use CONDITIONAL predicates, such as INSERTING, UPDATING, and DELETING, to determine which particular EVENT will cause the trigger to fire. All the THREE predicates have Boolean values and are useful in triggers, such as AFTER INSERT or UPDATE.

Triggers use conditional predicates, such as INSERTING, UPDATING, and DELETING, to determine which particular event will cause the trigger to fire. All the three predicates have Boolean values and are useful in triggers, such as AFTER INSERT or UPDATE.

If an INSERT STATEMENT is used on the same table to which the TRIGGER is ASSOCIATED, then this will GIVE RISE to a mutating table, as it is not possible to change the same table that is already in the process of being changed.

If an INSERT statement is used on the same table to which the trigger is associated, then this will give rise to a mutating table, as it is not possible to change the same table that is already in the process of being changed.