Oracle9i program with pl/sql Sample Questions:

1. 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) DELETE FROM employees(calc_sal(salary))
WHERE calc_sal(salary) > 1000;
B) SELECT last_name, salary, calc_sal(salary)
FROM employees ORDER BY
calc_sal(salary);
C) UPDATE employees (calc_sal(salary))
SET salary = salary * calc_sal(salary);
D) INSERT calc_sal(salary) INTO employees
WHERE department_id = 60;
E) SELECT salary, calc_sal(salary)
FROM employees
WHERE department_id = 60;

2. Which two does the INSTEAD OF clause in a trigger identify? (Choose two)

A) The table associated with the trigger.
B) The statement level or for each row association to the trigger.
C) The package associated with the trigger.
D) The view associated with the trigger.
E) The event associated with the trigger.

3. 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.

4. 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;
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 $75,000,000 to the V_MAX_TEAM_SALARY variable
from within a stand-alone procedure?

A) V_MAX_TEAM_SALARY := 7500000;
B) This variable cannot be assigned a value from outside the package.
C) BB_PACK.V_MAX_TEAM_SALARY := 75000000;
D) BB_PACK.ADD_PLAYER.V_MAX_TEAM_SALARY := 75000000;

5. Which two dictionary views track dependencies? (Choose two)

A) UTL_DEPTREE
B) DEPTREE_TEMPTAB
C) DBA_DEPENDENT_OBJECTS
D) USER_OBJECTS
E) USER_SOURCE
F) USER_DEPENDENCIES

Solutions: