Oracle PL/SQL Quiz: Trivia Exam!

1. Select invalid variable types.

CHAR

VARCHAR1

VARCHAR2

INTEGER

NUMBER

VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.

Explanation

VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.

2. Select the best answer. This is an example of what _____ type of cursor?

DECLARE
    l_date   DATE;

    CURSOR c1 IS
   SELECT TRUNC(SYSDATE)
     FROM DUAL;
BEGIN

    OPEN c1;
         FETCH c1 INTO l_date;
    CLOSE c1;
END;

Explicit

Implicit

Select

PL/SQL

None of the above

The given code snippet explicitly declares a cursor named c1 using the CURSOR keyword. This indicates that the cursor is explicitly defined by the programmer. Therefore, the correct answer is Explicit.

Explanation

The given code snippet explicitly declares a cursor named c1 using the CURSOR keyword. This indicates that the cursor is explicitly defined by the programmer. Therefore, the correct answer is Explicit.

3. Where do you declare an explicit cursor in the PL/SQL language?

In the PL/SQL working storage section

In the PL/SQL declaration section

In the PL/SQL body section

In the PL/SQL exception section

None of the above

In the PL/SQL language, an explicit cursor is declared in the PL/SQL declaration section. This section is where variables, constants, and cursors are declared before they are used in the program. By declaring the cursor in this section, it allows the program to define the cursor and its associated query before executing it. This helps in organizing the code and making it more readable and maintainable.

Explanation

In the PL/SQL language, an explicit cursor is declared in the PL/SQL declaration section. This section is where variables, constants, and cursors are declared before they are used in the program. By declaring the cursor in this section, it allows the program to define the cursor and its associated query before executing it. This helps in organizing the code and making it more readable and maintainable.

4. The || is is an example of what function

SELECT last_name || ', ' || first_name || ' ' || middle_name
FROM employees;

Incantination

Integration

Continuation

Concatenation

None of the above

The || symbol in the given query is used to concatenate or join different strings together. In this case, it is used to concatenate the last name, a comma, first name, a space, middle name, and so on. Therefore, the correct answer is "Concatenation".

Explanation

The || symbol in the given query is used to concatenate or join different strings together. In this case, it is used to concatenate the last name, a comma, first name, a space, middle name, and so on. Therefore, the correct answer is "Concatenation".

5. List the correct sequence of commands to process a set of records when using explicit cursors.

INITIALIZE, GET, CLOSE

CURSOR, GET, FETCH, CLOSE

OPEN, FETCH, CLOSE

CURSOR, FETCH, CLOSE

GET, SEEK, HIDE

The correct sequence of commands to process a set of records when using explicit cursors is to first open the cursor, then fetch the records one by one, and finally close the cursor. Opening the cursor initializes it and prepares it for fetching records. Fetching retrieves the records from the cursor one by one. Closing the cursor releases any resources associated with it. This sequence ensures that the cursor is properly initialized, records are retrieved, and resources are freed after processing.

Explanation

The correct sequence of commands to process a set of records when using explicit cursors is to first open the cursor, then fetch the records one by one, and finally close the cursor. Opening the cursor initializes it and prepares it for fetching records. Fetching retrieves the records from the cursor one by one. Closing the cursor releases any resources associated with it. This sequence ensures that the cursor is properly initialized, records are retrieved, and resources are freed after processing.

6. Select incorrect variable declarations.

Foo_number varchar2(10);

Foo_text number(10);

Foo_char char(1) := 'Y';

Foo_time date;

Foo_text varchar2(10) := 'hello world';

The length of string 'hello world' (11) exceeds declared variable length of 10.

Explanation

The length of string 'hello world' (11) exceeds declared variable length of 10.

7. What command can you use to see the errors from a recently created view or stored procedure?

SHOW MISTAKES;

DISPLAY MISTAKES;

DISPLAY ERRORS;

SHOW ERRORS;

None of the above.

The command "SHOW ERRORS;" can be used to see the errors from a recently created view or stored procedure. This command allows users to view any errors or mistakes that may have occurred during the creation or execution of the view or stored procedure. It provides a way to identify and troubleshoot any issues or errors in the code.

Explanation

The command "SHOW ERRORS;" can be used to see the errors from a recently created view or stored procedure. This command allows users to view any errors or mistakes that may have occurred during the creation or execution of the view or stored procedure. It provides a way to identify and troubleshoot any issues or errors in the code.

8. Select the best answer describing the maximum number of times the COMMIT will be executed.

Example

FOR i IN 1..1000 LOOP
   ...
   IF MOD(i, 100) = 0 THEN
      COMMIT;
   END IF;
   ...
END LOOP;

The commit is fired 1000 times

The commit is fired 100 times

