Explore topic-wise InterviewSolutions in .

The SQLCODE FUNCTION returns the Oracle error number for internal EXCEPTIONS. This error number can be PASSED to the SQLERRM function to get the message associated with the error number. The SQLCODE function returns the value +1; whereas, the SQLERRM function returns the User-Defined Exception message for the user-defined exceptions.

The SQLCODE function returns the Oracle error number for internal exceptions. This error number can be passed to the SQLERRM function to get the message associated with the error number. The SQLCODE function returns the value +1; whereas, the SQLERRM function returns the User-Defined Exception message for the user-defined exceptions.

The OTHERS exception HANDLER ENSURES that no exception goes UNHANDLED and the program TERMINATES successfully.

The OTHERS exception handler ensures that no exception goes unhandled and the program terminates successfully.

Pre-defined Oracle ERRORS are as FOLLOWS:

• NO_DATA_FOUND —Refers to an ERROR when a SELECT statement does not return any result set.
• DUP_VAL_ON_INDEX —Refers to an error when a program TRIES to store duplicate values in an indexed COLUMN.
• TOO_MANY_ROWS —Refers to an error when a SELECT INTO statement returns more than one row.
• VALUE_ERROR —Refers to an error when an arithmetic, conversion, truncation, or size-constraints error occurs.
• ROWTYPE_MISMATCH —Refers to an error when the actual and formal parameters do not have the same datatypes

Pre-defined Oracle errors are as follows:

No, it is not POSSIBLE to have more than one OTHERS clause in the EXCEPTION SECTION.

No, it is not possible to have more than one OTHERS clause in the exception section.

No, the PL/SQL BLOCK cannot HANDLE more than ONE exception. It can process only one exception handler before terminating.

No, the PL/SQL block cannot handle more than one exception. It can process only one exception handler before terminating.

The exception section handles only those ERRORS that are specified; however, other errors that are not specified cannot be HANDLED. To HANDLE these errors explicitly, the OTHERS exception HANDLER is used that is always DEFINED as the last exception handler.

The exception section handles only those errors that are specified; however, other errors that are not specified cannot be handled. To handle these errors explicitly, the OTHERS exception handler is used that is always defined as the last exception handler.

The structure of an EXCEPTION section WITHIN the PL/SQL code is GIVEN as follows:

The structure of an exception section within the PL/SQL code is given as follows:

An exception is RAISED within the PROGRAM and if there is no CORRESPONDING exception handler, the PL/SQL block terminates unsuccessfully and the exception is propagated to the calling ENVIRONMENT.

An exception is raised within the program and if there is no corresponding exception handler, the PL/SQL block terminates unsuccessfully and the exception is propagated to the calling environment.

When a runtime ERROR occurs, an exception is raised. The exception is handled when this error is successfully handled by the PL/SQL code and is not allowed to propagate to the outward environment. COMPILATION error cannot be handled WITHIN the program; only runtime errors can be handled within the PL/SQL code.
An exception can be raised:

•  By a runtime Oracle error
•  By the RAISE STATEMENT
•  By calling the RAISE_APPLICATION_ERROR procedure

After an exception is raised, the control is passed to the exception section, where the code is written about how to handle the error that has occurred. The control cannot pass back to the EXECUTABLE section of the same block after the exception is handled.

When a runtime error occurs, an exception is raised. The exception is handled when this error is successfully handled by the PL/SQL code and is not allowed to propagate to the outward environment. Compilation error cannot be handled within the program; only runtime errors can be handled within the PL/SQL code.
An exception can be raised:

After an exception is raised, the control is passed to the exception section, where the code is written about how to handle the error that has occurred. The control cannot pass back to the executable section of the same block after the exception is handled.

An exception is an IDENTIFIER, which defines an error CONDITION within the Procedural Language/Structured Query Language (PL/SQL) code. When an error OCCURS, it is trapped and the program CONTROL unconditionally BRANCHES to the exception section of the PL/SQL block.

