Explore topic-wise InterviewSolutions in .

The IMPLICITLY defined record can be populated field by field or the ENTIRE record can be populated in one STATEMENT, as SHOWN in the FOLLOWING code:

The implicitly defined record can be populated field by field or the entire record can be populated in one statement, as shown in the following code:

Implicit RECORDS are USEFUL as there is no hard-code DESCRIPTION NEEDED. As they are based on a database table record, any change in the database table record will be automatically REFLECTED in the record.

Implicit records are useful as there is no hard-code description needed. As they are based on a database table record, any change in the database table record will be automatically reflected in the record.

Implicitly defined records are those that do not have the need to describe each field separately in the RECORD definition. The structure of the record is not defined using the TYPE STATEMENT; instead, the %ROWTYPE attribute is used to define the IMPLICIT record to have the same structure as the DATABASE record. FOLLOWING code shows the declaration of the implicit defined records:

Implicitly defined records are those that do not have the need to describe each field separately in the record definition. The structure of the record is not defined using the TYPE statement; instead, the %ROWTYPE attribute is used to define the implicit record to have the same structure as the database record. Following code shows the declaration of the implicit defined records:

Yes, it is possible to DEFINE a DEFAULT value for a record field, as SHOWN in the following CODE:

Yes, it is possible to define a default value for a record field, as shown in the following code:

The VALUE of %NOTFOUND attribute will be NULL after the CURSOR is OPENED but before the FETCH statement is executed. After the FETCH statement, the value of %NOTFOUND attribute will be TRUE OR FALSE depending on the result of the FETCH statement.

The value of %NOTFOUND attribute will be NULL after the cursor is opened but before the FETCH statement is executed. After the FETCH statement, the value of %NOTFOUND attribute will be TRUE OR FALSE depending on the result of the FETCH statement.

No, the IF-THEN-ELSIF BLOCK can have only one ELSE clause at the END of the it, which is executed if no other CONDITION is evaluated to TRUE.

No, the IF-THEN-ELSIF block can have only one ELSE clause at the end of the it, which is executed if no other condition is evaluated to TRUE.

Index-By TABLES are instantiated AUTOMATICALLY. No special constructor is required to initialize an Index-By table. WHENEVER an element of the collection is REFERRED, it is initialized.

Index-By tables are instantiated automatically. No special constructor is required to initialize an Index-By table. Whenever an element of the collection is referred, it is initialized.

A CONSTRUCTOR is a SPECIAL built-in FUNCTION that is used to initialize nested tables and VARRAYs. Constructor has the same name as the collection and can take any number of ARGUMENTS, as shown in the following CODE:

A constructor is a special built-in function that is used to initialize nested tables and VARRAYs. Constructor has the same name as the collection and can take any number of arguments, as shown in the following code:

The value of SQL%F0UND ATTRIBUTE will be NULL before an INSERT STATEMENT is executed and TRUE after the successful INSERTION into a table.

The value of SQL%F0UND attribute will be NULL before an INSERT statement is executed and TRUE after the successful insertion into a table.

A COLLECTION type cannot be used for defining the DATATYPE of FIELDS; only SCALAR datatype can be used for a PL/SQL record.

A collection type cannot be used for defining the datatype of fields; only scalar datatype can be used for a PL/SQL record.

Two RECORDS can be COMPARED by using the equality (comparison) operator for each FIELD of the record. However, the entire record cannot be compared as a whole.

Two records can be compared by using the equality (comparison) operator for each field of the record. However, the entire record cannot be compared as a whole.

The SELECT INTO CLAUSE should be USED when the result of the query is known to return a SINGLE row. When more than one row is to be RETURNED by the SQL query, then the FETCH clause should be used. If the FETCH clause is not used and the query returns more than one row, then the runtime error is generated in the code.

The SELECT INTO clause should be used when the result of the query is known to return a single row. When more than one row is to be returned by the SQL query, then the FETCH clause should be used. If the FETCH clause is not used and the query returns more than one row, then the runtime error is generated in the code.

Both nested TABLES and VARRAYS are collection types that can be stored in the database. They differ in TWO properties, bounding and sparsity. Bounding refers to the limit on the NUMBER of elements that a collection can have; whereas, sparsity means whether there can be gaps within the subscripts or not.

Nested tables are not bounded and can be sparse; whereas, VARRAYs are bounded and cannot be sparse. It means that VARRAYs have a limit on the number of elements and are bounded; whereas, nested tables does not have a limit on the number of elements and are unbounded. Nested tables can BECOME sparse if elements are deleted; whereas, VARRAYs can never become sparse.

Both nested tables and VARRAYs are collection types that can be stored in the database. They differ in two properties, bounding and sparsity. Bounding refers to the limit on the number of elements that a collection can have; whereas, sparsity means whether there can be gaps within the subscripts or not.

Nested tables are not bounded and can be sparse; whereas, VARRAYs are bounded and cannot be sparse. It means that VARRAYs have a limit on the number of elements and are bounded; whereas, nested tables does not have a limit on the number of elements and are unbounded. Nested tables can become sparse if elements are deleted; whereas, VARRAYs can never become sparse.

No, Index-By table cannot be STORED in a DATABASE. The NESTED tables and VARRAYs collection types can be used to STORE in database.

No, Index-By table cannot be stored in a database. The nested tables and VARRAYs collection types can be used to store in database.

If the upper BOUND of a FOR-LOOP is assigned a value smaller than the lower bound of the loop, then the STATEMENTS WITHIN the loop are not executed and the CONTROL is PASSED to the statement after the loop.

If the upper bound of a FOR-LOOP is assigned a value smaller than the lower bound of the loop, then the statements within the loop are not executed and the control is passed to the statement after the loop.

Yes, it is POSSIBLE to ASSIGN a dynamic value to the LOOP counter at the time of execution, as shown in the FOLLOWING code snippet:

In the preceding code snippet, the employee COUNT is derived from the table at runtime and the counter is used to iterate the FOR-LOOP.

Yes, it is possible to assign a dynamic value to the loop counter at the time of execution, as shown in the following code snippet:

In the preceding code snippet, the employee count is derived from the table at runtime and the counter is used to iterate the FOR-LOOP.

The COUNTER of a FOR-LOOP can only be used as a constant within the loop. It cannot be assigned a VALUE INSIDE the loop, as that would change the bounds of the loop, which is logically not possible.

The counter of a FOR-LOOP can only be used as a constant within the loop. It cannot be assigned a value inside the loop, as that would change the bounds of the loop, which is logically not possible.

Yes, It is POSSIBLE to label a LOOP SIMILAR to a PL/SQL block. Loop labels are undeclared identifiers enclosed within the double angle brackets. This helps in better readability in case of nested loops. In addition, this helps in using the EXIT statement with the loop label in the nested loops. Label of a loop should appear before the LOOP statement, as SHOWN in the FOLLOWING code snippet:

Yes, It is possible to label a loop similar to a PL/SQL block. Loop labels are undeclared identifiers enclosed within the double angle brackets. This helps in better readability in case of nested loops. In addition, this helps in using the EXIT statement with the loop label in the nested loops. Label of a loop should appear before the LOOP statement, as shown in the following code snippet:

The DATATYPE of a REORD Type can be a NUMBER, VRCHAR2, %TYPE, or %ROWTYPE; however, it cannot be a'REF CURSOR.

The datatype of a REORD Type can be a NUMBER, VRCHAR2, %TYPE, or %ROWTYPE; however, it cannot be a'REF CURSOR.

The WHERE CURRENT OF clause is used to reference the current row of an ACTIVE EXPLICIT CURSOR. When this clause is used, ROWID is not needed to access the current row for modifications.

The WHERE CURRENT OF clause is used to reference the current row of an active explicit cursor. When this clause is used, ROWID is not needed to access the current row for modifications.

The SELECT FOR UPDATE clause is used to lock rows in a table when an update or delete transaction is performed on the active SET. It selects the rows of the table that have to be modified and LOCKS each row in the result set. This prevents any other user from accessing the same set of records while modification is being executed on the table. You should ADD the NOWAIT clause to the SELECT FOR UPDATE statement to prevent the procedure from waiting indefinitely in case the lock cannot be ACQUIRED. If the NOWAIT clause is used, the control will return to the program in case the requested rows have been locked by another user.

The SELECT FOR UPDATE clause is used to lock rows in a table when an update or delete transaction is performed on the active set. It selects the rows of the table that have to be modified and locks each row in the result set. This prevents any other user from accessing the same set of records while modification is being executed on the table. You should add the NOWAIT clause to the SELECT FOR UPDATE statement to prevent the procedure from waiting indefinitely in case the lock cannot be acquired. If the NOWAIT clause is used, the control will return to the program in case the requested rows have been locked by another user.

Cursor FOR loop is a shortcut to use explicit CURSORS. It does not require explicit opening, fetching, and closing of the cursor. In other words, the cursor FOR loop implicitly opens the cursor, fetches the record, and closes the cursor. In the cursor FOR loop, the cursor is defined and its attributes are checked within the PROCESSING of the FOR loop. The RECORDS are also implicitly defined within the loop, as shown in the following code:

It is also possible to use a cursor FOR loop with a subquery, where the cursor NAME is not defined. In this case, the attributes of the cursor cannot be checked as no explicit cursor name is defined.

Cursor FOR loop is a shortcut to use explicit cursors. It does not require explicit opening, fetching, and closing of the cursor. In other words, the cursor FOR loop implicitly opens the cursor, fetches the record, and closes the cursor. In the cursor FOR loop, the cursor is defined and its attributes are checked within the processing of the FOR loop. The records are also implicitly defined within the loop, as shown in the following code:

It is also possible to use a cursor FOR loop with a subquery, where the cursor name is not defined. In this case, the attributes of the cursor cannot be checked as no explicit cursor name is defined.

Yes, It is POSSIBLE to define a record TYPE based on the selected columns of an explicit cursor. This helps in RETRIEVING the entire row TOGETHER into the defined record and further processing. The values of the row are DIRECTLY loaded into the fields of the record.

Yes, It is possible to define a record type based on the selected columns of an explicit cursor. This helps in retrieving the entire row together into the defined record and further processing. The values of the row are directly loaded into the fields of the record.

A loop MUST be DEFINED to fetch multiple ROWS from an explicit cursor. It will fetch one row from the active cursor set on each iteration of the loop, as shown in the following code:

A loop must be defined to fetch multiple rows from an explicit cursor. It will fetch one row from the active cursor set on each iteration of the loop, as shown in the following code:

The advantages of using the %ROWTYPE datatype to declare variables are given as follows:

•  It is useful to RETRIEVE an ENTIRE row from a table. If you do not use the %ROWTYPE datatype, then you have to declare variables for each column separately.
•  It can be USED EVEN if datatype of the table columns is not known
•  It ensures that datatype of the variable changes dynamically if the underlying table is altered.

The advantages of using the %ROWTYPE datatype to declare variables are given as follows:

The FOLLOWING operations occur when the OPEN cursor command is EXECUTED for a cursor:

•  Memory area for PROCESSING is allocated
•  The SELECT statement is parsed
•  The INPUT variables are assigned the values by obtaining the memory addresses
•  The active set of rows that fulfill the select criteria are identified
•  The pointer is set just before the first row of the active set

The following operations occur when the OPEN cursor command is executed for a cursor:

A RECORD is a collection of data items, which differ from each in other in datatype, but are logically related. For example, an EMPLOYEE record with name, age, address, and telephone NUMBER. These are all part of employee record but have different datatypes.
In PL/SQL, the record type is DEFINED FIRST and then the record of that type is declared as follows:

We have defined the record type by the name emp_record_type and then declared a record, emp_record, of that type.

A RECORD is a collection of data items, which differ from each in other in datatype, but are logically related. For example, an employee record with name, age, address, and telephone number. These are all part of employee record but have different datatypes.
In PL/SQL, the record type is defined first and then the record of that type is declared as follows:

We have defined the record type by the name emp_record_type and then declared a record, emp_record, of that type.

You can USE the SQL%NOTFOUND attribute to check if the UPDATE statement has successfully UPDATED any rows or not. This attribute RETURNS the TRUE VALUE if the last executed SQL statement has not AFFECTED any rows.

You can use the SQL%NOTFOUND attribute to check if the UPDATE statement has successfully updated any rows or not. This attribute returns the TRUE value if the last executed SQL statement has not affected any rows.

PSEUDO columns are not ACTUAL columns in a table but they behave like columns. They are used in SQL statements to retrieve SPECIFIC INFORMATION. PL/SQL recognizes pseudo-columns as a part of SQL statements but they cannot be used directly in a procedural language. The following pseudo-columns are used in SQL:

•  ROWID
•  ROWNUM
•  LEVEL
•  CURRVAL
•  NEXTVAL

Pseudo columns are not actual columns in a table but they behave like columns. They are used in SQL statements to retrieve specific information. PL/SQL recognizes pseudo-columns as a part of SQL statements but they cannot be used directly in a procedural language. The following pseudo-columns are used in SQL:

The SAVEPOINT command is used to set a point within a transaction to which you MAY rollback. This command helps in CANCELLING the portions of the current transaction. Using ROLLBACK with the SAVEPOINT TO clause, a transaction can be undone in parts RATHER than rolling BACK the entire transaction.

The SAVEPOINT command is used to set a point within a transaction to which you may rollback. This command helps in cancelling the portions of the current transaction. Using ROLLBACK with the SAVEPOINT TO clause, a transaction can be undone in parts rather than rolling back the entire transaction.

Transaction CONTROL statements are the COMMIT and REVOKE commands that control the logic of transactions WITHIN a database. These statements are valid within a PL/SQL block. The COMMIT command terminates the active transaction and makes the changes permanent to the database. The ROLLBACK command terminates the active transaction but CANCELS any changes that were MADE to the database.

Transaction Control statements are the COMMIT and REVOKE commands that control the logic of transactions within a database. These statements are valid within a PL/SQL block. The COMMIT command terminates the active transaction and makes the changes permanent to the database. The ROLLBACK command terminates the active transaction but cancels any changes that were made to the database.

These commands are USED with explicit cursors. First, the CURSOR is OPENED to make it active using the OPEN command. Then, the FETCH command is used to retrieve the ROWS from the active cursor. After all the rows are retrieved, the cursor is closed using the CLOSE command that can again be reopened. Any other operation on a closed cursor returns the predefined exception, INVALID_CURSOR. However, these commands cannot be used to control the SQL implicit cursors.

These commands are used with explicit cursors. First, the cursor is opened to make it active using the OPEN command. Then, the FETCH command is used to retrieve the rows from the active cursor. After all the rows are retrieved, the cursor is closed using the CLOSE command that can again be reopened. Any other operation on a closed cursor returns the predefined exception, INVALID_CURSOR. However, these commands cannot be used to control the SQL implicit cursors.

SQL is the name of the implicit CURSOR, which is always opened when the database executes the SQL statement. SQL%ISOPEN always EVALUATES to FALSE as ORACLE CLOSES the implicit cursor as SOON as it executes the query.

SQL is the name of the implicit cursor, which is always opened when the database executes the SQL statement. SQL%ISOPEN always evaluates to FALSE as ORACLE closes the implicit cursor as soon as it executes the query.

A cursor is a SQL memory work area. Any SQL statement always uses a cursor for the execution of statements and fetching of results. This is implicitly defined and is CALLED implicit cursor.
When a programmer DEFINES a cursor for fetching and PARSING results of a SQL QUERY, which returns more than one row, it is called an explicit cursor.

A cursor is a SQL memory work area. Any SQL statement always uses a cursor for the execution of statements and fetching of results. This is implicitly defined and is called implicit cursor.
When a programmer defines a cursor for fetching and parsing results of a SQL query, which returns more than one row, it is called an explicit cursor.

Any variable declared within a block is local to the block and global to the NESTED BLOCKS. The variables declared within the nested blocks are unknown to the outer blocks. They OVERRIDE the references to any outer declared variables with the same NAME UNLESS they are used with the block label name.

Any variable declared within a block is local to the block and global to the nested blocks. The variables declared within the nested blocks are unknown to the outer blocks. They override the references to any outer declared variables with the same name unless they are used with the block label name.

A bind variable is a variable within a SQL statement that has to be replaced by a VALID value on the successful execution of the statement. This value can be referenced into the PL/SQL block. Bind variable is ALWAYS PRECEDED by a COLON (:), as shown in the FOLLOWING code:

A bind variable is a variable within a SQL statement that has to be replaced by a valid value on the successful execution of the statement. This value can be referenced into the PL/SQL block. Bind variable is always preceded by a colon (:), as shown in the following code:

Anonymous blocks, as the name suggests, are PL/SQL blocks that are not given any name and cannot be stored in the database. These blocks can call ANOTHER blocks but they cannot be called by any other block, as they do not have a name. An anonymous block is EXECUTED by either storing the block CODE in a file or writing the block code at the SQL prompt.

