Easy Oracle Plsql Quiz

31 Questions  I  By Ibecruzin
Please take the quiz to rate it.

Oracle Quizzes & Trivia
Oracle PL/SQL quiz

  
Changes are done, please start the quiz.


Questions and Answers

Removing question excerpt is a premium feature

Upgrade and get a lot more done!
  • 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. 
    Select the best answerPACKAGE foo_foo ISPROCEDURE foo( p_foo_text IN VARCHAR2 );PROCEDURE foo(p_foo_number IN NUMBER);END;
    • 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. 
    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' )              
    • 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. 
    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;
    • 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. 
    All the blanks can be filled with one word.  Fill in the blank Starting in Oracle 9i, you can use the _________ statement within a SQL statement. It has the functionality of an IF-THEN-ELSE statement. The syntax for the ________ statement is: __________  [ expression ]  WHEN condition_1 THEN result_1  WHEN condition_2 THEN result_2  ...  WHEN condition_n THEN result_n  ELSE resultEND

  • 27. 
    Which of the following is not a valid Oracle PL/SQL exception.
    • 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. 
    What is the value of l_child_number?DECLARE   l_parent_number    NUMBER  := 1;BEGIN   DECLARE      l_child_number   NUMBER := 2;      BEGIN       l_child_number := l_parent_number + l_child_number;   END;     DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number));EXCEPTION   WHEN OTHERS THEN      l_child_number := 0;      DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number);END;
    • 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.

Back to top

Removing ad is a premium feature

Upgrade and get a lot more done!
Take Another Quiz
We have sent an email with your new password.