Take This Oracle PL/SQL Test

40 Questions | Total Attempts: 4454

SettingsSettingsSettings
Take This Oracle PL/SQL Test - Quiz

Are you familiar with PL/SQL in Oracle? Take this Oracle PL/SQL test and test your knowledge. If you learned Oracle, this quiz would be an easy one for you. We have got a set of questions for your practice and knowledge-testing on PL/SQL in Oracle. Just go for it, and find out your results. Even if you are not sure about any of your answers, we will help you with the correct answer. All the best! Don't forget to share the quiz with other Oracle users.


Questions and Answers
  • 1. 
    Examine this function: CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus?
    • A. 

      SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

    • B. 

      EXECUTE CALC_PLAYER_AVG(31);

    • C. 

      CALC_PLAYER('RUTH');

    • D. 

      CALC_PLAYER_AVG(31);

    • E. 

      START CALC_PLAYER_AVG(31)

  • 2. 
    Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept;    Which three are valid calls to the add_dep procedure? (Choose three)
    • A. 

      Add_dept;

    • B. 

      Add_dept('Accounting');

    • C. 

      Add_dept(, 'New York');

    • D. 

      Add_dept(p_location=>'New York);

  • 3. 
    Examine this code: CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END bonus; /   Which statement is true?
    • A. 

      You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.

    • B. 

      You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table

    • C. 

      You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.

    • D. 

      You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table

  • 4. 
    You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?
    • A. 

      FOR EACH ROW trigger on the EMP table.

    • B. 

      Statement-level trigger on the EMP table.

    • C. 

      FOR EACH ROW trigger on the AUDIT_TABLE table

    • D. 

      Statement-level trigger on the AUDIT_TABLE table

    • E. 

      FOR EACH ROW statement-level trigger on the EMPtable.

  • 5. 
    which of the following passes a value from the procedure to the calling environment?
    • A. 

      In

    • B. 

      Out

    • C. 

      Return

    • D. 

      None of the above

  • 6. 
    create or replace trigger emp_trig after update on emp begin insert into audit values(user,sysdate); end; an update statement on emp affects 100 rows. How many rows will be inserted into audit table after this trigger fires?
    • A. 

      100

    • B. 

      1

    • C. 

      0

    • D. 

      Same no of rows as in emp table

  • 7. 
    Which determines the no of times a trigger is fired?
    • A. 

      Trigger timing

    • B. 

      Trigger event

    • C. 

      Trigger type

    • D. 

      Trigger body

  • 8. 
    While attempting to create a package specification in SQL*Plus, you receive this error: Warning: Package created with compilation errors. Which command should you execute to see a more detailed error message?
    • A. 

      Show compilation errors

    • B. 

      Display errors

    • C. 

      Show errors

    • D. 

      Show procedure errors

  • 9. 
    How will u delete a trigger emp_trig permanently?
    • A. 

      Drop trigger emp_trig;

    • B. 

      Delete trigger emp_trig;

    • C. 

      Delete from user_triggers where trigger_name=’emp_trig’;

    • D. 

      Alter trigger emp_trig disable

  • 10. 
    In a pl/sql loop,you need to find whether a sql statement has  fetched rows successfully.Which cursor attibute will you use?
    • A. 

      Sql%isopen

    • B. 

      Sql%rowcount

    • C. 

      Sql%found

    • D. 

      You cannot use cursor attributes for pl/sql loops

  • 11. 
    What are the  common oracle predefined exceptions that occur while executing select statements?
    • A. 

      No_data_found

    • B. 

      Too_many_rows

    • C. 

      Zero_divide

    • D. 

      Invalid_cursor

  • 12. 
    Begin       ------      exception        when no_data_found           ----------        when value_error            ---------        when others            -----------      end;      if you encounter an error while dividing a number by zero, which exception handler will be executed?
    • A. 

      Value_error

    • B. 

      No_data_found

    • C. 

      Others

    • D. 

      None of the above

  • 13. 
    What type of cursor will u use when you use the cursor many times in the PL/SQL block with some different datas to be fetched depending upon differrent values
    • A. 

      Cursors with parameters

    • B. 

      Use explicit cursor so many time in the PL/SQL block

    • C. 

      Cursors with attributes

    • D. 

      Any of the above

  • 14. 
    whT does the %rowcount return when no rows are returned by a select statement?
    • A. 

      0

    • B. 

      Undefined

    • C. 

      NULL

    • D. 

      None of the above

  • 15. 
    What will be the value of a uninitialised but declared variable when it is first used in the executable section
    • A. 

      0

    • B. 

      NULL

    • C. 

      Show error

    • D. 

      None of the above

  • 16. 
    which clause has to be used in the explicit cursor to lock the rows first?
    • A. 

      Where current of

    • B. 

      FOR UPDATE OF

    • C. 

      Any cursor Attribute

    • D. 

      Any Cursor Variable

  • 17. 
    .which clause has to be used to reference the current row from an explicit cursor
    • A. 

      FOR UPDATE OF

    • B. 

      Where current of

    • C. 

      Any cursor Attribute

    • D. 

      Any Cursor Variable

  • 18. 
    . BEGIN FOR I in 1..10 LOOP If I=2 or I=4 Null; ELSE INSERT into easn(record) values (I); End if; Commit; END LOOP; ROLLBACK: END: How many values will have been inserted into easn table?
    • A. 

      0

    • B. 

      4

    • C. 

      8

    • D. 

      10

  • 19. 
    DECLARE             V_NAME CONSTANT NUMBER(9);         What is the error with the above statment ?
    • A. 

      No error

    • B. 

      Should initialize with value.

    • C. 

      Should not mention width for data type.

    • D. 

      Syntax error that constant should be after data type

  • 20. 
    Declare           V_lower := 4;           V_upper := 4;        Begin             for i in V_lower .. V_upper loop                     ---------            End loop; end;    How many times does the loop execute?
    • A. 

      0

    • B. 

      1

    • C. 

      4

    • D. 

      5

  • 21. 
    FOR I IN 1..6        LOOP INSERT INTO val_table(vcol)  VALUES(I); COMMIT; END LOOP; ROLLBACK; How many rows will be inserted after the execution of above PL/SQL?
    • A. 

      0

    • B. 

      6

    • C. 

      3

    • D. 

      10

  • 22. 
    IF in_val > 2000 THEN out_val = in_val + 1;       ELSIF in_val > 3000 THEN          out_val = in_val + 2;       ELSIF in_val > 4000 THEN out_val = in_val + 3; ELSE out_val = in_val + 4; END IF;     What is the out_val value, if the in_val is 2500?
    • A. 

      2501

    • B. 

      2502

    • C. 

      2504

    • D. 

      NULL

  • 23. 
    . What is the value of v_price if the following block is executed with v_value=250 declare v_value number(6,2); v_price number(6,2); begin if v_value>150 then v_price=2500; elsif v_value>200 then v_price=3500; elsif v_value>300 then v_price=4500; else v_price=null; end if;
    • A. 

      Null

    • B. 

      4500

    • C. 

      3500

    • D. 

      2500

  • 24. 
    In which situation you use make use of explicit cursor?
    • A. 

      When the select returns zero rows

    • B. 

      When the select returns one row

    • C. 

      When the select returns more than one row

    • D. 

      When DML operations are performed

  • 25. 
    You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create?
    • A. 

      A cursor FOR loop.

    • B. 

      A multiple selection cursor.

    • C. 

      A cursor for each active set.

    • D. 

      A cursor that uses parameters

Back to Top Back to top
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.