Unformatted text preview: ES(v_id, p_depntno, p_sal);
DELETE FROM emp
WHERE empno = v_id;
v_sal NUMBER(10, 2);
v_raise NUMBER(10, 2);
WHERE empno = v_id;
IF v_sal < 500 THEN
v_raise := .05;
ELSIP v_sal < 1000 THEN
v_raise := .07;
v_raise := .04;
RETURN p_sal * tax_rate;
How many public procedures are in the MANAGE_EMPS package?
There are three procedure that are declared in the Package Specification and these
Procedures are public and can be called from inside and outside of the package.
A, B, D & E. There are three procedures and one function declared in the package
specification which are public. There is also one procedure that is only declared in the
package body and it is private.
WWW.ExamMagic.COM - 73: Which command must you issue to allow users to access the UPD_TEAM_STAT
trigger on the TEAM table?
A. GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC;
B. GRANT SELECT,INSERT,UPDATE,DELETE ON UPD_TEAM_STAT TO
C. GRANT EXECUTE ON TEAM TO PUBLIC
D. GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC;
WWW.ExamMagic.COM - 74: Examine this code:
CREATE OR REPLACE PROCEDURE set_bonus
(p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY'
p_employee_id IN employees_employee_id%TYPE
p_salary IN employees_salary%TYPE,
p_bonus_percent IN OUT NUMBER DEFAULT 1.5,
p_margin OUT NUMBER DEFAULT 2,
p_bonus_value OUT NUMBER)
SET bonus_amount =(p_salary * p_bonus_percent)/p_margin
WHERE employee_id = p_employee_id;
You execute the CREATE PROCEDURE statement above and notice that it fails.
What are two reasons why it fails? (Choose two)
A. The syntax of the UPDATE statement is incorrect.
B. You cannot update a table using a stored procedure.
C. The format parameter p_bonus_value is declared but is not used anywhere.
D. The formal parameter p_cutoff cannot have a DEFAULT clause.
E. The declaration of the format parameter p_margin cannot have a DEFAULT clause.
F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT
Answer: E, F
You can't assign a default value for the OUT and IN OUT parameters. Assigning a
default value to an OUT or IN OUT parameter causes the following compilation error:
PLS-00230: OUT and IN OUT formal parameters may not have default expressions
A. There are no syntax errors on the Procedure.
B. Updates in a Stored Procedure are allowed and are quite common.
C. This parameter has a default value and the fact that it is not being used in the
Procedure will not cause an error.
WWW.ExamMagic.COM - 75: Which three statements are true regarding database triggers? (Choose three)
A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table,
view, schema, or the database.
B. A database trigger needs to be executed explicitly whenever a particular event takes
C. A database trigger executes implicitly whenever a particular event takes place.
D. A database trigger fires whenever a data event (such as DML) or system event (such
as logon, shutdown) occurs on a schema or database.
E. With a schema, triggers fire for each event for all users; with a database, triggers fire
for each event for that specific user.
Answer: A, C, D
The trigger body is a PL/SQL block in which you can issue both SQL and PL/SQL
statements from the trigger body. You can also call a stored procedure or a Java
procedure from the trigger body. You can also invoke a procedure developed using
object-oriented languages, such as C.
C Triggers are invoked implicitly whenever a particular event occurs in the database.
D Database Triggers occur when a Data Event DM, DDL occurs or when system
events such as shutting down or starting up the database or a logon or logoff from the database.
B. Database Triggers are invoked implicitly whenever a particular event occurs in the
E. Triggers that are created at the database level fire for all the users, and triggers that are
created at the schema level fire only when the triggering event involves that schema.
WWW.ExamMagic.COM - 76: You create a DML trigger. For the timing information, which are valid with a DML
trigger? www.ExamMagic.com 1Z0-147
B. IN PLACE OF
C. ON SHUTDOWN
E. ON STATEMENT EXECUTION
Trigger Timing determines whether the trigger will fire BEFORE or AFTER the DML
the table. The BEFORE and AFTER timings are not valid for views. You can only create
INSTEAD OF trigger on a view.
A, B, C & E are not valid Oracle Events
WWW.ExamMagic.COM - 77: Whic...
View Full Document
- Spring '10
- Stored procedure, Incorrect Answers