Oracle Database: Advanced PL/SQL (1z0-148) Free Practice Test

Question 1

You are logged on to the SCOTT schema and the schema has EMP and DEPT tables already created:
Examine this PL/SQL procedure:

Which PL/SQL block will raise an exception?

A. EXEC get_tab_row_count ('DEPT');

B. EXEC get_tab_row_count ('DEPT, EMP')

C. EXEC get_tab_row_count (' "EMP" ');

D. EXEC get_tab_row_count ('emp');

E. EXEC get_tab_row_count ('SCOTT.EMP');

Correct Answer: B

Question 2

Examine the code:

Which two subprograms will be created successfully?

A. CREATE PROCEDURE p1 (y IN OUT pkg.tab_typ) ISBEGINEXECUTE IMMEDIATE 'SELECT f (:b) FROM DUAL' INTO y USING pkg.x;END p1;

B. CREATE PROCEDURE p2 (v IN OUT VARCHAR2) ISBEGINEXECUTE IMMEDIATE 'SELECT f (:b) FROM DUAL' INTO v USING pkg.x;END p2;

C. CREATE FUNCTION p4 (y pkg.tab_typ) RETURN pkg.tab_typ ISBEGINEXECUTE IMMEDIATE
'SELECT pdt_id, pdt_name FROM TABLE (:b)'BULT COLLECT INTO pkg.x USING y;RETURN pkg.x;END p4;

D. CREATE PROCEDURE p5 (y pkg. rec_typ) ISBEGINEXECUTE IMMEDIATE 'SELECT pdt_name FROM TABLE (:b)' BULK COLLECT INTO y USING pkg.x;END p5;

E. CREATE FUNCTION p3 RETURN pkg. tab_typ ISBEGINEXECUTE IMMEDIATE 'SELECT f (:b) FROM DUAL' INTO pkg.x;END p3;

Correct Answer: B,C

Question 3

Examine this code:

Assuming the default edition is ORA$BASE, what is the result of the last execution of hello?

A. Hello, edition 1.
Hello, edition 2.

B. Hello, edition 2.
Hello, edition 1.

C. Hello, edition 2.

D. Hello, edition 1.

E. An exception is raised.

Correct Answer: D

Question 4

Examine the incomplete code:

Which three lines of code must be added for it to successfully compile?

A. src_cur SYS_REFCURSOR;

B. src_cur= NUMBER;

C. curid := DBMS_SQL.TO_CURSOR_NUMBER (src_cur);

D. src_cur := DBMS_SQL.TO_REFCURSOR (curid);

E. curid NUMBER;

F. curid SYS_FEFCURSOR;

Correct Answer: A,D,E

Question 5

Examine these program units:

Which two blocks will execute successfully?

A. BEGINpkg1.proc1b;END;

B. BEGINpkg2.proc3;END;

C. BEGINpkg1.proc1a;END;

D. BEGINpkg2.proc2;END;

E. BEGINMy_proc;END;

Correct Answer: B,D

Question 6

Examine the EMPLOYEE_IDS table its data:

Examine this PL/SQL block:

What is the result of executing this PL/SQL block with SERVEROUTPUT enabled?

A. Compilation fails saying EMP_TAXID must be declared.

B. An exception is thrown at runtime saying EMP_TAXID is not visible.

C. It executes successfully and outputs:Fetched: 1011, JJONES,Fetched: 1012, SSMITH,

D. It executes successfully and outputs:Fetched: 1011, JJONES, 3789Fetched: 1012, SSMITH, -1

E. It executes successfully and outputs:Fetched: 1011, JJONES, -1Fetched: 1012, SSMITH, -1

Correct Answer: A

Question 7

Examine this Java method in class Employee, loaded into the Oracle database:
Public static int updateSalary (String name, float salary) {...}
Which PL/SQL specification can be used to publish this method?

A. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER)RETURN PLS_INTEGER AS LANGUAGE JAVANAME 'Employee.updateSalary (java.lang.String, float) return int';

B. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER)RETURN PLS_INTEGER AS LANGUAGE JAVALIBRARY "Employee" NAME "updateSalary"PARAMETERS (p_nm java.lang. String, p_sal float, RETURN int);

C. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER)RETURN PLS_INTEGER AS LANGUAGE JAVANAME 'int Employee.updateSalary (java.lang.String, float)';

D. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER)RETURN PLS_INTEGER AS LANGUAGE JAVANAME "Employee.updateSalary"PARAMETERS ("name" java.lang.String,
"salary" float, RETURN int);

E. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER)RETURN PLS_INTEGER AS LANGUAGE JAVANAME "Employee.updateSalary"PARAMETERS (p_nm java.lang.String, p_sal float, RETURN int);

Correct Answer: A

Question 8

Which statement describes the plshprof command-line utility?

A. It produces HTML output from raw profiler output.

B. It produces HTML output from hierarchical profiler tables in the database.

C. It stores hash values in hierarchical profiler tables in the database.

D. It produces raw profiler output for the most recently executed PL/SQL block.

E. It executes a script to create hierarchical profiler tables in the database.

Correct Answer: A

Question 9

Which is the correct method for releasing unused money after deleting elements of a collection?

A. Execute DBMS_SHARED_POOL.UNKEEP.

B. Use the TRUNCATE method on the collection.

C. Execute DBMS_SESSION.FREE_UNUSED_USER_MEMORY.

D. Flush the shared pool using the ALTER SYSTEM command.

Correct Answer: C

Question 10

Which statement is true about internal and external LOBs?

A. Internal and external LOBs can be written using DBMS_LOB.

B. After an exception transfers program control outside a PL/SQL block, all references to open external LOBs are lost.

C. A NOEXIST_DIRECTORY exception can be raised when using internal and external LOBs.

D. When using DBMS_LOB.INSTR for internal and external LOBs, DBMS_LOB.OPEN should be called for each LOB.

E. An external LOB can be loaded into an internal LOB variable using the DBMS_LOB package.

Correct Answer: B,D

Welcome to TestSimulate

Oracle Database: Advanced PL/SQL (1z0-148) Free Practice Test