Exam magic

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus? A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT; B. EXECUTE CALC_PLAYER_AVG(31); C. CALC_PLAYER('RUTH'); D. CALC_PLAYER_AVG(31); E. START CALC_PLAYER_AVG(31) Answer: A Explanation: 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. Incorrect Answers 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 V_GET_VALUE www.ExamMagic.com 1Z0-147 ----------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 DECLARE v_get_from_fn NUMBER; BEGIN v_get_from := CALC_PLAYER_AVG(31); END; / 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 dictionary view. F. You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view. Answer: A,C,F 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 example SQL> CREATE OR REPLACE PROCEDURE delete_temp_table(v_table varchar2) 2 AUTHID CURRENT_USER 3 IS 4 BEGIN 5 EXECUTE IMMEDIATE 'DROP TABLE '||V_TABLE; 6 END; 7/ Procedure created. 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. Incorrect Answers 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') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure? (Choose three) A. add_dept; B. add_dept('Accounting'); C. add_dept(, 'New York'); D. add_dept(p_location=>'New York');. www.ExamMagic.com 1Z0-147 Answer: A,B,D 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 value. 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 procedure: Incorrect Answer 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.

Ask a homework question - tutors are online