An exception is an identifier, which defines an error condition within the Procedural Language/Structured Query Language (PL/SQL) code. When an error occurs, it is trapped and the program control unconditionally branches to the exception section of the PL/SQL block.

The STATEMENT is GIVEN as FOLLOWS:

The statement is given as follows:

An exception is CALLED a PUBLIC exception if it is declared in a package specification as a separate CONSTRUCT, as SHOWN in the FOLLOWING code:

An exception is called a public exception if it is declared in a package specification as a separate construct, as shown in the following code:

PACKAGE SPECIFICATION, which declares CONSTRUCTS, such as variables, constants, user-defined exceptions, and cursors, is enough to CREATE a complete package. Package body is not required if there are no procedures and FUNCTIONS to be defined in the package.

Package specification, which declares constructs, such as variables, constants, user-defined exceptions, and cursors, is enough to create a complete package. Package body is not required if there are no procedures and functions to be defined in the package.

Two SEPARATE ENTRIES are created for PACKAGE specification and package body in the USER_OBJECTS DATA dictionary.

Two separate entries are created for package specification and package body in the USER_OBJECTS data dictionary.

Variables declared in a PACKAGE BODY are private variables. Therefore, these cannot be REFERENCED in other PACKAGES.

Variables declared in a package body are private variables. Therefore, these cannot be referenced in other packages.

The CONSTANT DECLARED in a PACKAGE specification can be referenced in the same package where it is declared, stored PROCEDURE, stored function, or an ANONYMOUS b : c.

The constant declared in a package specification can be referenced in the same package where it is declared, stored procedure, stored function, or an anonymous b : c.

Global constructs once DECLARED in package are available throughout the USER session. Until the user session is terminated, the global construct retains its value across the PL/SQL blocks. This is applicable for all the packaged global constructs, such as constants, exceptions, and cursors. For example, a GLOBALLY declared cursor may be opened in one PL/SQL block, fetched in another block, and CLOSED in some other block.

Global constructs once declared in package are available throughout the user session. Until the user session is terminated, the global construct retains its value across the PL/SQL blocks. This is applicable for all the packaged global constructs, such as constants, exceptions, and cursors. For example, a globally declared cursor may be opened in one PL/SQL block, fetched in another block, and closed in some other block.

Yes, if the database objects associated with the PACKAGE are modified, the package specification and the package BODY become invalid; and therefore, the package NEEDS recompilation USING the following syntax:

Yes, if the database objects associated with the package are modified, the package specification and the package body become invalid; and therefore, the package needs recompilation using the following syntax:

A package can be STORED on the server side within a schema in the database. It can also be stored on the client side in the form of Oracle REPORTS, Oracle FORMS, or as PART of PL/SQL LIBRARY.

A package can be stored on the server side within a schema in the database. It can also be stored on the client side in the form of Oracle Reports, Oracle Forms, or as part of PL/SQL library.

A package consists of two PARTS: package specification and package body. The package specification contains the names of procedures and functions, their parameters, and return types; whereas, the subprogram code is part of the package body. Developers who require calling the procedures or functions just need to have ACCESS on the package specification. THEREFORE, the package owner can grant privilege on the package specification while HIDING the package body. In this way, the developers can use the package WITHOUT seeing the actual code. In addition, package owners can create procedures and functions that are private for the package and need not be part of the package specification. In this way, information hiding is achieved within a package.

A package consists of two parts: package specification and package body. The package specification contains the names of procedures and functions, their parameters, and return types; whereas, the subprogram code is part of the package body. Developers who require calling the procedures or functions just need to have access on the package specification. Therefore, the package owner can grant privilege on the package specification while hiding the package body. In this way, the developers can use the package without seeing the actual code. In addition, package owners can create procedures and functions that are private for the package and need not be part of the package specification. In this way, information hiding is achieved within a package.

It is not possible to call a function stored in ONE Oracle Form from ANOTHER Oracle Form unless it is DEFINED and DECLARED in a PL/SQL library, which is SHARED between the two Oracle Forms.

It is not possible to call a function stored in one Oracle Form from another Oracle Form unless it is defined and declared in a PL/SQL library, which is shared between the two Oracle Forms.

It is ADVISABLE to KEEP the RETURN STATEMENT at the end of the function code. This is because the function processes the STATEMENTS until the RETURN statement is encountered and then the CONTROL is passed back to the calling environment.

It is advisable to keep the RETURN statement at the end of the function code. This is because the function processes the statements until the RETURN statement is encountered and then the control is passed back to the calling environment.

YES, a function can be compiled WITHOUT a RETURN STATEMENT, but it cannot be executed.

Yes, a function can be compiled without a RETURN statement, but it cannot be executed.

No, functions STORED on the client side in ORACLE Forms or Reports cannot be INVOKED from SQL STATEMENTS. They can only be used as PL/SQL expressions in the PROGRAM units of the same client program.

No, functions stored on the client side in Oracle Forms or Reports cannot be invoked from SQL statements. They can only be used as PL/SQL expressions in the program units of the same client program.

No, FUNCTIONS cannot be CALLED as stand-alone programs LIKE procedures. They have to be a part of an expression or they should be invoked as part of SQL statements.

No, functions cannot be called as stand-alone programs like procedures. They have to be a part of an expression or they should be invoked as part of SQL statements.

The CALLING FUNCTION func__test_a becomes invalid. It has to be edited and COMPILED again to become VALID.

The calling function func__test_a becomes invalid. It has to be edited and compiled again to become valid.

This ERROR is displayed if the function is EXECUTED when it is in the invalid state. The ORA-06575 error indicates that for some reason the function is not valid and NEEDS to be checked and compiled again.

This error is displayed if the function is executed when it is in the invalid state. The ORA-06575 error indicates that for some reason the function is not valid and needs to be checked and compiled again.

Yes, a stored procedure or FUNCTION can be INVOKED from the Oracle Forms and the result can be used for further PROGRAMMING.

Yes, a stored procedure or function can be invoked from the Oracle Forms and the result can be used for further programming.

Procedure cannot be CALLED from within the IF THEN ELSE block; however, functions can become a PART of the IF THEN ELSE block because functions RETURN the value in such a manner that the FUNCTION call in the block acts LIKE a variable.

Procedure cannot be called from within the IF THEN ELSE block; however, functions can become a part of the IF THEN ELSE block because functions return the value in such a manner that the function call in the block acts like a variable.

No, BOOLEAN DATATYPE cannot be used in functions that are called from SQL STATEMENTS.

No, BOOLEAN datatype cannot be used in functions that are called from SQL statements.

The proc_get_highest_rank PROCEDURE can be accessed within the user_a schema from another stored procedure. It can ALSO be accessed either from an anonymous PL/SQL block EXECUTED on the SQL prompt LOGGED in as user_a or directly at the SQL prompt using the EXECUTE command. In addition, it can be accessed by other users if they have the EXECUTE PRIVILEGE.

The proc_get_highest_rank procedure can be accessed within the user_a schema from another stored procedure. It can also be accessed either from an anonymous PL/SQL block executed on the SQL prompt logged in as user_a or directly at the SQL prompt using the EXECUTE command. In addition, it can be accessed by other users if they have the EXECUTE privilege.

No, an IN PARAMETER cannot be ASSIGNED VALUES WITHIN a PROCEDURE.

No, an IN parameter cannot be assigned values within a procedure.

Any OUT parameter cannot be referenced WITHOUT being ASSIGNED a value, as the OUT parameter cannot be assigned a default value. Therefore, the v_total_marks parameter cannot be referred within the code until the EXECUTABLE statements of the procedure have BEGUN; the OUT parameter is assigned a value using the PL/SQL assignment statements.

Any OUT parameter cannot be referenced without being assigned a value, as the OUT parameter cannot be assigned a default value. Therefore, the v_total_marks parameter cannot be referred within the code until the executable statements of the procedure have begun; the OUT parameter is assigned a value using the PL/SQL assignment statements.

No, a SEQUENCE generator cannot be USED to assign a default value to a PARAMETER.

No, a sequence generator cannot be used to assign a default value to a parameter.

The USER_SOURCE, ALL_SOURCE, and DBA_SOURCE data dictionary views will have the source code, as a procedure is STORED in the DATABASE EVEN if it has a syntax error.

The USER_SOURCE, ALL_SOURCE, and DBA_SOURCE data dictionary views will have the source code, as a procedure is stored in the database even if it has a syntax error.

Oracle recompiles the procedure AUTOMATICALLY when the procedure is INVOKED. Therefore, no ACTION has to be taken if the INSERT statement is using column NAMES. However, if the INSERT statement is just adding values without using the column names, then the procedure has to be modified, as another column has been added.

Oracle recompiles the procedure automatically when the procedure is invoked. Therefore, no action has to be taken if the INSERT statement is using column names. However, if the INSERT statement is just adding values without using the column names, then the procedure has to be modified, as another column has been added.

The functions that are called within SQL STATEMENTS have the following restrictions:

• They must be stored in a database.
• They cannot modify any database table.
• They can only take the IN parameter modes. The OUT and IN OUT parameter modes are not allowed in a function.
• They can only use valid SQL datatypes, such as NUMBER, VARCHAR2, and DATE. HOWEVER, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.
• The return type of a function should be of SQL DATATYPE.
• They cannot have the COMMIT and ROLLBACK statements.
• They cannot have the ALTER SESSION and ALTER SYSTEM commands.

The functions that are called within SQL statements have the following restrictions:

Function purity levels DEFINE what type of DATA structures can be read or modified by a function. There are four function purity levels, which are given as follows:

• WNDS —Writes no database state. The function does not MODIFY any database table using any DML statement.
• RNDS—Reads no database state. The function does not read any database TABLES using the SELECT statement.
• WNPS —Writes no package state. The function does not modify any PACKAGED variables.
• RNPS— Reads no package state. The function does not read any packaged variables.

Function purity levels define what type of data structures can be read or modified by a function. There are four function purity levels, which are given as follows:

No, stand-alone programs cannot be overloaded; HOWEVER, PACKAGED sub-programs can be overloaded WITHIN the same PACKAGE.

No, stand-alone programs cannot be overloaded; however, packaged sub-programs can be overloaded within the same package.

PROCEDURE is used when there are more than one return values; HOWEVER, if there is only one return VALUE, then a FUNCTION should be used. Although functions may have more than one OUT PARAMETERS, it is considered as a poor programming style. In addition, when a subprogram has to be invoked within an SQL statement, function should be used.

Procedure is used when there are more than one return values; however, if there is only one return value, then a function should be used. Although functions may have more than one OUT parameters, it is considered as a poor programming style. In addition, when a subprogram has to be invoked within an SQL statement, function should be used.

When any database object associated with the subprogram changes, the program NEEDS to be recompiled. When the program is executed the next time, after the object is CHANGED, the SYSTEM will automatically perform runtime recompilation. To avoid this performance overhead, it is BETTER to explicitly recompile the subprogram USING the ALTER [PROCEDURE | FUCNTION] command, as shown in the following statement:

When any database object associated with the subprogram changes, the program needs to be recompiled. When the program is executed the next time, after the object is changed, the system will automatically perform runtime recompilation. To avoid this performance overhead, it is better to explicitly recompile the subprogram using the ALTER [PROCEDURE | FUCNTION] command, as shown in the following statement:

The procedures or functions can be DEBUGGED using the SHOW_ERROR COMMAND or the DBMS_OUTPUT command WITHIN the SUBPROGRAM code.

