PL/SQL Interview Questions-1

QUESTION 1
Examine this package:

CREATE OR REPLACE PACKAGE pack_cur
IS
   CURSOR c1
   IS
        SELECT   prodid
          FROM   product
      ORDER BY   Prodid DESC;

   PROCEDURE Proc1;

   PROCEDURE Proc2;
END pack_cur;
/

      CREATE OR REPLACE PACKAGE BODY pack_cur IS v_prodif NUMBER; PROCEDURE
      proc1 IS
      BEGIN OPEN C1;
      LOOP FETCH c1
      INTO V_prodid; DBMS_OUTPUT.PUT_LINE('Row is ;'||C1% ROWCOUNT); EXIT WHEN
      C1% ROWCOUNT>=3;
      END
      LOOP;
      END PROC1; PROCEDURE proc2 IS
      BEGIN
      LOOP FETCH C1
      INTO v_prodid; DBMS_OUTPUT-PUT_LINE ( ' Row is: ' ll c1 %ROWCOUNT); EXIT
      WHEN C1%ROWCOUNT >= 3;
      END
      LOOP;
      END Procl;

The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL *Plus with the command: EXECUTE pack_cur. PROC1; You then execute the procedure PROC2 from SQL *Plus with the command: EXECUTE pack_cur. PROC2; What is the output in your session from the PROC2 procedure? 
A. ERROR at line 1:
 B. Row is: Row is: Rows is:
C. Row is: 1 Row is: 2 Row is: 3
D. Row is: 4 Row is: 5 Row is: 6 
Answer: D 


QUESTION 2
You disabled all triggers on the EMPLOYEES table to perform a data load. Now, you need to enable all triggers on the EMPLOYEES table. Which command accomplished this? 
A. You cannot enable multiple triggers on a table in one command.
B. ALTER TRIGGERS ON TABLE employees ENABLE;
C. ALTER employees ENABLE ALL TRIGGERS;
D. ALTER TABLE employees ENABLE ALL TRIGGERS; 
Answer:  

QUESTION 3
 When creating stored procedures and functions, which construct allows you to transfer values to and from the calling environment? 
A. local variables
B. arguments
C. Boolean variables
D. Substitution variables 
Answer: B 


QUESTION 4
 Which code successfully calculates tax? 
A. CREATE OR REPLACE PROCEDURE calctax (p_no IN NUMBER) RETURN tax IS v_sal
      NUMBER;  tax Number;
      BEGIN
      SELECT sal
      INTO v_sal
      FROM emp
      WHERE empno = p_no; tax := v_sal * 0.05;
      END;
B. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) RETURN NUMBER IS v_sal NUMBER
      BEGIN
      SELECT sal
      INTO v_sal
      FROM emp
      WHERE empno = p_no; RETURN:= v_sal * 0.05;
      end
C. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) RETURN NUMBER IS v_sal NUMBER
      ; tax NUMBER;
      BEGIN
      SELECT sal
      INTO v_sal
      FROM emp
      WHERE empno = p_no; tax := v_sal * 0.05;
      END

D. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER;
      BEGIN
      SELECT sal
      INTO v_sal
      FROM emp
      WHERE empno = p_no; tax := v_sal * 0.05; return (tax);
      END;   

Answer: B 

QUESTION 5
 Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name (p_first VARCHAR2, p_last VARCHAR2)
      RETURN VARCHAR2 IS v_email_name VARCHAR (19) ;
      BEGIN v_email_bame := SUBSTR(p_first, 1, 1) || SUBSRE(p_last, 1, 7) ||
      RETURN v_email_name;
      END/
 Which two statements are true? 
A. This function is invalid.
B. This function can be used against any table.
C. This function cannot be used in a SELECT statement.
D. This function can be used only if the two parameters passed in are not bull values.
 E. This function will generate a string based on 2 character values passed into the function.
 F. This function can be used only on tables where there is a p_first and p_last column. 
Answer: E 


QUESTION 6
Examine the code examples. Which one is correct? 
A. CREATE OR REPLACE TRIGGER authorize_action BEFORE INSERT ON EMPLOYEES CALL log_exectution;  /
B. CREATE OR REPLACE TRIGGER authorize_action BEFORE EMPLOYEES INSERT CALL log_exectution;
 C. CREATE OR REPLACE TRIGGER authorize_action BEFORE EMPLOYEES INSERT CALL log_exectution; 
D. CREATE OR REPLACE TRIGGER authorize_action CALL log_exectution; BEFORE INSERT ON EMPLOYEES; / 
Answer: B 


QUESTION  7 
You need to create a DML trigger. Which five pieces need to be identified? (Choose five) 
A. Table
 B. DML event
 C. Trigger body
 D. Package body
 E. Package name
F. Trigger name
 G. System event
H. Trigger timing 
Answer: A, B, D, F, H 

QUESTION  8
Procedure PROCESS_EMP references the table EMP. Procedure UPDATE_EMP updates rows if table EMP through procedure PROCESS_EMP.
There is a remote procedure QUERY_EMP that queries the EMP table through the local procedure PROCESS_EMP.
The dependency mode is set to TIMESTAMP in this session. Which two statements are true? (Choose two) 
A. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated.
 B. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.
C. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.
D. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time.
E. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time. 
Answer: B, E