EASY ORACLE PLSQL QUIZ

31 Questions
Oracle Quizzes & Trivia

Oracle PL/SQL quiz

Please wait...
Questions and Answers
  • 1. 
    Select incorrect variable declarations
    • A. 

      Foo_number varchar2(10);

    • B. 

      Foo_text number(10);

    • C. 

      Foo_char char(1) := 'Y';

    • D. 

      Foo_time date;

    • E. 

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

  • 2. 
    Supply the missing keywordDECLARE     CURSOR c1 IS          SELECT * FROM DUAL;     r1   c1%ROWTYPE;BEGIN      OPEN c1:            ______________ c1 INTO r1;                       IF c1%NOTFOUND THEN                 NULL;            END IF;      CLOSE c1;END;
  • 3. 
    Select invalid variable types
    • A. 

      CHAR

    • B. 

      VARCHAR1

    • C. 

      VARCHAR2

    • D. 

      INTEGER

    • E. 

      NUMBER

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

      INITIALIZE, GET, CLOSE

    • B. 

      CURSOR, GET, FETCH, CLOSE

    • C. 

      OPEN, FETCH, CLOSE

    • D. 

      CURSOR, FETCH, CLOSE

    • E. 

      GET, SEEK, HIDE

  • 5. 
    Supply the missing keywordDECLARE     CURSOR c1 IS          SELECT * FROM DUAL;     r1   c1%ROWTYPE;BEGIN      OPEN c1:            FETCH c1 INTO r1;                       IF c1%NOTFOUND THEN                 RAISE;            END IF;      ________________ c1;END;
  • 6. 
    Select the best answerPROCEDURE foo ( p_foo_text  IN VARCHAR2,  p_foo_number IN OUT NUMBER ) ISp_foo_text and p_foo_number are referred to as this procedure's _________
    • A. 

      Variables

    • B. 

      IN OUT Parameters

    • C. 

      Name

    • D. 

      Signature

    • E. 

      None of the above

  • 7. 
    • A. 

      Package specification is invalid. Too many procedures named foo.

    • B. 

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

    • C. 

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

    • D. 

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

    • E. 

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

  • 8. 
    Select the best answer to complete this variable declaration for a column value.DECLARE   l_foo_column_id        SOME_TABLE.SOME_COLUMN_________;BEGIN...
    • A. 

      %ID

    • B. 

      %ROWTYPE

    • C. 

      %COLUMNTYPE

    • D. 

      %TYPE

    • E. 

      None of the above.

  • 9. 
    Select the best answer to complete this variable declaration for a record.DECLARE   l_foo_table        SOME_TABLE_________;BEGIN...
    • A. 

      %TABLE

    • B. 

      %ROWTYPE

    • C. 

      %COLUMNTYPE

    • D. 

      %TYPE

    • E. 

      None of the above

  • 10. 
    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;
    • A. 

      Explicit

    • B. 

      Implicit

    • C. 

      Select

    • D. 

      PL/SQL

    • E. 

      None of the above

  • 11. 
    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;
    • A. 

      Explicit

    • B. 

      Implicit

    • C. 

      Select

    • D. 

      PL/SQL

    • E. 

      None of the above

  • 12. 
    Supply the missing key wordIF foo IS NULL _________   NULL;END IF;
  • 13. 
    Enter the missing keywordIF foo = 1 THEN    l_text := 'A';______  foo = 2 THEN   l_text := 'B';ELSE    l_text := 'C';END IF;
    • A. 

      ELSE

    • B. 

      ELSE IF

    • C. 

      ELIF

    • D. 

      ELSIF

    • E. 

      None of the above

  • 14. 
    Fill in the missing keyword.DECLARE    l_date    DATE;BEGIN  SELECT sysdate     ______ l_date     FROM dual;END;
  • 15. 
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

      This SQL will return a cartesian product

    • E. 

      None of the above

  • 16. 
    Where do you declare an explicit cursor in the PL/SQL language?
    • A. 

      In the PL/SQL working storage section

    • B. 

      In the PL/SQL declaration section

    • C. 

      In the PL/SQL body section

    • D. 

      In the PL/SQL exception section

    • E. 

      None of the above

  • 17. 
    Enter the missing keyword to make a new stored procedure.____________ PROCEDURE foo( p_foo_text  IN VARCHAR2 ) ASBEGIN    NULL;END;
  • 18. 
    • A. 

      The commit is fired 1000 times

    • B. 

      The commit is fired 100 times

    • C. 

      The commit is fired 10 times

    • D. 

      The commit is fired 1 time

    • E. 

      None of the above.

  • 19. 
    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;
    • A. 

      09/09/2009 09:09:09

    • B. 

      09/09/2009 09:09:09AM

    • C. 

      09/09/2009

    • D. 

      09/09/2009 00:00:00

    • E. 

      None of the above

  • 20. 
    The || is is an example of what functionSELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;
    • A. 

      Incantination

    • B. 

      Integration

    • C. 

      Continuation

    • D. 

      Concatenation

    • E. 

      None of the above

  • 21. 
    Which of the following is not an Oracle DML function?
    • A. 

      DECODE

    • B. 

      TRUNCATE

    • C. 

      TO_CHAR

    • D. 

      NVL

    • E. 

      Trick question, all of these are Oracle DML functions.

  • 22. 
    Fill in the blank with the name of the function to convert a date to an alphanumeric stringSELECT _______(sysdate, 'MM/DD/YYYY') FROM dual;
  • 23. 
    Select the invalid PL/SQL looping construct.
    • A. 

      WHILE LOOP ... END LOOP;

    • B. 

      FOR rec IN some_cursor LOOP ... END LOOP;

    • C. 

      LOOP ... UNTIL ; END LOOP;

    • D. 

      LOOP ... EXIT WHEN ; END LOOP;

    • E. 

      None of the above. All are valid.

  • 24. 
    Select the best answer.  Which listed attribute is an invalid attribute of an Explicit cursor.
    • A. 

      %NOTFOUND

    • B. 

      %FOUND

    • C. 

      %ROWCOUNT

    • D. 

      %ISOPEN

    • E. 

      None of the above. All of these are valid.

  • 25. 
    Which of the following is not a grouping function.
    • A. 

      COUNT

    • B. 

      SUM

    • C. 

      DISTINCT

    • D. 

      MIN

    • E. 

      All of the above.

  • 26. 
  • 27. 
    • A. 

      NO_DATA_FOUND ORA-01403

    • B. 

      TWO_MANY_ROWS ORA-01422

    • C. 

      DUP_VAL_ON_INDEX ORA-00001

    • D. 

      OTHERS

    • E. 

      None of the above. These are all valid.

  • 28. 
    • A. 

      1

    • B. 

      2

    • C. 

      3

    • D. 

      0

    • E. 

      None of the above.

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

      SHOW MISTAKES;

    • B. 

      DISPLAY MISTAKES;

    • C. 

      DISPLAY ERRORS;

    • D. 

      SHOW ERRORS;

    • E. 

      None of the above.

  • 30. 
    Select the best answer below.  What are the components of a package?
    • A. 

      Box, wrapping and binding

    • B. 

      Header and body

    • C. 

      Specification and content

    • D. 

      Specification and body

    • E. 

      None of the above

  • 31. 
    Write a function named date_as_mmyyyy which will accept a date as a parameter.  This date is then converted to characters and formatted as MMYYYY and then returned.   If this date is null then return sysdate in this format.