The procedures or functions can be debugged using the SHOW_ERROR command or the DBMS_OUTPUT command within the subprogram code.

The AUTHID property affects the NAME resolution and privilege checking of SQL statements at runtime; however, it does not affect the compilation, and has no meaning for blocks that have no code, such as COLLECTION types.

Any PROCEDURE may be created with its AUTHID property set to either the DEFINER or the INVOKER rights. If a procedure is created with the DEFINER rights, then the USER executing the procedure need not have an access to database objects, which the procedure is accessing. However, if a procedure is created with the INVOKER rights, then the user MUST have access rights to all the objects that the procedure is accessing.

The AUTHID property affects the name resolution and privilege checking of SQL statements at runtime; however, it does not affect the compilation, and has no meaning for blocks that have no code, such as collection types.

Any procedure may be created with its AUTHID property set to either the DEFINER or the INVOKER rights. If a procedure is created with the DEFINER rights, then the user executing the procedure need not have an access to database objects, which the procedure is accessing. However, if a procedure is created with the INVOKER rights, then the user must have access rights to all the objects that the procedure is accessing.

A procedure or function, which is owned by another user, may be executed by EITHER creating a synonym for the SUBPROGRAM or prefixing it with the user name, as shown in the following CODE SNIPPET:

A procedure or function, which is owned by another user, may be executed by either creating a synonym for the subprogram or prefixing it with the user name, as shown in the following code snippet:

To execute a SUBPROGRAM owned by another user, the user must be GRANTED EITHER the EXECUTE privilege on a PROCEDURE or function or the EXECUTE ANY PROCEDURE system privilege.

To execute a subprogram owned by another user, the user must be granted either the EXECUTE privilege on a procedure or function or the EXECUTE ANY PROCEDURE system privilege.

A One-TIME-Only procedure is executed only once, when the PACKAGE is called for the first time. While declaring a One-Time-Only procedure the keyword END is not used at the end of the one-time-only procedure; however, the procedure uses the package body's END clause, as the procedure's CODE is PLACED at the bottom of the package body.

A One-Time-Only procedure is executed only once, when the package is called for the first time. While declaring a One-Time-Only procedure the keyword END is not used at the end of the one-time-only procedure; however, the procedure uses the package body's END clause, as the procedure's code is placed at the bottom of the package body.

PL/SQL does not allow the use of any subprogram before it is DECLARED and defined. However, in a package, forward declaration is allowed in case the subprogram specification is terminated by a semi-colon. This enables the subprogram to be defined in a logical manner and groups the subprograms in a package. It also helps in defining MUTUALLY recursive PROGRAMS that call each other directly or indirectly.

PL/SQL does not allow the use of any subprogram before it is declared and defined. However, in a package, forward declaration is allowed in case the subprogram specification is terminated by a semi-colon. This enables the subprogram to be defined in a logical manner and groups the subprograms in a package. It also helps in defining mutually recursive programs that call each other directly or indirectly.

PL/SQL ALLOWS you to OVERLOAD the subprogram names and type methods. The package can include subprograms that have the same NAME as long as they have different FORMAL parameters. PL/SQL determines which subprogram to be called depending on the formal parameters. In PL/SQL, only packaged subprograms can be overloaded.

PL/SQL allows you to overload the subprogram names and type methods. The package can include subprograms that have the same name as long as they have different formal parameters. PL/SQL determines which subprogram to be called depending on the formal parameters. In PL/SQL, only packaged subprograms can be overloaded.

YES, it is POSSIBLE to drop the package BODY without dropping the package specification using the DROP PACKAGE BODY COMMAND.

Yes, it is possible to drop the package body without dropping the package specification using the DROP PACKAGE BODY command.

The DROP PACKAGE COMMAND is USED to drop the ENTIRE package INCLUDING the specification and the body.

The DROP PACKAGE command is used to drop the entire package including the specification and the body.