Wwwexammagiccom 100 when using a plsql stored package

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: on. C. This would not require an INSTEAD of Trigger, rather you would specify BEFORE UPDATE OF ColumnName ON TableName. WWW.ExamMagic.COM - 100: When using a PL/SQL stored package, how is a side effect defined? A. changes only to database tables B. changes only to packaged public variables defined in a package body C. changes only to packaged public variables defined in a package specification D. changes to database tables or packaged public variables defined in a package body E. changes to database tables or packaged variables defined in a package specification Answer: E Explanation: Side effects are defined as changes to the database tables or the public packaged variables. Incorrect Answers A. Side effects are not limited to changes to the database tables. They also include changes to public packaged variables. B. Variables defined in the package body are not public. C. This option excludes changes to database tables. D. Database Tables and Public variables are not defined in a package body. WWW.ExamMagic.COM - 101: Examine this package CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER:=7839 discount_rate NUMBER:=0.00; PROCEDURE display_price(p_price NUMBER); END discount; www.ExamMagic.com 1Z0-147 / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE display_price (p_price NUMBERI) IS BEGIN DBMS_OUTPUT.PUT LINE ( 'Discounted '|| TO_CHAR(p_price*NVL(discount_rate,1))); END discount; BEGIN Discount_rate=0.10; END discounts; / The SOL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure DISPLAY_PRICE from SOL*Plus with the command EXECUTE discount.display_price(100); What is the result? A. Discounted 10 B. Discounted 100 C. Discounted 0.00 D. Discounted NULL E. Discounted 0.10 Answer: A Explanation: The discounts package contains a one-time-only procedure which is executed when the package is first referenced and sets the public variable discount_rate = .10. The value of 100 is passed to the p_price parameter and this is multipled by the discount_rate resulting in a value of 'Discounted 10' is displayed by the DBMS_OUTPUT.PUT_LINE. Incorrect Answers B, C, D & E are incorrect WWW.ExamMagic.COM - 102: Which two statements about functions are true? (Choose two.) A. A function must have a return statement in its body to execute successfully B. Client-side functions can be used in SOL statements C. A stored function that is called from a SOL statement can return a value of any PL/SOL variable data type D. From SOL*Plus, a function can be executed by giving the command EXECUTE functionname; www.ExamMagic.com 1Z0-147 E. A stored function increases efficiency of queries by performing functions on the server rather than in the application Answer: A, E Explanation: There should be a RETURN statement in the function body. If the RETURN statement in the executable section is omitted, the function will successfully compile but the following error will be generated at run time: ORA-06503: PL/SQL: Function returned without value This is because the function does not return any value to the calling block. E. User-defined functions increase the efficiency of queries by applying the functions in the query itself. This improves performance because the data will be filtered on the server as opposed to the client which will reduce network traffic. Incorrect Answers B. Functions must be stored on the server to be used in a SQL Statement. C. Functions called from SQL expressions should return the data type that is compatible with SQL. PL\SQL Data Types such as BOOLEAN, RECORD, or TABLE data types are not supported by SQL. D. Functions are not called like procedures. You cannot use EXECUTE to invoke a function unless you have a variable to hold the returning value. WWW.ExamMagic.COM - 103: Examine this code CREATE OR REPLACE PROCEDURE load bfile (p_flle_loc IN VARCHAR2) IS V_file BFILE; v_filename VARCHAR2(16); CURSOR emp_cursor IS SELECT employee_id FROM employees WHERE Job_id = 'IT_PROG' FROM UPDATE BEGIN FOR emp_record IN emp_cursor LOOP v_filename:=emp_record.emplyee_id||;GIF'; V_file:=BFILENMAE(p_file_loc,v_filename); END LOOP; END; / What does the BFILENAME function do? www.ExamMagic.com 1Z0-147 A. It reads data from an external BFILE B. It checks for the existence of an external BFILE C. It returns a BFILE locator that is associated with a physical LOB binary file on the server's file system D. It creates a directory object for use with the external BFILEs Answer: C Explanation: In Oracle/PLSQL, the BFILENAME function returns a BFILE locator for a physical LOB binary file. Incorrect Answers A. DBMS_LOB.READ Procedure reads data from a BFILE. B. DBMS_LOB.FILEEXISTS functions checks for the existence of the file on the Server. D. You do not use the BFILENAME function to create a directory object. The syntax to create the directory object is: CREATE DIRECTORY<directory name> AS <operating system path> WWW.ExamMagic.COM - 104: Consider this scenario A procedure X references a view Y that is ba...
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