Oracle PL/SQL Quiz: Trivia Exam!

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Asgari.sematec
A
Asgari.sematec
Community Contributor
Quizzes Created: 8 | Total Attempts: 10,421
| Attempts: 1,388
SettingsSettings
Please wait...
  • 1/20 Questions

    Select invalid variable types.

    • CHAR
    • VARCHAR1
    • VARCHAR2
    • INTEGER
    • NUMBER
Please wait...
About This Quiz

This Oracle PL\/SQL Trivia Exam tests your knowledge on variable declarations, cursor operations, procedure signatures, and package specifications. It's ideal for learners looking to validate their PL\/SQL skills and understanding of database programming concepts.

Oracle PL/SQL Quiz: Trivia Exam! - Quiz

Quiz Preview

  • 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

    Correct Answer
    A. 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.

    Rate this question:

  • 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

    Correct Answer
    A. In the PL/SQL declaration section
    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.

    Rate this question:

  • 4. 

    The || is is an example of what functionSELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;

    • Incantination

    • Integration

    • Continuation

    • Concatenation

    • None of the above

    Correct Answer
    A. 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".

    Rate this question:

  • 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

    Correct Answer
    A. OPEN, FETCH, CLOSE
    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.

    Rate this question:

  • 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';

    Correct Answer
    A. Foo_text varchar2(10) := 'hello world';
    Explanation
    The length of string 'hello world' (11) exceeds declared variable length of 10.

    Rate this question:

  • 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.

    Correct Answer
    A. SHOW ERRORS;
    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.

    Rate this question:

  • 8. 

    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.

    Correct Answer
    A. Package specification is valid. This is an example of overloading.
    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.

    Rate this question:

  • 9. 

    Select the best answer describing the maximum number of times the COMMIT will be executed.ExampleFOR 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.

    Correct Answer
    A. The commit is fired 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.

    Rate this question:

  • 10. 

    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.

    Correct Answer
    A. %TYPE
    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.

    Rate this question:

  • 11. 

    Enter the missing keywordIF 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

    Correct Answer
    A. ELSIF
    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.

    Rate this question:

  • 12. 

    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

    Correct Answer
    A. Specification and body
    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.

    Rate this question:

  • 13. 

    Assuming the date and time is 09/09/2009 09:09:09, what value will the following statement returnSELECT 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

    Correct Answer
    A. 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'.

    Rate this question:

  • 14. 

    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

    Correct Answer
    A. %ROWTYPE
    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.

    Rate this question:

  • 15. 

    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.

    Correct Answer
    A. None of the above. All of these are valid.
    Explanation
    All of the listed attributes (%NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN) are valid attributes of an Explicit cursor.

    Rate this question:

  • 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.

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

    Rate this question:

  • 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

    Correct Answer
    A. Implicit
    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.

    Rate this question:

  • 18. 

    Describe the result set that will be obtained from this joinSELECT d.department_name, s.first_name, s.last_name, s.title, s.salary   FROM employee s,             department dWHERE 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

    Correct Answer
    A. This SQL will return a cartesian product
  • 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

    Correct Answer
    A. Signature
    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".

    Rate this question:

  • 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.

    Correct Answer
    A. TWO_MANY_ROWS ORA-01422
    Explanation
    TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception.

    Rate this question:

Quiz Review Timeline (Updated): Mar 22, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 22, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • May 10, 2017
    Quiz Created by
    Asgari.sematec
Back to Top Back to top
Advertisement