Explore topic-wise InterviewSolutions in .

The UTL_HTTP PACKAGE enables PL/SQL and SQL to access the INTERNET data using the REQUEST and REQUEST_PIECES functions. These functions take the UNIFORM Resource Locator (URL) of the WEBSITE as input and return the HyperText MARKUP Language (HTML) data from the website.

The UTL_HTTP package enables PL/SQL and SQL to access the Internet data using the REQUEST and REQUEST_PIECES functions. These functions take the Uniform Resource Locator (URL) of the website as input and return the HyperText Markup Language (HTML) data from the website.

The difference between FOPEN and IS_OPEN functions is GIVEN as follows:

FOPEN —Opens a file for input/output and returns a file HANDLE used in I/O OPERATIONS. Following is the SYNTAX of the FOPEN function:

IS_OPEN — Returns a Boolean value when the file handle is PASSED as a parameter to the function to an open file. Following is the syntax of the IS_OPEN function:

The difference between FOPEN and IS_OPEN functions is given as follows:

FOPEN —Opens a file for input/output and returns a file handle used in I/O operations. Following is the syntax of the FOPEN function:

IS_OPEN — Returns a Boolean value when the file handle is passed as a parameter to the function to an open file. Following is the syntax of the IS_OPEN function:

YES, the UTL_FILE PACKAGE can RAISE PREDEFINED EXCEPTIONS, such as NO_DATA_FOUND or VALUE_ERROR.

Yes, the UTL_FILE package can raise predefined exceptions, such as NO_DATA_FOUND or VALUE_ERROR.

A few EXCEPTIONS of the UTL_FILE package are as follows:

• INVALID_PATH — Specifies that a file location or filename is INVALID
• INVALID_MODE — Specifies that the OPEN_MODE parameter in the FOPEN procedure is incorrect
• READ__ERROR  — Specifies that an OPERATING system ERROR has occurred during the read operation
• WRITE_ERROR — Specifies that an operating system error has occurred during the write operation
• INVALID__OPERATION — Specifies that a file cannot be OPENED or used

A few exceptions of the UTL_FILE package are as follows:

The FFLUSH PROCEDURE is USED to write all DATA buffered in MEMORY to a file.

The FFLUSH procedure is used to write all data buffered in memory to a file.

The difference between the PUT_LINE and PUTF functions of the UTL_FILE package is as follows:

• PUT_LINE —Refers to a function used to write text string, which is stored in the buffer, to the OPENED file with a line TERMINATOR.
• PUTF— Refers to a function used to write text string, which is stored in the buffer, to the opened file but in a formatted FORM with format specifiers - %s and n. The %s format specifier is used to SUBSTITUTE a VALUE in the output string and n is a new line character.

The difference between the PUT_LINE and PUTF functions of the UTL_FILE package is as follows:

The following SETTINGS will turn off all the database permissions on the DIRECTORIES that are accessible to the database PROCESSES and make all of them AVAILABLE to the UTL_FILE PACKAGE:

The following settings will turn off all the database permissions on the directories that are accessible to the database processes and make all of them available to the UTL_FILE package:

It is essential to initialize the UTL_FILE_DIR PARAMETER in the init.ora file before USING the UTL_FILE PACKAGE. This parameter is initialized to the directory that is to be ACCESSED as follows:

It is essential to initialize the UTL_FILE_DIR parameter in the init.ora file before using the UTL_FILE package. This parameter is initialized to the directory that is to be accessed as follows:

The UTL_FILE PACKAGE PROVIDES TEXT files input/output functionality within PL/SQL. The security on client-side is handled USING normal operating system file permissions; whereas, server-side security is handled though restrictions on the directories that are ACCESSED.

The UTL_FILE package provides text files input/output functionality within PL/SQL. The security on client-side is handled using normal operating system file permissions; whereas, server-side security is handled though restrictions on the directories that are accessed.

The DBMS_OUTPUT package uses the GET_LINES PROCEDURE to GET an array of LINES from OUTPUT buffer into the procedure.

The DBMS_OUTPUT package uses the GET_LINES procedure to get an array of lines from output buffer into the procedure.

The DBMS_OUTPUT PACKAGE is used to DISPLAY OUTPUT messages from the PL/SQL block.

The DBMS_OUTPUT package is used to display output messages from the PL/SQL block.

The DBA_JOBS_RUNNING data DICTIONARY view is used to display the status of the jobs CURRENTLY running.

The DBA_JOBS_RUNNING data dictionary view is used to display the status of the jobs currently running.

The DBA_JOBS data DICTIONARY VIEW is used to DISPLAY the status of the SUBMITTED jobs.