Anonymous blocks, as the name suggests, are PL/SQL blocks that are not given any name and cannot be stored in the database. These blocks can call another blocks but they cannot be called by any other block, as they do not have a name. An anonymous block is executed by either storing the block code in a file or writing the block code at the SQL prompt.

DDL COMMANDS, such as create, alter, revoke, and GRANT, cannot be used directly within the procedural language block. They can only be called USING built-in Native DYNAMIC SQL or Dynamic SQL Package, which allows the execution of the DDL statements at runtime.

DDL commands, such as create, alter, revoke, and grant, cannot be used directly within the procedural language block. They can only be called using built-in Native Dynamic SQL or Dynamic SQL Package, which allows the execution of the DDL statements at runtime.

The if...then...ELSE statement is the equivalent of DECODE FUNCTION within the procedural STATEMENTS.

The if...then...else statement is the equivalent of DECODE function within the procedural statements.

The MAXIMUM LENGTH of an IDENTIFIER in a PL/SQL BLOCK is 30.

The maximum length of an identifier in a PL/SQL block is 30.

The SQL group functions, such as AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE, cannot be directly USED within PL/SQL. However, they can be embedded within SQL statements and then can be utilized in the procedural statements.

The SQL group functions, such as AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE, cannot be directly used within PL/SQL. However, they can be embedded within SQL statements and then can be utilized in the procedural statements.

No, a NOT NULL constraint does not apply to VARIABLES declared using the %TYPE attribute. These variables can be assigned a null VALUE in a PL/SQL block, as shown in the FOLLOWING CODE snippet:

No, a NOT NULL constraint does not apply to variables declared using the %TYPE attribute. These variables can be assigned a null value in a PL/SQL block, as shown in the following code snippet:

Yes, a variable can be ASSIGNED a value from a database. You can FETCH the value from the database and ASSIGN it to the variable using the into keyword, as shown in the following CODE:

Yes, a variable can be assigned a value from a database. You can fetch the value from the database and assign it to the variable using the into keyword, as shown in the following code:

YES, ARRAYS can be defined and used in a PL/SQL BLOCK USING the composite datatypes, such as RECORD, TABLE, NESTED TABLE, and VARRAY.

Yes, arrays can be defined and used in a PL/SQL block using the composite datatypes, such as RECORD, TABLE, NESTED TABLE, and VARRAY.

The BEGIN and END KEYWORDS are MANDATORY for any PL/SQL BLOCK.

The BEGIN and END keywords are mandatory for any PL/SQL block.

PL/SQL is a transaction processing procedural language that has the following advantages:

1. Integration with database —PL/SQL SUPPORTS SQL statements, thus enabling integration between procedural language capabilities and the database technology.
2. Better performance —SQL statements are grouped within a PL/SQL block and sent TOGETHER to database for parsing; therefore, it reduces the network traffic and increases the performance.
3. Higher productivity —-PL/SQL HELPS the package reusable code within the well-defined modular blocks; and therefore, increases the productivity.
4. Portability —Programs PACKAGES and libraries written in PL/SQL can be reused in DIFFERENT environments.

PL/SQL is a transaction processing procedural language that has the following advantages:

Structured Query Language (SQL) is a non-procedural language that interacts with the database, and is used for database MANIPULATION using the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. CONTROL statements cannot be used in SQL, which is compiled and executed statement by statement at the runtime (late-binding).

PL/SQL is a programming language that allows the USAGE of Cursor Control statements and Transaction Control statements, such as if...then...else. It integrates with SQL functions and statements to interact and manipulate with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the machine-readable code, which is executed at runtime (EARLY binding); and therefore, improves the performance.

Structured Query Language (SQL) is a non-procedural language that interacts with the database, and is used for database manipulation using the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Control statements cannot be used in SQL, which is compiled and executed statement by statement at the runtime (late-binding).

PL/SQL is a programming language that allows the usage of Cursor Control statements and Transaction Control statements, such as if...then...else. It integrates with SQL functions and statements to interact and manipulate with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the machine-readable code, which is executed at runtime (early binding); and therefore, improves the performance.

The SET OPTION TERMOUT controls OUTPUT to the SCREEN. Setting TERMOUT OFF TURNS off screen output. This option can be SHORTENED to TERM.

The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.

The SET OPTIONS FEEDBACK and VERIFY can be set to OFF.

The SET options FEEDBACK and VERIFY can be set to OFF.

By USE of the SPOOL COMMAND

By use of the SPOOL command