You execute the procedure proc1 from sql plus with

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: table has more than 1000 rows. The SQL *Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL *Plus with the command: EXECUTE pack_cur.proc1 What is the output in your session? A. ERROR at line 1: B. Row is: Row is: Row is: C. Row is: 1 Row is: 2 Row is: 3 D. Row is: 4 Row is: 5 Row is: 6 Answer: C Explanation: proc1 will open the C1 Cursor and go into a Loop. The Loop Fetches and outputs the first three records. Since the SET SERVEROUTPUT Command was set the results will be displayed Incorrect Answers A. This procedure will successfully execute with errors. B. The output will include the Row Number fetched from the cursor. D. This would be the output if you executed the proc1 cursor fro the second time. WWW.ExamMagic.COM - 96: The add_player procedure inserts rows into the PLAYER table. Which command will show this directory dependency? A. SELECT * FROM USER_DEPENDENCIES WHERE REFERENCD NAME = ' PLAYER ' ; B. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' ADD PLAYER ' ; C. SELECT * FROM USER_DEPENDENCIES WHERE TYPE = 'DIR' ; D. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' TABLE ' ; Answer: A www.ExamMagic.com 1Z0-147 Explanation: The REFERENCED_NAME Column displays the name of the referenced object. If you specify Player in the Referenced name column all objects that reference the PLAYER Table(Direct Dependecies) will be displayed. Incorrect Answers B. This would show all dependencies on the add_player, not the PLAYER Table. C. This would not display anything. The TYPE Column list the dependent object's type (procedure, function, package, package body,trigger, or view. D. This would not display anything. If you wanted all dependencies for OBJECTS of TYPE TABLE you would filter on the TYPE Column. WWW.ExamMagic.COM - 97: When using a packaged function in a query, what is true? A. The COMMIT and ROLLBACK commands are allowed in the packaged function. B. You can not use packaged functions in a query statement. C. The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried. D. The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery. E. The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used. Answer: C Explanation: A function, stand-alone or package can't execute DML (INSERT, UPDATE & DELETE) against the table that is being queried. This will result in a mutating table and generate a runtime error. Incorrect Answers A. The function must not end the current transaction with COMMIT or ROLLBACK, or ROLLBACK to a savepoint prior to the function execution. B. You can use packaged functions in query statements provided they do not violate certain restrictions. D. This results in a mutating table and will generate a runtime error. E. The PRAGMA RESTRICT_REFERENCES is a compiler directive compiler as to what a function can and cannot do. This compiler processing so that the function conforms to its purity level. This will not prevent the problem of a mutating table. www.ExamMagic.com 1Z0-147 WWW.ExamMagi c.COM - 98: You have a table with the following definition: CREATE TABLE long_tab ( id NUMBER) long_col LONG) You need to convert the LONG_COL column from a LONG data type to a LOB data type. Which statement accomplish this task? A. AKTER TABLE long_tab MODIFY (LONG_COL CLOB); B. EXECUTE dbms_lob.migrate(long_tab, long_col, clob) C. EXECUTE dbms_manage.lob.migrate(long_tab, long_col, clob) D. EXECUTE utl_lob.migrate(long_tab, long_col, clob) E. EXECUTE utl_manage_lob.migrate(long_tab, long_col, clob) Answer: A Explanation: In Oracle 9i, a LONG column in a Table can be migrated to a LOB column using the ALTER TABLE statement. The syntax is: ALTER TABLE &lt;schema&gt;.&lt;table name&gt;&lt;BR&gt; MODIFY <long column name> {CLOB | BLOB | NCLOB} In Oracle 8i you must use the TO_LOB function to migrate an existing LONG column to a LOB column. This function can only be used in the SELECT list of a subquery in an INSERT Statement. WWW.ExamMagic.COM - 99: Why do you use an INSTEAD OF trigger? A. To perform clean up actions when ending a user session. B. To insert data into a view that normally does not accept inserts. C. To insert into an audit table when data is updated in a sensitive column. D. To modify data in which the DML statement has been issued against an inherently non-updateable view. Answer: D Explanation: An INSTEAD OF trigger is used to perform a DML activity on the underlying tables of a view that www.ExamMagic.com 1Z0-147 is inherently non-updatable. Incorrect Answers A. This could be performed by a System Event Trigger (BEFORE LOGOFF) not by an INSTEAD OF Trigger. B. You can't insert data into a view, however, you can insert data into the underlying table that the view is based...
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