This preview shows page 1. Sign up to view the full content.
Unformatted text preview: values,
control execution, and return values to the calling environment.
A procedure is a named PL/SQL block that can accept parameters (sometimes referred to
as arguments), and be invoked. Generally speaking, you use a procedure to perform an
action. A procedure has a header, a declaration section, an executable section, and an
optional exception-handling section.
A procedure can be compiled and stored in the database as a schema object.
Procedures promote reusability and maintainability. When validated, they can be used in
any number of applications. If the requirements change, only the procedure needs to be
WWW.ExamMagic.COM - 40: Examine this code:
CREATE OR REPLACE PROCEDURE add_dept
( p_name departments.department_name%TYPE DEFAULT
p_loc departments.location_id%TYPE DEFAULT 1700)
INSERT INTO departments(department_id, department_name,
You created the add_dept procedure above, and you now invoke the procedure in
Which four are valid invocations? (Choose four)
A. EXECUTE add_dept(p_loc=>2500)
B. EXECUTE add_dept('Education', 2500)
C. EXECUTE add_dept('2500', p_loc =>2500)
D. EXECUTE add_dept(p_name=>'Education', 2500)
E. EXECUTE add_dept(p_loc=>2500, p_name=>'Education')
Explanation: www.ExamMagic.com 1Z0-147
A. This statement correctly uses named notation. A Default value is defined for both
parameters in the procedure so it is not necessary to pass any parameters.
B. This statement correct uses positional notation.
C. This statement correctly uses mixed notation.
E. This statement correctly uses named notation.
D. When using mixed notation to pass the values, all the parameters specified with
must precede the parameters specified with named notation in the subprogram call.
If the parameters specified with positional notation do not precede the parameters
specified with named
notation, the following error is generated at run time:
PLS-00312: a positional parameter association may not follow a named
WWW.ExamMagic.COM - 41: Which three are valid ways to minimize dependency failure? (Choose three)
A. Querying with the SELECT * notification.
B. Declaring variables with the %TYPE attribute.
C. Specifying schema names when referencing objects.
D. Declaring records by using the %ROWTYPE attribute.
E. Specifying package.procedure notation while executing procedures.
A. Use the SELECT * notation. This will minimize recompilation failures because
will select all the columns of the table. If you add or remove any column from the table,
the SELECT statement will not be impacted by the change in the table and will require
changes to the program unit.
B, D. Use the %ROWTYPE to declare records and %TYPE to declare variables. This
allows you to
declare records and variables that inherit the data types of the underlying columns of the
tables, rather than defining the data type of each variable in the program unit. The
and %TYPE attributes provide you the flexibility to change the data types of the columns
table without having to modify the data types specified in the program unit. www.ExamMagic.com 1Z0-147 WWW.ExamMagi
c.COM - 42: Which two does the INSTEAD OF clause in a trigger identify? (Choose two)
A. The view associated with the trigger.
B. The table associated with the trigger.
C. The event associated with the trigger.
D. The package associated with the trigger.
E. The statement level or for each row association to the trigger.
Answer A is correct. You must specify the View. INSTEAD OF TRIGGERS are created
on views to allow DML statements on an on-updateable view.
Answer C is correct. An INSETED OF Trigger can fire for all three Triggering Events
(INSERT, UPDATE and UPDATE).
Answer B is incorrect INSTEAD of Triggers can only be created for Views they can't be
created on Tables.
Answer D is incorrect. You can't call a package.
Answer E is incorrect. INSTEAD OF TRIGGERS must always fire FOR EACH ROW;
STATEMENT Level Triggers are not valid for Views
WWW.ExamMagic.COM - 43: Examine this package:
CREATE OR REPLACE PACKAGE manage_emps
tax_rate CONSTANT NUMBER(5,2) := .28;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
FUNCTION calc_tax (p_sal NUMBER)
CREATE OR REPLACE PACKAGE BODY manage_emps
SET sal = (sal * p_raise_emt) + sal
WHERE empno = v_id;
(p_deptno NUMBER, p_sal NUMBER)
INSERT INTO emp(empno, deptno, sal)
VALYES(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...
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