PLSQL MCQ's Set 1
1. A database trigger can be created on which two objects? (choose two.)
A. view
B. table
C. index
D. package
E. function
F. procedure
Answer: A & B
2. Examine this database trigger:
CREATE OR REPLACE TRIGGER audit_gross_receipt
AFTER DELETE OR UPDATE OF seats_sold, cost_per_ticket ON gross_receipt BEGIN
... END;
When deleting multiple rows form the GROSS_RECEIPT table, the AUDIT_DELETE_GR procedure of the THEATER_PCK package must only be executed once for the entire DELETE statement.
A. IF DELETING THEN theater_pck.audit_delete_gr; END IF;
B. IF CHECK_DELETING THEN theater_pck.audit_delete_gr; END IF;
C. IF DMBS_SQL(‘DELETE’) THEN theater_pck.audit_delete_gr; END IF;
D. IF DMBS_check_manipulation (‘DELETE’) THEN theater_pck.audit_delete_gr; END IF;
Answer: A
3.Examine this database trigger:
CREATE OR REPLACE TRIGGER update_total_gross
{additional trigger information} BEGIN
END;
UPDATE theater
SET total_gross = total_gross_sold * :new.cost._per_ticket) WHERE id = :new.theater_id;
This database trigger will be created on the GROSS_RECEIPT table. This code should only execute when the value of the SHOW-DATE column of the GROSS_RECEIPT table is less than today’s date. Which clause will enforce this rule?
A. WHEN (new.show_date <sysdate)
B. WHEN ( :new.show_date <sysdate)
C. WHERE (new.show_date <sysdate)
D. WHERE ( :new.show_date <sysdate)
Answer: A
4.Examine this database trigger:
CREATE OR REPLACE TRIGGER update_show_gross
{additional trigger information} BEGIN
{additional code} END;
The trigger code should only execute when COST_PER_TICKERTcolumn is greater that $3.75. which trigger information will you add?
A. WHEN (new.cost_pert_ticket > 3.75)
B. WHEN ( :new.cost_pert_ticket > 3.75)
C. WHERE (new.cost_pert_ticket > 3.75)
D. WHERE ( :new.cost_pert_ticket > 3.75)
Answer: A
5.Examine this database trigger:
CREATE OR REPLACE TRIGGER prevent_gross_modification
{additional trigger information} BEGIN
IF T0_CHAR(sysdate, ‘DY’) = ‘MON’ THEN
RAISE_APPLICATION-ERROR (-20000, ‘Gross receipts cannot be entered on ‘Monday’);
END IF;
END;
This trigger must fire before each DELETE, INSERT, and UPDATE of the GROSS_RECEIPT table. It should fire only once for the entire data manipulation statement. Which additional trigger information must you add?
A. BEFORE (gross_receipt) DELETE, INSERT, UPDATE
B. AFTER DELETE OR INSERT OR UPDATE ON gross_receipt
C. BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt
D. BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt FOR EACH ROW
Answer: C
6.Procedures and functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
A. when the transaction is committed
B. During a data manipulation statement
C. When it is explicitly invoked by another construct
D. When an oracle supplied package references the trigger
Answer: B
7.Which trigger timing can be used when creating a trigger on a view which does not allow DML?
A. AFTER only
B. BEFORE only
C. INSTEAD OF only
D. BEFORE and AFTER only
E. BEFORE, AFTER, and INSERT OF
Answer: C
8.Examine this database trigger:
CREATE OR REPLACE TRIGGR audit_gross_modification AFTER INSERT OR DELETE ON gross_receipt
BEGIN
END;
INSERT INTO audit_gross VALUES (USER, SYSDATE);
To test this trigger, you delete 30 rows from the GROSS_RECEIPT table. How many rows are inserted into the AUDIT_GROSS table due to this event?
A. 1
B. 30
C. 31
D. none
Answer: A
9.Examine this database trigger:
CREATE OR REPLACE TRIGGER update_show_gross
{additional trigger information} BEGIN
{additional code} END;
The code of this trigger needs to calculate a value for the derived column, SHOW_GROSS. The value is determined by the multiplication result of SEATS_SOLD and COST_PER_TICKET. This value should only be calculated when the triggering operation is an insert. Which code will successfully assign a value to SHOW_GROSS when inserting?
A. IF INSERTING THEN
new.show_gross :=new.seats_sold * new.cost_per_ticket; END IF;
B. IF INSERTING THEN
:new.show_gross :=new.seats_sold * :new.cost_per_ticket; END IF;
C. IF DBMS_DML (‘INSERTING’) THEN new.show_gross :=.newseats_sold *new.cost_per_ticket; END IF;
D. IF DBMS_DML (‘INSERT’) THEN
:new.show_gross :=new.seats_sold * :new.cost_per_ticket; END IF;
Answer: A
10.Which allows a PL/SQL user define a function?
A. NEXTVAL.
B. HAVING clause of the SELECT COMMAND.
C. ALTER TABLE command.
D. FROM clause of the SELECT AN UPDATE COMMANDS.
Answer: B
11.Which two statements about the overloading feature of packages are true? (Choose two)
A. Only local or packaged sub programs 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 the parameter.
D. Overloading allows different subprograms with the same name and same number or type of the parameters.
E. Overloading allows different subprograms with the same name but different in either number or type or order of parameter.
Answer: A & E
12.Examine this package:
CREATE OR REPLACE manag 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(o_sal NUMBER) RETURN NUMBER;
END manag_emps;
/
CREATE REPLACE PACKAGE BODY manage_emps IS
BEGIN
Update emp.
SET sal=|sal+p-raise_amt)+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_deptno, 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:=0. 05;
ELSIF v_sal<1000 THEN
V_raise:=0. 07; ELSE
V_raise:=0. 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_emp;
/
What is the name of the private procedure in the package?
A. CALC_TAX
B. INSERT_EMP
C. UPDATE_SAL
D. DELETE_EMP
E. UPDATE_EMP
F. MANAGE_EMP
Answer: C
13.Examine the code:
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?
A. 1
B. 10
C. none
D. Value equal to the number of rows in the emp table
Answer: A
14.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)
A. GRANT SELECT ON ADD_PLAYER TO PUBLIC;
B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
C. GRANT INSERT ON PLAYER TO PUBLIC;
D. GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;
E. REVOKE INSERT ON PLAYER FROM PUBLIC;
Answer: B & C
15.Which Oracle supply package allows you to run jobs at use defined times?
A. DBMS_JOB
B. DBMS_RUN
C. DBMS_PIPE
D. DBMS_SQL
Answer: A
