Oracle9i program with pl/sql Sample Questions:

1. Which two tables or views track object dependencies? (Choose two)

A) USER_IDEPTREE
B) USER_DEPENDS
C) IDEPTREE
D) USER_DEPTREE
E) USER_DEPENDENCIES

2. Examine this code:
CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGIN INSERT 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 invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.
B) The trigger records an audit trail when a user makes changes to the database.
C) The trigger marks the user as logged on to the database before an audit statement is issued.
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.

3. 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, p_name=>'Education')
B) EXECUTE add_dept('2500', p_loc =>2500)
C) EXECUTE add_dept('Education', 2500)
D) EXECUTE add_dept(p_loc=>2500)
E) EXECUTE add_dept(p_name=>'Education', 2500)

4. 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;
/
How many public procedures are in the MANAGE_EMPS package?

A) Two
B) One
C) Three
D) Four
E) Five

5. 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) UPD_BAT_STAT(V_AB=>10, V_ID=>31);
C) RUN UPD_BAT_STAT;
D) EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);
E) EXECUTE UPD_BAT_STAT(31, 'FOUR','TWO');

Solutions: