PL \ SQL Dumbs part 1

1. examine this function:

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG

 (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE)

  RETURN NUMBER IS V_AVG NUMBER;

BEGIN

  SELECT HITS / AT_BATS INTO V_AVG

  FROM PLAYER_BAT_STAT

  WHERE PLAYER_ID = V_ID;

  RETURN (V_AVG);

 END;

Which statement will successfully invoke this function in SQL *Plus?

A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

B. EXECUTE CALC_PLAYER_AVG(31);

C. CALC_PLAYER('RUTH');

D. CALC_PLAYER_AVG(31);

E. START CALC_PLAYER_AVG(31)

2. Which two statements about packages are true? (Choose two)

A. Packages can be nested.

B. You can pass parameters to packages.

C. A package is loaded into memory each time it is invoked.

D. The contents of packages can be shared by many applications.

E. You can achieve information hiding by making package constructs private.

3. CREATE OR REPLACE PACKAGE bonus

 IS

g_max_bonus NUMBER := .99;
FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER;

FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY bonus

IS

v_salary employees.salary%TYPE;

v_bonus employees.commission_pct%TYPE;

FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER

IS

BEGIN

SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id;

RETURN v_bonus * v_salary;

END calc_bonus ;

FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER

IS

BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees;
 RETURN v_bonus * v_salary + v_salary;

END cacl_salary;

END bonus;

Which statement is true?

A. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.

B. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.

C. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.

D. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.

4. CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG

(V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE)

RETURN NUMBER

IS

V_AVG NUMBER;

BEGIN

SELECT HITS / AT_BATS

INTO V_AVG

FROM PLAYER_BAT_STAT

WHERE PLAYER_ID = V_ID;

RETURN (V_AVG);

END;

Which statement will successfully invoke this function in SQL *Plus?

A. SELECT CALC_PLAYER_AVG(PLAYER_ID)

FROM PLAYER_BAT_STAT;

B. EXECUTE CALC_PLAYER_AVG(31);

C. CALC_PLAYER(‘RUTH’);

D. CALC_PLAYER_AVG(31);

E. START CALC_PLAYER_AVG(31)

5. CREATE OR REPLACE PRODECURE add_dept

(p_dept_name VARCHAR2 DEFAULT ‘placeholder’,

p_location VARCHAR2 DEFAULT ‘Boston’)

IS

BEGIN

INSERT INTO departments

VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location);

END add_dept;

/

Which three are valid calls to the add_dep procedure? (Choose three)

A. add_dept;

B. add_dept(‘Accounting’);

C. add_dept(, ‘New York’);

D. add_dept(p_location=>’New York’);

6.You need to create a trigger on the EMP table that monitors every row that is changed and places this nformation into the AUDIT_TABLE.

What type of trigger do you create?

A. FOR EACH ROW trigger on the EMP table.

B. Statement-level trigger on the EMP table.

C. FOR EACH ROW trigger on the AUDIT_TABLE table.

D. Statement-level trigger on the AUDIT_TABLE table.

E. FOR EACH ROW statement-level trigger on the EMP table.

7. CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER

IS

BEGIN

RETURN (sal * 0.05);

END;

If you want to run the above function from the SQL *Plus prompt, which statement is true?

A. You need to execute the command CALCTAX(1000);.

B. You need to execute the command EXECUTE FUNCTION calctax;.

C. You need to create a SQL *Plus environment variable X and issue the command

:X := CALCTAX(1000);.

D. You need to create a SQL *Plus environment variable X and issue the command

EXECUTE :X := CALCTAX;

E. You need to create a SQL *Plus environment variable X and issue the command

EXECUTE :X := CALCTAX(1000);

8. CREATE OR REPLACE TRIGGER secure_emp

BEFORE LOGON ON employees

BEGIN

IF (TO_CHAR(SYSDATE, ‘DY’) IN (‘SAT’, ‘SUN’)) OR

(TO_CHAR(SYSDATE, ‘HH24:MI’)

NOT BETWEEN ’08:00’ AND ’18:00’)

THEN RAISE_APPLICATION_ERROR (-20500, ‘You may

insert into the EMPLOYEES table only during

business hours.’);

END IF;

END;

/

What type of trigger is it?

A. DML trigger

B. INSTEAD OF trigger

C. Application trigger

D. System event trigger

E. This is an invalid trigger.

9. When creating a function in SQL *Plus, you receive this message:

“Warning: Function created with compilation errors.”

Which command can you issue to see the actual error message?

A. SHOW FUNCTION_ERROR

B. SHOW USER_ERRORS

C. SHOW ERRORS

D. SHOW ALL_ERRORS

10. Which four triggering events can cause a trigger to fire? (Choose four)

A. A specific error or any errors occurs.

B. A database is shut down or started up.

C. A specific user or any user logs on or off.

D. A user executes a CREATE or an ALTER table statement.

E. A user executes a SELECT statement with an ORDER BY clause.

F. A user executes a JOIN statement that uses four or more tables.

11. Which type of argument passes a value from a procedure to the calling environment?

A. VARCHAR2

B. BOOLEAN

C. OUT

D. IN

12. You are about to change the arguments of the CALC_TEAM_AVG function.

Which dictionary view can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?

A. USER_PROC_DEPENDS

B. USER_DEPENDENCIES

C. USER_REFERENCES

D. USER_SOURCE

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

14. 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;

15. 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?

1. Row

B. Statement

C. ORACLE FORM trigger

2. D. Before

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

16.In which areas of the PL/SQL block must code be placed in order to handle Oracle-defined exceptions?

A. Declaration section only

B. Declaration and executable sections only

C. Exception handler only

D. Declaration, executable, and exception handler sections

17. An Oracle-defined exception is raised

A. By the PL/SQL raise statement

B. In the PL/SQL exception handler

C. Automatically by Oracle

D. By the user

18. A procedure declares a user-defined exception but does not raise it explicitly. Which of the following statements is true about this function?

A. The user-defined exception will never be raised.

B. The user-defined exception will be handled by a WHEN OTHERS exception handler.

C. The procedure will fail on compile.

D. The user-defin The SOCCER_FANS table has a trigger associated with it that inserts data into SOCCER_FANS_SNACKS henever rows are inserted into SOCCER_FANS. A foreign key constraint exists between FAN_ID on OCCER_FANS and SOCCER_FANS_SNACKS. What happens when the trigger fires?

A. The trigger processes normally.

B. The trigger invalidates.

C. The trigger execution fails because of a mutating or constraining table.

D. The trigger execution succeeds because the trigger is a statement trigger.

ed exception is defined incorrectly.

19.  To find information about trigger status, which of the following views are appropriate?

A. ALL_TRIGGERS

B. ALL_OBJECTS

C. ALL_TRIGGER_COLS

D. ALL_SOURCE

20. Which of the following trigger types will be impacted by constraining factors brought on by mutating tables?

A. Row triggers only

B. Statement triggers only

C. Both row and statement triggers

D. Neither row or statement triggers