PL/SQL Interview Questions-2

QUESTION  1
The add_player procedure inserts rows into the PLAYER table. Which command will show this directory dependency? 
A. SELECT * FROM USER_DEPENDENCIES WHERE REFERENCD NAME = ' PLAYER ' ;
B. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' ADD PLAYER ' ;
C. SELECT * FROM USER_DEPENDENCIES WHERE TYPE = 'DIR' ;
D. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' TABLE ' ; 

Answer: A 

QUESTION 2
When using a packaged function in a query, what is true? 
A. The COMMIT and ROLLBACK commands are allowed in the packaged function.
B. You can not use packaged functions in a query statement.
C. The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.
D. The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.
 E. The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used. 

Answer: C 

QUESTION 3
You have a table with the following definition:
CREATE TABLE long_tab ( id NUMBER) long_col LONG) You need to convert the LONG_COL column from a LONG data type to a LOB data type. Which statement accomplish this task? 
A. AKTER TABLE long_tab
B. EXECUTE dbms_lob.migrate(long_tab, long_col, clob)
C. EXECUTE dbms_manage.lob.migrate(long_tab, long_col, clob)
 D. EXECUTE utl_lob.migrate(long_tab, long_col, clob)
E. EXECUTE utl_manage_lob.migrate(long_tab, long_col, clob) 

Answer: A 

QUESTION 4
Why do you use an INSTEAD OF trigger? 
A. To perform clean up actions when ending a user session.
B. To insert data into a view that normally does not accept inserts.
C. To insert into an audit table when data is updated in a sensitive column.
 D. To modify data in which the DML statement has been issued against an inherently non-updateable view. 

Answer: D 


QUESTION  5
When using a PL/SQL stored package, how is a side effect defined? 
A. changes only to database tables
B. changes only to packaged public variables defined in a package body
C. changes only to packaged public variables defined in a package specification
D. changes to database tables or packaged public variables defined in a package body
E. changes to database tables or packaged variables defined in a package specification 

Answer: E 

QUESTION 6
 Examine this package
CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER:=7839 discount_rate NUMBER
      :=0.00; PROCEDURE display_price(p_price NUMBER);
      END discount;
/

      CREATE OR REPLACE PACKAGE BODY discounts
IS PROCEDURE display_price (p_price NUMBERI) IS BEGIN DBMS_OUTPUT.PUT LINE (
      'Discounted '|| TO_CHAR(p_price*NVL(discount_rate,1)));
      END discount;
      BEGIN Discount_rate=0.10;
      END discounts/
The SOL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure DISPLAY_PRICE from SOL*Plus with the command EXECUTE discount.display_price(100); What is the result? 
A. Discounted 10
B. Discounted 100
C. Discounted 0.00
D. Discounted NULL
E. Discounted 0.10 
Answer: A
 
QUESTION 7
Which two statements about functions are true? (Choose two.) 
A. A function must have a return statement in its body to execute successfully
B. Client-side functions can be used in SOL statements
C. A stored function that is called from a SOL statement can return a value of any PL/SOL variable data type
D. From SOL*Plus, a function can be executed by giving the command EXECUTE functionname; E. A stored function increases efficiency of queries by performing functions on the server rather than in the application 
Answer: A, E 

QUESTION 8
Consider this scenario A procedure X references a view Y that is based on a table Z . Which two statements are true? (Choose two.) 
A. Y is a referenced object
B. Z is a direct dependent of X
C. Y is a direct dependent of X
D. Y is an indirect dependent of X
E. Y is an indirect dependent of Z
F. Z is an indirect dependent of Y 
Answer: A, C 

QUESTION 9
 Examine this code
CREATE OR REPLACE FUNCTION change_dept (p_old_id NUMBER, p_deptname VARCHAR2)
      RETURN NUMBER IS v_new_id NUMBER
      BEGIN
      SELECT departments_seq.nextval
      INTO v_new_id
      FROM dual;
      UPDATE departments
SET departmenet_id = v_new_id, Department_name = p_deptname WHERE
      department_id=p_old_id; Return v_new_id;
      End;
/
There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables. Which statement performs a successful update to the EMPLOYEES table? 
A. UPDATE departments SET department_id = change_dept(10, 'Finance') Where dapartment_id=10;
B. UPDATE employees SET department_id = change_dept(10, 'Finance') Where dapartment_id=10;
C. UPDATE departments change_dept(270, 'Outsource') Where dapartment_name='payroll'; D. UPDATE employees SET department_id = change_dept(10, 'Finance') WHERE department_id=departments:CURRVAl: 
Answer: B 

QUESTION 10
You create a DML trigger. For the timing information, which are valid with a DML trigger? (Choose all that apply) 
A. DURING
B. IN PLACE OF
C. ON SHUTDOWN
D. BEFORE
E. ON STATEMENT EXECUTION 
Answer: D 


QUESTION 11
Which two statements about the overloading feature of packages are true? (Choose two) 
A. Only local or packaged subprograms can be overloaded.
B. Overloading allows different functions with the same name that differ only in their return types.
C. Overloading allows different subprograms with the same number, type and order of parameters.
D. Overloading allows different subprograms with the same name and same number or type of parameters.
E. Overloading allows different subprograms with the same name, but different in either number, type or order of parameters.

Answer: A, E 


QUESTION 12
All users currently have the INSERT privilege on the PLAYER table. You only want your users to insert into this table using the ADD_PLAYTER procedure. Which two actions must you take? (Choose two) 
A. CRANT SELECT ON ADD_PLAYER TO PUBLIC;
 B. CRANT EXECTUE ON ADD_PLAYER TO PUBLIC; 
C. CRANT INSERT ON PLAYER TO PUBLIC;
 D. CRANT EXECTUE INSERT ON ADD_PLAYER TO PUBLIC;
E. REVOKE INSERT ON PLAYER FROM PUBLIC; 
Answer: B, E 

QUESTION 13
When creating a function, in which section will you typically find the RETURN keyword? 
A. HEADER only
B. DECLARATIVE
C. EXECUTABLE and HEADER
D. DECLARATIVE,EXECUTABLE and EXCEPTION HANDLING 
Answer: C 


QUESTION 14
A dependent procedure or function directly or indirectly references one or more of which four objects? (Choose four) 
A. view
B. sequence
C. privilege
D. procedure
E. anonymous block
F. packaged procedure or function 
Answer: A, B, D, F 


QUESTION 15
Which three are true regarding error propagation? (Choose three) 
A. An exception cannot propagate across remote procedure calls.
B. An exception raised inside a declaration immediately propagates to the current block.
C. The use of the RAISE; statement in an exception handler reprises the current exception.
D. An exception raised inside an exception handler immediately propagates to the enclosing block. 
Answer: A, C, D 


QUESTION 16
Which two tables or views track object dependencies? (Choose two) 
A. USER_DEPENDENCIES
B. USER_IDEPTREE
C. IDEPTREE
D. USER_DEPTREE
E. USER_DEPENDS 
Answer: A, C 

QUESTION 17
Examine the trigger heading: CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF sal, job ON emp FOR EACH ROW Under which condition does this trigger fire? 
A. When a row is inserted into the EMP table.
B. When the value of the SAL or JOB column in a row is updated in the EMP table.
C. When any column other than the SAL and JOB columns in a row are updated in the EMP table.
D. Only when both values of the SAL and JOB columns in a row are updated together in the EMP table. 
Answer: B 

QUESTION 18
You have an AFTER UPDATE row-level on the table EMP. The trigger queries the EMP table and inserts the updating user's information into the AUDIT_TABLE. What happens when the user updates rows on the EMP table? 
A. A compile time error occurs.
B. A runtime error occurs. The effect of trigger body and the triggering statement are rolled back.
C. A runtime error occurs. The effect of trigger body is rolled back, but the update on the EMP table takes place.
D. The trigger fires successfully. The update on the EMP table occurs, and data is
inserted into theAUDIT_TABLE table.
E. A runtime error occurs. The update on the EMP table does not take place, but the insert into the AUDIT_TABLE occurs. 
Answer: B
 
QUESTION 19
The add_player, upd_player_stat, and upd_pitcher_stat procedures are grouped together in a package. A variable must be shared among only these procedures. Where should you declare this variable? 
A. In the package body.
B. In a database trigger.
C. In the package specification.
D. In each procedure's DECLARE section, using the exact same name in each. 
Answer: A 

QUESTION 20 
Examine this code:
CREATE OR REPLACE PACKAGE metric_converter IS
c_height CONSTRAINT NUMBER :=      2.54;
c_weight CONSTRAINT NUMBER := .454;
FUNCTION calc_height (
      p_height_in_inches NUMBER) RETURN NUMBER;
FUNCTION calc_weight (
      p_weight_in_pounds NUMBER) RETURN NUMBER;
 /
CREATE OR REPLACE PACKAGE
      BODY metric_converter IS FUNCTION calc_height (p_height_in_inches NUMBER
      ) RETURN NUMBER IS
      BEGIN RETURN p_height_in_inches * c_height;
      END calc_height;

      FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER IS
      BEGIN RETURN p_weight_in_pounds * c_weight
      END calc_weight
      END calc_weight;

/
CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER)
   RETURN NUMBER
IS
BEGIN
   RETURN p_height_in_inches * metric_converter.c_height;
END calc_height;
 /
Which statement is true? 
A. If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed.
B. If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed.
C. If you remove the package specification, then the package body is removed.
D. If you remove the package body, then the package specification is removed.
E. If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER package body and the package specification are removed.
F. The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function. 
Answer: C 


QUESTION 21
What is a condition predicate in a DML trigger? 
A. A conditional predicate allows you to specify a WHEN-LOGGING-ON condition in the trigger body.
 B. A conditional predicate means you use the NEW and OLD qualifiers in the trigger body as a condition.
 C. A conditional predicate allows you to combine several DBM triggering events into one in the trigger body.
D. A conditional predicate allows you to specify a SHUTDOWN or STARTUP condition in the trigger body. 
Answer: C 

QUESTION 22
Examine this package specification:
CREATE OR REPLACE PACKAGE concat_all
IS
   v_string   VARCHAR2 (100);

   PROCEDURE combine (p_num_val NUMBER);

   PROCEDURE combine (p_date_val DATE);

   PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
END concat_all; /

Which overloaded COMBINE procedure declaration can be added to this package specification? 
A. PROCEDURE combine;
B. PROCEDURE combine (p_no NUMBER);
C. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER;
D. PROCEDURE concat_all (p_num_val VARCHAR2, p_char_val NUMBER); 
Answer: A 


QUESTION 23
 Local procedure A calls remote procedure
B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TIMESTAMP. What happens when procedure A is invoked at 1 P.M? 
A. There is no affect on procedure A and it runs successfully.
B. Procedure B is invalidated and recompiles when invoked.
C. Procedure A is invalidated and recompiles for the first time it is invoked.
D. Procedure A is invalidated and recompiles for the second time it is invoked. 

Answer: D When the local procedure is invoked, at run time the Oracle server compares the two time stamps of the referenced remote procedure. If the time stamps are equal (indicating that the remote procedure has not recompiled), the Oracle server executes the local procedure. If the time stamps are not equal (indicating that the remote procedure has recompiled), the Oracle server invalidates the local procedure and returns a runtime error. If the local procedure, which is now tagged as invalid, is invoked a second time, the Oracle server recompiles it before executing, in accordance with the automatic local dependency mechanism. So if a local procedure returns a run-time error the first time that it is invoked, indicating that the remote procedure's time stamp has changed, you should develop a strategy to re-invoke the local procedure.
Incorrect Answers: A, B, C 


QUESTION 24
Under which two circumstances do you design database triggers? (Choose two) 
A. To duplicate the functionality of other triggers.
B. To replicate built-in constraints in the Oracle server such as primary key and foreign key.
C. To guarantee that when a specific operation is performed, related actions are performed.
 D. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement. 
Answer: C,D 


QUESTION 25 
Examine this procedure:
CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER)
IS
BEGIN
   DELETE FROM   PLAYER
         WHERE   ID = V_ID;
EXCEPTION
   WHEN STATS_EXITS_EXCEPTION
   THEN
      DBMS_OUTPUT.PUT_LINE('Cannot delete this player, child records exist in PLAYER_BAT_STAT table');
END;
What prevents this procedure from being created successfully? 
A. A comma has been left after the STATS_EXIST_EXCEPTION exception.
B. The STATS_EXIST_EXCEPTION has not been declared as a number.
C. The STATS_EXIST_EXCEPTION has not been declared as an exception.
D. Only predefined exceptions are allowed in the EXCEPTION section. 
Answer: C 


QUESTION 26
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) VALUES (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; FUNCTION calc_tax
      END
      IF; update_sal (v_raise) ;
      END update_emp ; (p_sal NUMBER) RETURN NUMBER IS
      BEGIN RETURN p_sal * tax_rate;
      END cale_tax;
END manage_emps;
/
 How many public procedures are in the MANAGE_EMPS package? 
A. One
B. Two
C. Three
D. Four
E. Five 
Answer: C 


QUESTION 27
  Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on the TEAM table? 
A. GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC;
B. GRANT SELECT,INSERT,UPDATE,DELETE ON UPD_TEAM_STAT TO
C. GRANT EXECUTE ON TEAM TO PUBLIC
D. GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC; 
Answer: A 

QUESTION 28
Examine this code:
CREATE OR REPLACE PROCEDURE set_bonus (p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY'
      p_employee_id IN employees_employee_id%TYPE p_salary IN employees_salary
      %TYPE, p_bonus_percent IN OUT NUMBER DEFAULT 1.5, p_margin OUT NUMBER
      DEFAULT 2, p_bonus_value OUT NUMBER) IS
      BEGIN
      UPDATE emp_bonus
      SET bonus_amount =(p_salary * p_bonus_percent)/p_margin
      WHERE employee_id = p_employee_id;
      END set_bonus;/
 You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two) 
A. The syntax of the UPDATE statement is incorrect.
B. You cannot update a table using a stored procedure.
C. The format parameter p_bonus_value is declared but is not used anywhere.
 D. The formal parameter p_cutoff cannot have a DEFAULT clause.
E. The declaration of the format parameter p_margin cannot have a DEFAULT clause.
F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause. 
Answer: E, F 


QUESTION 29
Which three statements are true regarding database triggers? (Choose three) 
A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
B. A database trigger needs to be executed explicitly whenever a particular event takes place.
 C. A database trigger executes implicitly whenever a particular event takes place.
D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
E. With a schema, triggers fire for each event for all users; with a database, triggers fire  for each event for that specific user. 
Answer: A, C, D 

QUESTION 30
The creation of which four database objects will cause a DDL trigger to fire?
A. Index
B. Cluster
C. Package
 D. Function
E. Synonyms
F. Dimensions
G. Database links 
Answer: A,,D,C,E 

QUESTION 31
Which two program declarations are correct for a stored program unit? (Choose two) 
A. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER
 B. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER
C. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)
D. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)
E. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))

Answer: A,C 


QUESTION 32
You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created? 
A. DML trigger
B. System event trigger
C. INSTEAD OF trigger
D. Application trigger 
Answer: B 

QUESTION 33
You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table? 
A. The trigger fires successfully.
B. The trigger fails because it needs to be a row level AFTER UPDATE trigger.
C. The trigger fails because a SELECT statement on the table being updated is not allowed.
D. The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger. 
Answer: C 

QUESTION 34 
Examine this code:
CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE)
        RETURN NUMBER IS v_salary NUMBER;
v_raise NUMBER(8,2);
   BEGIN
          SELECT salary INTO v_salary
            FROM employees
     WHERE employee_id = p_employee_id;
v_raise := p_raise_amt * v_salary;
  RETURN v_raise; END;
Which statement is true? 
A. This statement creates a stored procedure named get_sal.
B. This statement returns a raise amount based on an employee id.
C. This statement creates a stored function named get_sal with a status of invalid.
D. This statement creates a stored function named get_sal.
E. This statement fails. 
Answer: E 


QUESTION 35
 You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this?
 A.  None of these commands; you cannot disable multiple triggers on a table in one command. B. ALTER TRIGGERS ON TABLE employees DISABLE;
 C. ALTER employees DISABLE ALL TRIGGERS;
D. ALTER TABLE employees DISABLE ALL TRIGGERS; 
Answer: D 


QUESTION 36 
An internal LOB is _____. 
A. A table. B. A column that is a primary key. C. Stored in the database. D. A file stored outside of the database, with an internal pointer to it from a database column. 
Answer: C 
QUESTION 8  Examine this code:
CREATE OR REPLACE FUNCTION calc_sal (p_salary NUMBER)
   RETURN NUMBER
IS
   v_raise   NUMBER (4, 2) DEFAULT 1.08 ;
BEGIN
   RETURN v_raise * p_salary;
END calc_sal;/
Which statement accurately call the stored function CALC_SAL? (Choose two) 

A. UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary);
B. INSERT calc_sal(salary) INTO employees WHERE department_id = 60;
C. DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000;
D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60;
 E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary); 
Answer: D,E 


QUESTION 37  This statement fails when executed:
CREATE OR REPLACE TRIGGER CALC_TEAM_AVG
   AFTER INSERT
   ON PLAYER
BEGIN
   INSERT INTO PLAYER_BATSTAT (PLAYER_ID,
                               SEASON_YEAR,
                               AT_BATS,
                               HITS)
     VALUES   (:NEW.ID,
               1997,
               0,
               0);
END;To which type must you convert the trigger to correct the error? 
A. Row
B. Statement
 C. ORACLE FORM trigger
 D. Before 
Answer: A 


QUESTION 38 
Examine this code: CREATE OR REPLACE PROCEDURE audit_emp (p_id IN emp_empno%TYPE) IS v_id NUMBER;
      PROCEDURE log_exec IS
      BEGIN
      INSERT
      INTO log_table (user_id, log_delete)
      VALUES (USER, SYSDATE);
      END log_exec;

v_name VARCHAR2(20);
 BEGIN
DELETE FROM emp WHERE empno = p_id;
 log_exec;
SELECT ename, empno INTO v_name, v_id FROM emp WHERE empno = p_id; END audit_emp; Why does this code cause an error when compiled? 
A. An insert statement is not allowed in a subprogram declaration.
B. Procedure LOG_EXEC should be declared before any identifiers.
C. Variable v_name should be declared before declaring the LOG_EXEC procedure.
 D. The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure. 
Answer: C