PL/SQL Interview Questions-3

QUESTION 1
Examine this code:
CREATE OR REPLACE PACKAGE comm_package
IS
   g_comm   NUMBER := 10;

   PROCEDURE reset_comm (p_comm IN NUMBER);
END comm_package;
/
User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?
A. g_comm has a value of 15 at 9:06am for Smith.
B. g_comm has a value of 15 at 9:06am for Jones.
C. g_comm has a value of 20 at 9:06am for both Jones and Smith.
D. g_comm has a value of 15 at 9:03 am for both Jones and Smith.
E. g_comm has a value of 10 at 9:06am for both Jones and Smith.
F. g_comm has a value of 10 at 9:03am for both Jones and Smith 
Answer: B 


QUESTION 2 
Examine this code:
 CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name
      VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS
      v_email_name VARCHAR2(19)
      BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name,1
      ,7) || '@Oracle.com';
      UPDATE employees
      SET email = v_email_name
      WHERE employee_id = p_id; RETURN v_email_name;
      END;
/
Which statement removes the function? 
A. DROP FUNCTION gen_email_name;
B. REMOVE gen_email_name;
C. DELETE gen_email_name;
D.Truncate gen_email _name;
E .DROP FUNCTION gen_email_name;
F.ALTER FUNCTION gen_email_name;REMOVE 
Answer: E 


QUESTION 3
Examine this procedure:
CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID   IN NUMBER DEFAULT 10 ,
                                          V_AB   IN NUMBER DEFAULT 4 )
IS
BEGIN
   UPDATE   PLAYER_BAT_STAT
      SET   AT_BATS = AT_BATS + V_AB
    WHERE   PLAYER_ID = V_ID;

   COMMIT;
END;
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two) 
A. EXECUTE UPD_BAT_STAT;
B. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);
C. EXECUTE UPD_BAT_STAT(31, 'FOUR','TWO');
D. UPD_BAT_STAT(V_AB=>10, V_ID=>31);
 E. RUN UPD_BAT_STAT; 

Answer: A,B 


QUESTION 4 
Examine this code:
 CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGININSERT INTO
      audit(schema_user)
      VALUES(p_who);
      END audit_action;; /
CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do? 
A. The trigger records an audit trail when a user makes changes to the database.
B. The trigger marks the user as logged on to the database before an audit statement is issued. C. The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.
D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table. 
Answer: D 


QUESTION 5
 Which view displays indirect dependencies, indenting each dependency? 
A. DEPTREE
B. IDEPTREE
C. INDENT_TREE
D. I_DEPT_TREE
Answer: B 


QUESTION 6
The OLD and NEW qualifiers can be used in which type of trigger? 
A. Row level DML trigger
 B. Row level system trigger
C. Statement level DML trigger
D. Row level application trigger
E. Statement level system trigger
F. Statement level application trigger 

Answer: A 


QUESTION 7
 Which statement is true? 
A. Stored functions can be called from the SELECT and WHERE clauses only.
B. Stored functions do not permit calculations that involve database links in a distributed environment.
C. Stored functions cannot manipulate new types of data, such as longitude and latitude.
 D. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application. 

Answer: D 

QUESTION 8
Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW
      DELCARE n INTEGER;
BEGIN
   SELECT   COUNT ( * ) INTO n FROM Emp_tab;

   DBMS_OUTPUT.PUT_LINE (' There are now ' || a || ' employees,');
END;
This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error? 

A. Change the trigger type to a BEFORE DELETE.
B. Take out the COUNT function because it is not allowed in a trigger.
C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
D. Change the trigger to a statement-level trigger by removing FOR EACH ROW. 
Answer: D 


QUESTION 9
What is true about stored procedures? 
A. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.
 B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.
C. A stored procedure must have at least one executable statement in the procedure body.
D. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters. 
Answer: C 

QUESTION 10
 Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id
      NUMBER(4);
      BEGIN
      INSERT
      INTO departments
      VALUES (5, 'Education', 150, p_location_id)
      SELECT department_id
      INTO v_dept_id
      FROM employees
      WHERE employee_id=99999;
      END insert_dept;
/
You just created the departments, the locations, and the employees table. You did
- The Power of Knowing 
not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, 'San Francisco') What is the result in thisEXECUTE command? 
A. The locations, departments, and employees tables are empty.
 B. The departments table has one row. The locations and the employees tables are empty.
C. The location table has one row. The departments and the employees tables are empty.
 D. The locations table and the departments table both have one row. The employees table is empty. 
Answer: A 

QUESTION 11 
How can you migrate from a LONG to a LOB data type for a column? 
A. Use the DBMS_MANAGE_LOB.MIGRATE procedure.
B. Use the UTL_MANAGE_LOB.MIGRATE procedure.
C. Use the DBMS_LOB.MIGRATE procedure.
D. Use the ALTER TABLE command.
E. You cannot migrate from a LONG to a LOB date type for a column. 
Answer: D 


QUESTION 12
Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2
      DEFAULT 'AUSTIN', V_NAME in VARCHAR2) IS
      BEGIN
      INSERT
      INTO TEAM (id, city, name)
      VALUES (v_id, v_city, v_name);
      COMMIT;
      END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two) 
A. EXECUTE INSERT_TEAM;
B. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', , V_CITY=>'AUSTIN');
C.EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
D. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
E. EXECUTE INSERT_TEAM (3, 'LONGHORNS'); 
Answer: B,C 


QUESTION 13
To be callable from a SQL expression, a user-defined function must do what? 
A. Be stored only in the database.
B. Have both IN and OUT parameters.
C. Use the positional notation for parameters.
D. Return a BOOLEAN or VARCHAR2 data type. 
Answer: A 

QUESTION  14
Which two describe a stored procedure? (Choose two) 
A. A stored procedure is typically written in SQL.
B. A stored procedure is a named PL/SQL block that can accept parameters.
C. A stored procedure is a type of PL/SQL subprogram that performs an action.
D. A stored procedure has three parts: the specification, the body, and the exception handler part.
E. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment. 

Answer: B,C A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has a header, a declaration section, an executable section, and an optional exception-handling section. A procedure can be compiled and stored in the database as a schema object. Procedures promote reusability and maintainability. When validated, they can be used in any number of applications. If the requirements change, only the procedure needs to be updated. 


QUESTION 15 
Examine this code:
CREATE OR REPLACE PROCEDURE add_dept ( p_name departments.department_name%TYPE
      DEFAULT 'unknown', p_loc departments.location_id%TYPE DEFAULT 1700) IS
      BEGIN
      INSERT
      INTO departments(department_id, department_name, loclation_id)
      VALUES(dept_seq.NEXTVAL,p_name, p_loc);
      END add_dept;/
You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus. Which four are valid invocations? (Choose four) 
A. EXECUTE add_dept(p_loc=>2500)
B. EXECUTE add_dept('Education', 2500)
C. EXECUTE add_dept('2500', p_loc =>2500)
D. EXECUTE add_dept(p_name=>'Education', 2500)
E. EXECUTE add_dept(p_loc=>2500, p_name=>'Education') 
Answer: A,B,C,E 

QUESTION 16Which three are valid ways to minimize dependency failure? (Choose three) 
A. Querying with the SELECT * notification.
B. Declaring variables with the %TYPE attribute.
C. Specifying schema names when referencing objects.
D. Declaring records by using the %ROWTYPE attribute.
E. Specifying package.procedure notation while executing procedures. 
Answer: A,B,D 


QUESTION 17
Which two dopes the INSTEAD OF clause in a trigger identify? (Choose two) 
A. The view associated with the trigger.
B. The table associated with the trigger.
 C. The event associated with the trigger.
D. The package associated with the trigger.
 E. The statement level or for each row association to the trigger. 
Answer: A,C 


QUESTION 18
Examine this package:
CREATE OR REPLACE PACKAGE manage_emps
IS
   tax_rate CONSTANT   NUMBER (5, 2) := .28;
   v_id                NUMBER;

   PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);

   PROCEDURE delete_emp;

   PROCEDURE update_emp;

   FUNCTION calc_tax (p_sal NUMBER)
      RETURN NUMBER;
END manage_emps;/
CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (
      p_raise_amt NUMBER) IS
      BEGIN
      UPDATE emp
      SET sal = (sal * p_raise_emt) + sal
      WHERE empno = v_id;
      END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS
      BEGIN
      INSERT
      INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal);
      END insert_emp; PROCEDURE delete_emp IS
      BEGIN
      DELETE
      FROM emp
      WHERE empno = v_id;
      END delete_emp; PROCEDURE update_emp
IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal
      FROM emp
      WHERE empno = v_id;
      IF v_sal < 500
      THEN v_raise := .05; ELSIP v_sal < 1000
      THEN v_raise := .07;
      ELSE v_raise := .04;
      END
      IF; update_sal(v_raise);
      END update_emp;
FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal *
      tax_rate;
      END calc_tax;
      END manage_emps;; /
What is the name of the private procedure in this package? 
A. CALC_TAX
B. INSERT_EMP
C. UPDATE_SAL
D. DELETE_EMP
E. UPDATE_EMP
F. MANAGE_EMPS 
Answer: C 

QUESTION 19
  What can you do with the DBMS_LOB package? 
A. Use the DBMS_LOB.WRITE procedure to write data to a BFILE.
B. Use the DBMS_LOB.BFILENAME function to locate an external BFILE.
C. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.
D. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed. 

Answer: D 

QUESTION 20
 Examine this package:
 CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE
      ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, , V_SALARY NUMBER);
      END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK IS PROCEDURE UPD_PLAYER_STAT V_ID IN
      NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS
      BEGIN
      UPDATE PLAYER_BAT_STAT
      SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS
      WHERE PLAYER_ID = V_ID;
      COMMIT; VALIDATE_PLAYER_STAT(V_ID);
      END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME
      VARCHAR2, V_SALARY NUMBER) IS
      BEGIN
      INSERT
      INTO PLAYER(ID,LAST_NAME,SALARY)
      VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0);
      END ADD_PLAYER;
      END BB_PACK /
Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package? 
A. V_PLAYER_AVG := .333;
 B. BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;
C. BB_PACK.V_PLAYER_AVG := .333;
D. This variable cannot be assigned a value from outside of the package. 
Answer: D