The commit is fired 10 times

The commit is fired 1 time

None of the above.

The commit is fired 10 times because the loop iterates from 1 to 1000, and the IF statement checks if the current iteration is a multiple of 100. Since there are 10 multiples of 100 between 1 and 1000 (100, 200, 300, ..., 1000), the COMMIT statement will be executed 10 times.

Explanation

The commit is fired 10 times because the loop iterates from 1 to 1000, and the IF statement checks if the current iteration is a multiple of 100. Since there are 10 multiples of 100 between 1 and 1000 (100, 200, 300, ..., 1000), the COMMIT statement will be executed 10 times.

9. Select the best answer. PACKAGE foo_foo IS PROCEDURE foo ( p_foo_text IN VARCHAR2 ); PROCEDURE foo (p_foo_number IN NUMBER); END;

Package specification is invalid. Too many procedures named foo.

Package specification is invalid. First procedure should be called called foo_1, second procedure should be called foo_2.

Package specification is valid. We can have an unlimited number of procedures name foo.

Package specification is valid. This is an example of overloading.

Package specification is invalid. We can only have one procedure named foo in the package.

The package specification is valid because it demonstrates the concept of overloading. Overloading allows multiple procedures with the same name to exist in the same package, as long as they have different parameter lists. In this case, the package has two procedures named foo, but they have different parameter types (VARCHAR2 and NUMBER). This allows for flexibility and convenience when calling the procedures, as the appropriate procedure can be automatically selected based on the parameter types provided.

Explanation

The package specification is valid because it demonstrates the concept of overloading. Overloading allows multiple procedures with the same name to exist in the same package, as long as they have different parameter lists. In this case, the package has two procedures named foo, but they have different parameter types (VARCHAR2 and NUMBER). This allows for flexibility and convenience when calling the procedures, as the appropriate procedure can be automatically selected based on the parameter types provided.

10. Select the best answer below. What are the components of a package?

Box, wrapping and binding

Header and body

Specification and content

Specification and body

None of the above

The components of a package typically include the specification, which outlines the details and requirements of the package, and the body, which is the main part or content of the package. These components are essential in ensuring that the package is properly designed and contains the necessary information or items. The other options listed, such as box, wrapping, binding, header, and content, may be related to packaging but do not accurately represent the main components of a package.

Explanation

The components of a package typically include the specification, which outlines the details and requirements of the package, and the body, which is the main part or content of the package. These components are essential in ensuring that the package is properly designed and contains the necessary information or items. The other options listed, such as box, wrapping, binding, header, and content, may be related to packaging but do not accurately represent the main components of a package.

11. Select the best answer to complete this variable declaration for a column value.

DECLARE
l_foo_column_id SOME_TABLE.SOME_COLUMN_________;
BEGIN
...

%ID

%ROWTYPE

%COLUMNTYPE

%TYPE

None of the above.

The correct answer is "%TYPE". In PL/SQL, "%TYPE" is used to declare a variable that has the same data type as a specified column in a table. In this case, the variable "l_foo_column_id" will have the same data type as the "SOME_COLUMN" in the "SOME_TABLE". This allows for flexibility and ensures that the variable matches the data type of the column it is referencing.

Explanation

The correct answer is "%TYPE". In PL/SQL, "%TYPE" is used to declare a variable that has the same data type as a specified column in a table. In this case, the variable "l_foo_column_id" will have the same data type as the "SOME_COLUMN" in the "SOME_TABLE". This allows for flexibility and ensures that the variable matches the data type of the column it is referencing.

12. Enter the missing keyword

IF foo = 1 THEN
l_text := 'A';
______ foo = 2 THEN
l_text := 'B';
ELSE
l_text := 'C';
END IF;

ELSE

ELSE IF

ELIF

ELSIF

None of the above

The correct keyword to fill in the blank is "ELSIF". In this code snippet, the IF statement checks the value of the variable "foo". If foo is equal to 1, then the variable "l_text" is assigned the value 'A'. If foo is equal to 2, then "l_text" is assigned the value 'B'. If none of these conditions are met, then "l_text" is assigned the value 'C'. The keyword "ELSIF" is used to check for additional conditions after the initial IF statement.

Explanation

The correct keyword to fill in the blank is "ELSIF". In this code snippet, the IF statement checks the value of the variable "foo". If foo is equal to 1, then the variable "l_text" is assigned the value 'A'. If foo is equal to 2, then "l_text" is assigned the value 'B'. If none of these conditions are met, then "l_text" is assigned the value 'C'. The keyword "ELSIF" is used to check for additional conditions after the initial IF statement.

13. Assuming the date and time is 09/09/2009 09:09:09, what value will the following statement return

SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS')
FROM dual;

09/09/2009 09:09:09

09/09/2009 09:09:09AM

09/09/2009

09/09/2009 00:00:00

None of the above

The given statement uses the TRUNC function to truncate the current date (SYSDATE) to the nearest day. The TO_CHAR function then converts this truncated date to a string format with the format 'MM/DD/YYYY HH24:MI:SS'. Since the time portion of the date is truncated, it will be set to 00:00:00. Therefore, the statement will return the value '09/09/2009 00:00:00'.

Explanation

The given statement uses the TRUNC function to truncate the current date (SYSDATE) to the nearest day. The TO_CHAR function then converts this truncated date to a string format with the format 'MM/DD/YYYY HH24:MI:SS'. Since the time portion of the date is truncated, it will be set to 00:00:00. Therefore, the statement will return the value '09/09/2009 00:00:00'.

14. Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor.

%NOTFOUND

%FOUND

%ROWCOUNT

%ISOPEN

None of the above. All of these are valid.

All of the listed attributes (%NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN) are valid attributes of an Explicit cursor.

Explanation

All of the listed attributes (%NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN) are valid attributes of an Explicit cursor.

15. Select the best answer to complete this variable declaration for a record.

DECLARE
l_foo_table SOME_TABLE_________;
BEGIN
...

%TABLE

%ROWTYPE

%COLUMNTYPE

%TYPE

None of the above

The correct answer is "%ROWTYPE". In PL/SQL, "%ROWTYPE" is used to declare a record variable that has the same structure as a table or cursor row. It allows the record variable to have the same columns and data types as the table or cursor row, making it easier to manipulate and assign values to the record variable.

Explanation

The correct answer is "%ROWTYPE". In PL/SQL, "%ROWTYPE" is used to declare a record variable that has the same structure as a table or cursor row. It allows the record variable to have the same columns and data types as the table or cursor row, making it easier to manipulate and assign values to the record variable.

16. Which of the following is not an Oracle DML function?

DECODE

TRUNCATE

TO_CHAR

NVL

Trick question, all of these are Oracle DML functions.

Do not confuse TRUNCATE with TRUNC. Truncate is used to remove all rows from an Oracle table.

Explanation

Do not confuse TRUNCATE with TRUNC. Truncate is used to remove all rows from an Oracle table.

17. Select the best answer. This is an example of what _____ type of cursor?

DECLARE
    l_date   DATE;
BEGIN
   SELECT TRUNC(SYSDATE)
       INTO l_date
     FROM DUAL;
END;

Explicit

Implicit

Select

PL/SQL

None of the above

The given code snippet does not explicitly declare a cursor using the CURSOR keyword. Instead, it uses a SELECT statement to fetch data into the variable l_date directly. This is an example of an implicit cursor, where the cursor is automatically created and managed by the Oracle database system without the need for explicit declaration and control.

Explanation

The given code snippet does not explicitly declare a cursor using the CURSOR keyword. Instead, it uses a SELECT statement to fetch data into the variable l_date directly. This is an example of an implicit cursor, where the cursor is automatically created and managed by the Oracle database system without the need for explicit declaration and control.

18. Describe the result set that will be obtained from this join

SELECT d.department_name, s.first_name, s.last_name, s.title, s.salary
   FROM employee s,
             department d
WHERE s.salary > 20000
     AND s.title = 'ANALYST'
     AND ( d.department = 'FINANCE' OR
   d.department = 'SALES' )

This SQL will list all employees in the Finance department earning more than 20000

This SQL will list all employees who are in the Finance or Sales department earning more than 20000

This SQL will list all employees who are Analysts in the Finance or Sales department earning more than 20000

This SQL will return a cartesian product

None of the above

not-available-via-ai

Explanation

not-available-via-ai

19. Select the best answer. PROCEDURE foo ( p_foo_text IN VARCHAR2, p_foo_number IN OUT NUMBER ) IS p_foo_text and p_foo_number are referred to as this procedure's _________

Variables

IN OUT Parameters

Name

Signature

None of the above

The terms "p_foo_text" and "p_foo_number" in the procedure "foo" are referred to as its signature. The signature of a procedure includes the names and data types of its parameters, which in this case are "IN VARCHAR2" and "IN OUT NUMBER".

Explanation

The terms "p_foo_text" and "p_foo_number" in the procedure "foo" are referred to as its signature. The signature of a procedure includes the names and data types of its parameters, which in this case are "IN VARCHAR2" and "IN OUT NUMBER".

20. Which of the following is not a valid Oracle PL/SQL exception.

NO_DATA_FOUND ORA-01403

TWO_MANY_ROWS ORA-01422

DUP_VAL_ON_INDEX ORA-00001

OTHERS

None of the above. These are all valid.

TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception.

Explanation

TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception.