07 else vraise 04 end if updatesalvraise end updateemp

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: _id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate; END calc_tax; END manage_emps; / What is the name of the private procedure in this package? www.ExamMagic.com 1Z0-147 A. CALC_TAX B. INSERT_EMP C. UPDATE_SAL D. DELETE_EMP E. UPDATE_EMP F. MANAGE_EMPS Answer: C Explanation: This procedure is not declared in the package specification and therefore it is not public. Constructs declared and defined in the package body are known as private constructs. These constructs are available from inside the package only and cannot be called from outside the package. Incorrect Answers A, B, D & E Procedures declared in the package specification are public constructs and they can be referenced from outside the package. F. Is the Package Name and it is not a Public or Private Procedure WWW.ExamMagic.COM - 44: What can you do with the DBMS_LOB package? A. Use the DBMS_LOB.WRITE procedure to write data to a BFILE. B. Use the DBMS_LOB.BFILENAME function to locate an external BFILE. C. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE. D. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed. Answer: D Incorrect Answers: A. DBMS_LOB.WRITE is used to write to Internal LOBs. The internal LOB is stored inside the Oracle server. A BLOB, NCLOB, or CLOB can be one of the following: An attribute of a user-defined type A column in a table A bind or host variable A PL/SQL variable, parameter, or result Internal LOBs can take advantage of Oracle features such as: Concurrency mechanisms Redo logging and recovery mechanisms Transactions with commit or rollbacks www.ExamMagic.com 1Z0-147 B. BFILENAME is a built-in function that initializes a BFILE column to point to an external file. Use the BFILENAME function as part of an INSERT statement to initialize a BFILE column by associating it with a physical file in the server file system. You can use the UPDATE statement to change the reference target of the BFILE. A BFILE can be initialized to NULL and updated later by using the BFILENAME function. C. DBMS_LOB.FILEEXISTS function to find if the file exits on the server WWW.ExamMagic.COM - 45: Examine this package: CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_PLAYER_AVG NUMBER(4,3); PROCEDURE UPD_PLAYER_STAT V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID; COMMIT; VALIDATE_PLAYER_STAT(V_ID); END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER) IS BEGIN INSERT INTO PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0); END ADD_PLAYER; END BB_PACK / Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package? www.ExamMagic.com 1Z0-147 A. V_PLAYER_AVG := .333; B. BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333; C. BB_PACK.V_PLAYER_AVG := .333; D. This variable cannot be assigned a value from outside of the package. Answer: D Explanation: Constructs declared and defined in the package body are private constructs and they can't be invoked outside of the package. Incorrect Answers A. Even if this variable was declared in the package specification, this is the wrong syntax for referencing a global variable from outside the package. B. Nice try but this is wrong, you can't reference a package that is defined in the package body from outside of the package. C. This is the correct syntax for assigning a value to a global variable but this is wrong because the variable was not declared in the package specification. WWW.ExamMagic.COM - 46: Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true? A. g_comm has a value of 15 at 9:06am for Smith. B. g_comm has a value of 15 at 9:06am for Jones. C. g_comm has a value of 20 at 9:06am for both Jones and Smith. D. g_comm has a value of 15 at 9:03 am for both Jones and Smith. E. g_comm has a value of 10 at 9:06am for both Jones and Smith. F. g_comm has a value of 10 at 9:03am for both Jones and Smith Answer: B www.ExamMagic.com 1Z0-147 WWW.ExamMagi c.COM - 47: Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS v_email_name VARCHAR2(19=; BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || '@Oracle.com'; UPDATE employees SET email = v_email_name WHERE employee...
View Full Document

Ask a homework question - tutors are online