The DBA_JOBS data dictionary view is used to display the status of the submitted jobs.

YES, It is possible to run a job SCHEDULED for a particular TIME IMMEDIATELY using the DBMS_JOB.RUN PROCEDURE.

Yes, It is possible to run a job scheduled for a particular time immediately using the DBMS_JOB.RUN procedure.

The JOB can be RESCHEDULED as FOLLOWS:

The job can be rescheduled as follows:

The DBMS_JOB.NEXT_DATE parameter is used to RESCHEDULE a PARTICULAR JOB. This parameter is used when the next execution DATE for a particular job needs to be changed.

The DBMS_JOB.NEXT_DATE parameter is used to reschedule a particular job. This parameter is used when the next execution date for a particular job needs to be changed.

An exception is RAISED if the time specified for the JOB does not EVALUATE to a job in future.

An exception is raised if the time specified for the job does not evaluate to a job in future.

The following program code is used to schedule a database backup JOB USING the procedure pk_db_activities.proc_db_backup in the queue for MIDNIGHT:

The following program code is used to schedule a database backup job using the procedure pk_db_activities.proc_db_backup in the queue for midnight:

The DBMS_JOB.SUBMIT procedure adds a new job to the job queue. It TAKES four PARAMETERS as input and returns the job number as the output though the JOB parameter. The parameters of the DBMSJOB.SUBMIT procedure are as follows:

• JOB— Refers to the OUT parameter that gives the output of the job number, which is the unique identifier for the job
• WHAT— Refers to the IN parameter, which provides the PL/SQL code to be executed
• NEXT_DA TE —Refers to the IN parameter, which provides the next execution date of the job
• INTERVAL —Refers to the IN parameter for the interval USED to calculate the next execution date
• NO_PARSE —Refers to the IN parameter, which is a Boolean FLAG INDICATING whether to parse the job at the time of job submission

The DBMS_JOB.SUBMIT procedure adds a new job to the job queue. It takes four parameters as input and returns the job number as the output though the JOB parameter. The parameters of the DBMSJOB.SUBMIT procedure are as follows:

The DBMS_JOB.BROKEN procedure DISABLES the EXECUTION of the specified JOB. This job will not be executed by the Oracle server.

The DBMS_JOB.BROKEN procedure disables the execution of the specified job. This job will not be executed by the Oracle server.

The subprograms of the DBMS_JOB package are as FOLLOWS:

•  SUBMIT—Submits a job to the job queue
•  REMOVE—Removes the specified job from the job queue
•  CHANGE —Alters the specified job that has ALREADY been submitted to the job queue
•  WHAT—Alters the specified job
•  NEXT__DATE—Alters the execution TIME
•  INTERVAL —Alters the interval between execution of specified job
•  BROKEN—Disables the job execution
•  RUN—Forces a specified job to be executed

The subprograms of the DBMS_JOB package are as follows:

The DBMS_JOB PACKAGE is USED for SCHEDULING and EXECUTING PL/SQL PROGRAMS.

The DBMS_JOB package is used for scheduling and executing PL/SQL programs.

The DBMSJDDL package provides access to CERTAIN DDL STATEMENTS within PL/SQL blocks. This package runs with the privileges of the CALLING user and not the OWNER of the SYS package. Some of the procedures included in this package are as follows:

•  DBMS_DDL.ALTER_COMPILE object_type, owner, object__name)
•  DBMS_DDL.ANALYZE_OBJECT(object_type, owner, object_name, method)

The DBMSJDDL package provides access to certain DDL statements within PL/SQL blocks. This package runs with the privileges of the calling user and not the owner of the SYS package. Some of the procedures included in this package are as follows:

The native dynamic SQL can be executed within a PL/SQL block USING the EXECUTE IMMEDIATE command, as shown in the following EXAMPLE:

The native dynamic SQL can be executed within a PL/SQL block using the EXECUTE IMMEDIATE command, as shown in the following example:

No, PL/SQL datatypes are not supported by DYNAMIC SQL; only PL/SQL RECORD DATATYPE can be used in the INTO clause. No other PL/SQL datatype, such as BOOLEAN, can be used WITHIN dynamic SQL.

No, PL/SQL datatypes are not supported by dynamic SQL; only PL/SQL RECORD datatype can be used in the INTO clause. No other PL/SQL datatype, such as BOOLEAN, can be used within dynamic SQL.

Yes, dynamic SQL SUPPORTS all SQL data types. It is possible to define variables and bind arguments of COLLECTION type, LOBS, and REFs.

