Oracle Mock Final

1. You need to remove database trigger BUSINESS_RULE. Which command do you use to remove the trigger in the SQL*Plus environment?

DROP TRIGGER business_rule;

DELETE TRIGGER business_rule;

REMOVE TRIGGER business_rule;

ALTER TRIGGER business_rule;

DELETE FROM USER_TRIGGER WHERE TRIGGER_NAME= 'BUSINESS_RULE';

2. Examine this procedure CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2) IS BEGIN INSERT INTO PLAYER(ID,LAST_NAME). VALUES(V_ID,V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD-STAT procedure and pass a parameter. Which statement will successfully invoke this procedure?

EXECUTE ADD_PLAYER (10,a);

ADD_PLAYER (10,a);

RUN ADD_PLAYER (10,a);

START ADD_PLAYER (10,a);

3. Which code successfully calculates tax?

4. Which type of argument passes a value from a calling environment?

VARCHER2.

BOOLEAN.

OUT.

IN.

5. You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. Which type of trigger do you create?

Statement-level trigger on the EMP table.

For each row trigger on the EMP table.

Statement-level trigger on the AUDIT_TABLE table.

For each row statement level trigger on the EMP table.

For each row trigger on the AUDIT_TABLE table.

6. CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON emp BEGIN INSERT INTO audit_table (who, audited) VALUES(USER, SYSDATE); END; You issue an update command on the EMP table that results in changing ten rows. How many rows are inserted into the AUDIT_TABLE?

1

10

None

Value equal to the number of rows in the emp table

7. This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BAT_STAT (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?

Row

Statement

ORACLE FORM trigger

Before

8. Examine this trigger. CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY AFTER INSERT OR UPDATE OR DELETE ON PLAYER FOR EACH ROW BEGIN UPDATE TEAM SET TOT_SALARY=TOT_SALARY+:NEW SALARY. WHERE ID=:NEW:TEAM_ID; You will be adding additional coat later but for now you want the current block to fire when updated the salary column. Which solution should you use to verify that the user is performing an update on the salary column?

ROW_UPDATE('SALARY')

UPDATING('SALARY')

CHANGING('SALARY')

COLUMN_UPDATE('SALARY')

9. The QUERY_PRODUCT procedure directly references the product table. There is a NEW_PRODUCT_VIEW view created based on the NOT NULL columns of the table. The ADD_PRODUCT procedure updates the table indirectly by the way of NEW_PRODUCT_VIEW view. Under which circumstances does the procedure ADD_PRODUCT get invalidated but automatically get complied when invoked?

When the NEW_PRODUCT_VIEW is dropped.

When rows of the product table are updated through SQI Plus.

When the internal logic of the QUERY_PRODUCT procedure is modified.

When a new column that can contain null values is added to the product table.

When a new procedure s created that updates rows in the product table directly.

10. Under which situation do you create a server side procedure?

When the procedure contains no SQL statements.

When the procedure contains no PL/SQL commands.

When the procedure needs to be used by many client applications accessing several remote databases.

When the procedure needs to be used by many users accessing the same schema objects on a local database.

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

In the package body

In a database trigger

In the package specification

In each procedure's DECLARE section, using the exact same name in each

12. Examine this package CREATE OR REPLACE PACKAGE discounts IS G_ID NUMBER:=7839; DISCOUNT_RATE NUMBER O. 00; PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER); END DISCOUNTS; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('DISCOUNTED||2_4 (V_PRICE*NVL(DISCOUNT_RATE, 1))) END DISPLAY_PRICE; BEGIN DISCOUNT_RATE;=0. 10; END DISCOUNTS; / Which statement is true?

The value of DISCOUNT_RATE always remain 0. 00 in a session.

The value of DISCOUNT_RATE is set to 0. 10 each time the package are invoked in a session.

The value of DISCOUNT_RATE is set to 1 each time the procedure DISPLAY_PRICE is invoked.

The value of DISCOUNT_RATE is set to 0. 10 when the package is invoked for first time in a session.

13. What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

The rows are selected and ordered.

The validity of the SQL statement is established.

An area of memory is established to process the SQL statement.

The SQL statement is run and the number of rows processed is returned.

The area of memory established to process the SQL statement is released.

14. Which three are valid ways to minimize dependency failure? (Choose three.)

Querying with the SELECT * notation

Declaring variables with the %TYPE attribute

Specifying schema names when referencing objects

Declaring records by using the %ROWTYPE attribute

Specifying package.procedure notation while executing procedures

Submit

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

Packages can be nested.

You can pass parameters to packages.

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

The contents of packages can be shared by many applications.

You can achieve information hiding by making package constructs private.

Submit

16. Which two statements about the overloading feature of packages are true? (Choose two)

Only local or packaged sub programs can be overloaded.

Overloading allows different functions with the same name that differ only in their return types.

Overloading allows different subprograms with the same number, type and order of the parameter.

Overloading allows different subprograms with the same name and same number or type of the parameters.

Overloading allows different subprograms with the same name but different in either number or type or order of parameter.

Submit

17. All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)

GRANT SELECT ON ADD_PLAYER TO PUBLIC;

GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

GRANT INSERT ON PLAYER TO PUBLIC;

GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

REVOKE INSERT ON PLAYER FROM PUBLIC;

Submit

18. Which two statements are true about associative arrays and varrays? (Choose two.)

Only varrays must start with the subscript 1.

Only varrays can be used as column types in database tables.

Both associative arrays and varrays must start with the subscript 1.

Both associative arrays and varrays can be used as column types in database tables.

Submit