Free 1z0-148: Oracle Database 12c Advanced PLSQL Exam Questions and Answers

Examine this block: DECLARE TYPE t_va IS VARRAY(5) OF NUMBER; v t_va := t_va(10, 20, 30); BEGIN v.EXTEND(2); v(5) := 50; v.EXTEND; DBMS_OUTPUT.PUT_LINE(v.COUNT); END; What is the outcome?

• AA. The block fails with ORA-06532: Subscript outside of limit.
• BB. The block displays 6.
• CC. The block fails with ORA-06533: Subscript beyond count.
• DD. The block displays 5.

Examine this block: DECLARE TYPE t_inner IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE t_outer IS TABLE OF t_inner INDEX BY PLS_INTEGER; v t_outer; BEGIN v(1)(1) := 10; v(1)(2) := 20; v(2)(5) := 30; DBMS_OUTPUT.PUT_LINE(v.COUNT || ',' || v(1).COUNT || ',' || v(2).COUNT); END; What is the result?

• AA. 2,2,5
• BB. 3,2,1
• CC. Compilation fails.
• DD. 2,2,1

A developer must call a C function in a shared library from a PL/SQL function. Which two are required? (Choose two.)

Select 2 answers.

• AA. A library object created with CREATE LIBRARY that maps an alias to the shared library file.
• BB. A database link to the extproc agent created before the call specification is compiled.
• CC. A call specification that uses LANGUAGE C with LIBRARY and NAME clauses.
• DD. A relink of the Oracle executable so that the C function is linked into the database server.

You want to add the RESULT_CACHE clause to a function that returns a currency exchange rate. Which function declaration compiles successfully?

• AA. FUNCTION get_rate (p_code IN VARCHAR2, p_status OUT VARCHAR2) RETURN NUMBER RESULT_CACHE
• BB. FUNCTION get_rate (p_notes IN CLOB) RETURN NUMBER RESULT_CACHE
• CC. FUNCTION get_rate (p_code IN VARCHAR2) RETURN SYS_REFCURSOR RESULT_CACHE
• DD. FUNCTION get_rate (p_code IN VARCHAR2) RETURN NUMBER RESULT_CACHE

A procedure is compiled with PLSQL_OPTIMIZE_LEVEL set to 2. The procedure calls the local function get_tax in three statements. Only one of those statements is immediately preceded by PRAGMA INLINE (get_tax, 'YES'). What is the result?

• AA. The pragma is ignored because inlining takes place only at level 3.
• BB. All three calls to get_tax in the procedure are inlined.
• CC. Only the call in the statement that immediately follows the pragma is inlined.
• DD. Compilation fails because the pragma requires PLSQL_OPTIMIZE_LEVEL to be 3.

Which DBMS_LOB subprogram adds the entire contents of a source LOB to the end of a destination LOB?

• AA. APPEND
• BB. COPY
• CC. WRITEAPPEND
• DD. LOADFROMFILE

A company is upgrading the HR_APP schema online by using edition-based redefinition. The application is running in the ORA$BASE edition while the DBA prepares a new child edition, V2. Which two statements are true about this upgrade? (Choose two.)

Select 2 answers.

• AA. A forward crossedition trigger fires for DML issued by sessions that use the post-upgrade edition.
• BB. After ALTER USER hr_app ENABLE EDITIONS is issued, the user cannot be returned to a non-editions-enabled state.
• CC. An editioning view must be based on a single table and can rename columns but cannot contain a WHERE clause.
• DD. A table that is altered while the session uses edition V2 exists as a separate copy of the table for that edition.

CREATE TYPE num_tab IS TABLE OF NUMBER; / DECLARE t num_tab := num_tab(10, 20, 30, 40); BEGIN t.DELETE(2); t.EXTEND; t(t.LAST) := 50; DBMS_OUTPUT.PUT_LINE(t.COUNT || ' ' || t.LAST || ' ' || t.NEXT(1)); END; / What is the result?

• AA. An exception is thrown at runtime.
• BB. 4 5 3
• CC. 5 5 3
• DD. 4 5 2

A developer runs the following as the application owner: CREATE OR REPLACE LIBRARY calc_lib AS '/u01/app/clib/libcalc.so'; / CREATE OR REPLACE FUNCTION add_nums(a PLS_INTEGER, b PLS_INTEGER) RETURN PLS_INTEGER AS LANGUAGE C LIBRARY calc_lib NAME "add_nums"; / Both statements succeed. The listener has a working EXTPROC1 entry, and other external procedures that use libraries in $ORACLE_HOME/lib run correctly. The call SELECT add_nums(1, 2) FROM dual fails with ORA-28595: Extproc agent: Invalid DLL Path. What should the DBA do?

• AA. Set PLSQL_NATIVE_LIBRARY_DIR to /u01/app/clib and restart the instance
• BB. Grant CREATE ANY LIBRARY to the owner of the ADD_NUMS function
• CC. Grant EXECUTE on the library CALC_LIB to the user who calls the function
• DD. Add /u01/app/clib/libcalc.so to the EXTPROC_DLLS setting of the extproc agent

The class Hasher, which contains the static method public static String compute(String in), has been loaded into the database with loadjava. A developer must publish the method to PL/SQL as a stored function named GET_HASH that takes a VARCHAR2 and returns a VARCHAR2. Which call specification is correct?

• AA. CREATE FUNCTION get_hash(p_in VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Hasher.compute(java.lang.String) : java.lang.String';
• BB. CREATE FUNCTION get_hash(p_in VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Hasher.compute(String) return String';
• CC. CREATE FUNCTION get_hash(p_in VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Hasher.compute(java.lang.String) return java.lang.String';
• DD. CREATE FUNCTION get_hash(p_in VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA LIBRARY Hasher NAME 'compute';