Yes, dynamic SQL supports all SQL data types. It is possible to define variables and bind arguments of collection type, LOBs, and REFs.

The DBMS_SQL package is used to ACCESS dynamic SQL and dynamic PL/SQL from WITHIN a PL/SQL program. It allows you to execute statements that are otherwise impossible from within PL/SQL programs, which includes DDL, DCL, or TRANSACTION CONTROL statements.

The DBMS_SQL package is used to access dynamic SQL and dynamic PL/SQL from within a PL/SQL program. It allows you to execute statements that are otherwise impossible from within PL/SQL programs, which includes DDL, DCL, or Transaction Control statements.

SQL STATEMENTS are compiled using the following steps:

• Parse—Refers to a STEP in which statements are checked for EXISTENCE of all objects used and the SYNTAX is validated.
• Bind— Refers to a step In which the values for the variables are obtained. This process is also called binding variables.
• Execute —Refers to a step in which the statement is executed by Oracle.
• Fetch —Refers to a step in which rows are SELECTED and retrieved one by one until last row is fetched.

SQL statements are compiled using the following steps:

Dynamic SQL is used by PL/SQL to EXECUTE Data DEFINITION Language (DDL) statements, Data Control (DCL) statements, or Transaction Control statements within PL/SQL blocks. These statements are not stored within the source CODE but are stored as character variables in the program. The SQL statements are created dynamically at RUNTIME by using variables. This is used either using native dynamic SQL or through the DBMS_SQL package.

Dynamic SQL is used by PL/SQL to execute Data Definition Language (DDL) statements, Data Control (DCL) statements, or Transaction Control statements within PL/SQL blocks. These statements are not stored within the source code but are stored as character variables in the program. The SQL statements are created dynamically at runtime by using variables. This is used either using native dynamic SQL or through the DBMS_SQL package.

The catproc.sql is a SQL file, which has the script to create packages in the DATABASE, This file has to be EXECUTED on the SQL PROMPT for the packages to be available in the database.

The catproc.sql is a SQL file, which has the script to create packages in the database, This file has to be executed on the SQL prompt for the packages to be available in the database.

Oracle server provides the Oracle Supplied Packages to ALLOW PROCEDURAL Language/Structured Query Language (PL/SQL) to access certain FEATURES of SQL, which are not available in PL/SQL. They also help in EXTENDING the functionality of the database.

Oracle server provides the Oracle Supplied Packages to allow Procedural Language/Structured Query Language (PL/SQL) to access certain features of SQL, which are not available in PL/SQL. They also help in extending the functionality of the database.

The PRAGMA KEYWORD SIGNIFIES that the STATEMENT is a compiler directive, which is not processed when the PL/SQL is executed. It is a pseudo-instruction that tells the compiler to interpret all the occurrences of EXCEPTION name within the block with the associated ORACLE server number.

The PRAGMA keyword signifies that the statement is a compiler directive, which is not processed when the PL/SQL is executed. It is a pseudo-instruction that tells the compiler to interpret all the occurrences of exception name within the block with the associated Oracle server number.

• RAISE is USED to call pre-defined EXCEPTIONS declared in the declarative section of the PL/SQL block.
• RAISE_APPLICATION_ERROR is used to call user-defined ORA - error MESSAGES from STORED subprograms.

User-defined EXCEPTIONS are explicitly RAISED; WHEREAS, ORACLE- defined exceptions are raised implicitly.

User-defined exceptions are explicitly raised; whereas, Oracle- defined exceptions are raised implicitly.

If an exception is raised and there is no handler in the current block, the exception WOULD PROPAGATE to the enclosing block. If no handler is FOUND in the enclosing block, the exception will be propagated to the CALLING environment as an UNHANDLED exception.

If an exception is raised and there is no handler in the current block, the exception would propagate to the enclosing block. If no handler is found in the enclosing block, the exception will be propagated to the calling environment as an unhandled exception.

The ADVANTAGES of having a separate EXCEPTION section within a PL/SQL CODE are as follows:

•  It is easier to understand the program logic as it is easily visible and readable
•  Any error in any statement will be DETECTED and handled easily

The advantages of having a separate exception section within a PL/SQL code are as follows:

The ROW__TYPE_MISMATCH EXCEPTION is RAISED when a host cursor variable and a PL/SQL cursor variable INVOLVED in an assignment have incompatible RETURN types.

The ROW__TYPE_MISMATCH exception is raised when a host cursor variable and a PL/SQL cursor variable involved in an assignment have incompatible return types.

This EXCEPTION is RAISED when the program TRIES to open an already opened CURSOR. The cursor should be closed before it can be opened again.

This exception is raised when the program tries to open an already opened cursor. The cursor should be closed before it can be opened again.

When an EXCEPTION is raised WITHIN an exception section of a BLOCK, the control is transferred to the exception section of the ENCLOSING block.

When an exception is raised within an exception section of a block, the control is transferred to the exception section of the enclosing block.

The RAISE statement can be used to re-raise the current EXCEPTION. It STOPS the normal execution of a PL/SQL program and transfers the CONTROL to an exception handler, as SHOWN in the following code:

The RAISE statement can be used to re-raise the current exception. It stops the normal execution of a PL/SQL program and transfers the control to an exception handler, as shown in the following code:

This exception is RAISED when the program attempts to perform an illegal OPERATION, such as closing an UNOPENED CURSOR.

This exception is raised when the program attempts to perform an illegal operation, such as closing an unopened cursor.

The default behavior of the RAISE_APPLICATION_ERROR procedure is to overwrite the error message stack. However, if the parameter is PASSED, then the MESSAGES can be appended to the error stack as well. Following is the syntax of the RAISE_APPLICATION_ERROR procedure:

In the preceding syntax, TRUE adds the messages to the error stack; whereas, FALSE overwrites the error stack.

The default behavior of the RAISE_APPLICATION_ERROR procedure is to overwrite the error message stack. However, if the parameter is passed, then the messages can be appended to the error stack as well. Following is the syntax of the RAISE_APPLICATION_ERROR procedure:

In the preceding syntax, TRUE adds the messages to the error stack; whereas, FALSE overwrites the error stack.

The following method can be used to RETRY a transaction after an exception is raised:

• The transaction should be written within a sub-block
• The sub-block can be placed inside a LOOP that repeats the transaction.
• Mark a SAVEPOINT before starting the transaction. If the transaction SUCCEEDS, then COMMIT and exit from the loop. If the transaction fails, the control is transferred to the exception handler where the transaction is rolled back to the savepoint to undo the changes, and then try to fix the problem.

The following method can be used to retry a transaction after an exception is raised:

After the exception is handled, processing cannot be resumed within the EXECUTABLE section of the current block from where the exception was raised. The current block where the exception handler is DECLARED will be terminated. The control will pass to the STATEMENT in the enclosing executable block. If there is no enclosing block, control will pass BACK to the calling host environment.

After the exception is handled, processing cannot be resumed within the executable section of the current block from where the exception was raised. The current block where the exception handler is declared will be terminated. The control will pass to the statement in the enclosing executable block. If there is no enclosing block, control will pass back to the calling host environment.

If an EXCEPTION is not HANDLED in a sub-block, the exception propagates to the enclosing blocks until the associated handler is FOUND. If none of the blocks has a handler for the exception, an unhandled exception ERROR is passed BACK to the host environment as follows:

If an exception is not handled in a sub-block, the exception propagates to the enclosing blocks until the associated handler is found. If none of the blocks has a handler for the exception, an unhandled exception error is passed back to the host environment as follows:

YES, the RAISE_APPLICATION_ERROR procedure can be called in the executable SECTION as WELL as the EXCEPTION section of the PL/SQL code.

Yes, the RAISE_APPLICATION_ERROR procedure can be called in the executable section as well as the exception section of the PL/SQL code.

When a sub-block HANDLES an exception, the CONTROL passes to the NEXT statement in the executable section of the enclosing block immediately after the END statement of the sub-block.

When a sub-block handles an exception, the control passes to the next statement in the executable section of the enclosing block immediately after the END statement of the sub-block.

When the RAISE_APPLICATION_ERROR PROCEDURE is encountered, it ends the PROGRAM UNIT and RETURNS the error number and message to the application. The error number and message can be trapped LIKE any Oracle error.

When the RAISE_APPLICATION_ERROR procedure is encountered, it ends the program unit and returns the error number and message to the application. The error number and message can be trapped like any Oracle error.

User-defined EXCEPTIONS are defined in the declarative section of the PL/SQL code and raised EXPLICITLY USING the RAISE statement, as shown in the FOLLOWING code snippet:

User-defined exceptions are defined in the declarative section of the PL/SQL code and raised explicitly using the RAISE statement, as shown in the following code snippet:

These functions can be used with the OTHERS exception handler to GET the error number and the error MESSAGE that has CAUSED the OTHERS exception to be raised, as shown in the following code SNIPPET;

These functions can be used with the OTHERS exception handler to get the error number and the error message that has caused the OTHERS exception to be raised, as shown in the following code snippet;