This preview shows page 1. Sign up to view the full content.
Unformatted text preview: ExamMagic.com Exam : 1Z0-147
Title : Oracle 9i: Program with PL/SQL Ver : 7.0 1Z0-147 WWW.ExamMa
gic.COM - 1: Examine this function:
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE)
SELECT HITS / AT_BATS
WHERE PLAYER_ID = V_ID;
Which statement will successfully invoke this function in
A. SELECT CALC_PLAYER_AVG(PLAYER_ID)
B. EXECUTE CALC_PLAYER_AVG(31);
E. START CALC_PLAYER_AVG(31)
A function can be invoked in SELECT Statement provided that the function does not
modify any database tables. The function must use positional notation to pass values to
the formal parameters. The formal parameters must be of the IN mode. They should
return data types acceptable to SQL and they should not include any transaction, session,
or system control statements.
B. You can't call a function in this way, in this way you can call a procedure, because
function must return a value, to call a function using EXECUTE command you should
declare a bind variable using the VARIABLE command then assign the value returned
from the function to this variable, in the following way:
SQL> VARIABLE v_get_value NUMBER
SQL> EXECUTE :v_get_value := CALC_PLAYER_AVG(31)
PL/SQL procedure successfully completed.
SQL> PRINT v_get_value
----------C. Again this way can't be use for calling a function in PL/SQL block because the
function return a value and this values must be assigned to PL/SQL variable or to bind
variable. Like this
v_get_from := CALC_PLAYER_AVG(31);
D. Same as C.
E. START is use to execute a script.
WWW.ExamMagic.COM - 2: Which three are true statements about dependent objects? (Choose three)
A. Invalid objects cannot be described.
B. An object with status of invalid cannot be a referenced object.
C. The Oracle server automatically records dependencies among objects.
D. All schema objects have a status that is recorded in the data dictionary.
E. You can view whether an object is valid or invalid in the USER_STATUS data
F. You can view whether an object is valid or invalid in the USER_OBJECTS data
Incorrect answers: B, D, E
WWW.ExamMagic.COM - 3: You have created a stored procedure DELETE_TEMP_TABLE that uses
dynamic SQL to remove a table in your schema. You have
granted the EXECUTE privilege to user A on this procedure.
When user A executes the DELETE_TEMP_TABLE procedure, under whose
privileges are the operations performed by default?
A. SYS privileges
B. Your privileges
C. Public privileges
D. User A's privileges
E. User A cannot execute your procedure that has dynamic SQL.
www.ExamMagic.com 1Z0-147 Answer: B
When you create a procedure, it will be executed under the privileges of the creator,
unless the procedure has the following statement AUTHID CURRENT_USER. If you
specify AUTHID CURRENT_USER, the privileges of the current user are checked at run
time, and external references are resolved in the schema of the current user. Like this
SQL> CREATE OR REPLACE PROCEDURE delete_temp_table(v_table
2 AUTHID CURRENT_USER
5 EXECUTE IMMEDIATE 'DROP TABLE '||V_TABLE;
If the procedure is create in this way then the EXECUTE IMMEDIATE statement will be
execute under the privilege of the user who executes the procedure, but if we skip line 2
then the procedure will be executed under the privilege of the owner of the procedure.
A: SYS privilege has nothing with is.
C: What is the public privileges? There is nothing called public privileges.
D: This will be true if the procedure contains the AUTHID CURRENT_USER.
E: There is no problem in having a dynamic SQL statement in Procedure.
WWW.ExamMagic.COM - 4: Examine this code:
CREATE OR REPLACE PRODECURE add_dept
(p_dept_name VARCHAR2 DEFAULT 'placeholder',
p_location VARCHAR2 DEFAULT 'Boston')
INSERT INTO departments
VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location);
Which three are valid calls to the add_dep procedure? (Choose three)
C. add_dept(, 'New York');
D. add_dept(p_location=>'New York');. www.ExamMagic.com 1Z0-147
A is correct because both of the parameter have a default values.
B is correct because here we call the procedure using position notation, and the first
parameter for the procedure will have the value 'Accounting', and since the second
parameter has a default value then we can skip it, and in this case it will take the default
D is correct because here we are calling the procedure using naming notation, the value
'New York' will go to the parameter p_location, and the parameter p_dept_name will
have the default value.
The following table list the for passing parameters to a
C: You can't use this way and assume that the PL/SQL will understand that he should
assign the default value for the first paramet...
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