PL/SQL Grand Test

42 Questions | Total Attempts: 3724

SettingsSettingsSettings
Please wait...
PL/SQL Grand Test

This is a sample PL/SQL test


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

  • 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

  • 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

  • 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

  • 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

  • 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