Unformatted text preview: he DELETE
statement (in the WHERE clause), and in the INSERT statement (in the VALUES
B. Functions allow calculations on data across a database link.
C. Functions can be used to manipulate these data types.
WWW.ExamMagic.COM - 53: Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DBMS_OUTPUT.PUT_LINE(' There are now ' || a ||
This trigger results in an error after this SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
How do you correct the error?
A. Change the trigger type to a BEFORE DELETE.
B. Take out the COUNT function because it is not allowed in a trigger.
C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
D. Change the trigger to a statement-level trigger by removing FOR EACH ROW.
A mutating table is a table against which a data manipulation statement has been issued
corresponding trigger on the DML statement is reading from the same table, at the same
Mutating tables are not valid for statement triggers because statement triggers fire only
once for each
event and allow the process to complete before the trigger is actually fired. Row triggers
cause a table to mutate because row triggers fire for each row. To correct this problem
you change the trigger to a Statement-Level Trigger by removing FOR EACH ROW or
by specifying FOR EACH STATEMENT.
A. This will still result in an error and it will not achieve the intended result.
B. You may use the COUNT function in a Trigger.
C. The DBMS_OUTPUT statement is allowed in a Trigger .
WWW.ExamMagic.COM - 54: What is true about stored procedures?
A. A stored procedure uses the DELCLARE keyword in the procedure specification to
declare formal parameters.
B. A stored procedure is named PL/SQL block with at least one parameter declaration in the
C. A stored procedure must have at least one executable statement in the procedure body.
D. A stored procedure uses the DECLARE keyword in the procedure body to declare
The executable section must contain at least one executable statement. You
can include a NULL keyword to fulfill the need to have at least one executable statement
in this section.
A. Local variables are declared after the IS or AS keyword and before the BEGIN
keyword. The DECLARE keyword is not used.
B. It is not required that a Procedure have a parameter.
D. You do not use the DELCARE keyword when specifying parameters. The syntax for creating a
CREATE [OR REPLACE] PROCEDURE
[parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS | AS
www.ExamMagic.com 1Z0-147 BEGIN
c.COM - 55: Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept
INSERT INTO departments
VALUES (5, 'Education', 150, p_location_id);
CREATE OR REPLACE PROCEDURE insert_location
( p_location_id NUMBER,
INSERT INTO locations(location_id, city)
VALUES (p_location_id, p_city);
You just created the departments, the locations, and the employees table. You did
not insert any rows. Next you created both procedures.
You new invoke the insert_location procedure using the following command:
EXECUTE insert_location (19, 'San Francisco')
What is the result in thisEXECUTE command?
A. The locations, departments, and employees tables are empty.
B. The departments table has one row.
The locations and the employees tables are empty.
C. The location table has one row.
The departments and the employees tables are empty.
D. The locations table and the departments table both have one row.
The employees table is empty. www.ExamMagic.com 1Z0-147
All of the tables are empty. When the following statement executed:
WHERE employee_id = 9999;
An error is thrown and there is no exception section in this procedure or the calling
procedure, therefore all transactions are rolled back.
Note: If the exception is not handled in the called procedure, the control is transferred to
section of the calling procedure. If the exception is handled, all the
statements remain intact. If the
exception is not handled in the calling procedure's exception-handling section, all the
rolled back, and the exception propagates to the calling environment.
B. This is not true because an error is occurs on the SELECT Statement and the INSERT
Transaction is rolled back when the error is encountered.
C. The insert into the location table is also rolled back. The insert_locaton calls the
insert_dept Stored Procedure, this procedure fails and control is passed back to the
calling procedure which does not have an exception section , therefore both the inse...
View Full Document
This note was uploaded on 01/05/2011 for the course COMS 131 taught by Professor Dad during the Spring '10 term at University of Mumbai.
- Spring '10