Oracle 1z0-148 Oracle Database 12c: Advanced Pl-SQL Exam Practice Test

SELECT DBMS_METADATA.GET_GRANTED_DDL ('SYSTEM_GRANT', 'my_user') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL ('ROLE_GRANT', 'my_user') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL ('OBJECT_GRANT', 'my_user') FROM dual;

SELECT DBMS_METADATA.GET_DDL ('USER', 'my_user') FROM dual;

The DBMS_SQL.TO_REFCURSOR function should be used when the number of bind variables is known at compile time, but not the number of items to be selected.

The DBMS_SQL.TO_REFCURSOR function converts a SQL cursor number to a weakly typed cursor variable

only after it has been opened, parsed, and executed via DBMS_SQL.

DBMS_SQL operations can access the cursor only as the SQL cursor number, not as a REF CURSOR

variable.

The DBMS_SQL.TO_REFCURSOR function allows a query result to be returned implicitly from a non PL/SQL environment to a stored PL/SQL program.

The DBMS_SQL.IS_OPEN function is used to verify that a SQL cursor number is open after it has been converted to a REF CURSOR.

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

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

Internal and external LOBs can be written using DBMS_LOB.

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

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

ALTER SESSION SET PLSQL_CCFLAGS = ‘mode: FALSE’;

ALTER SESSION SET PLSQL_CCFLAGS= ‘mode: NULL’;

ALTER SESSION SET PLSQL_CCFLAGS= ‘mode: Level 1’;

ALTER SESSION SET PLSQL_CCFLAGS= ‘mode: Level1’;

ALTER SESSION SET PLSQL_CCFLAGS= ‘mode: 1’

The PL/SQL block will fail with a runtime exception.

LENGTH : 100.13 EXTRA_LEN : 15.25 TOTAL LENGTH : 115.38

LENGTH : 100.1254 EXTRA_LEN : 15.247 TOTAL LENGTH : 115.37

LENGTH : 100.1254 EXTRA_LEN : 15.25 TOTAL LENGTH : 115.38

LENGTH : 100.1254 EXTRA_LEN : 15.25

TOTAL LENGTH : 115.3754

There is no default edition defined in the database.

EBR does not let you upgrade the database components of an application while in use.

You never use EBR to copy the database objects and redefine the copied objects in isolation.

Editions are non-schema objects.

When you change an editioned object, all of its dependents remain valid.

Tables are not editionable objects.

2, 1, 3 and 4

1, 2, 4 and 3

2, 1, 4 and 3

1, 2, 3 and 4

2, 4, 1 and 3

FOR idx IN list1.FIRST .. list1.LAST LOOP

IF list1.EXISTS(idx) THEN

list2(idx) := list1(idx);

END IF;

END LOOP;

FOR idx IN list1.FIRST .. list1.LAST LOOP

list2(idx) := list1(idx);

END LOOP;

idx:= list1.FIRST;

WHILE (idx IS NOT NULL) LOOP

list2(list2.COUNT + 1) := list1(idx);

idx := list1.NEXT(idx);

END LOOP;

FORALL idx IN list1.FIRST ..list1.LAST LOOP

list2(list2.COUNT + 1) := list1(idx);

idx:= list1.FIRST; LOOP

EXIT WHEN idx IS NULL;

list2(list2.COUNT + 1) := list1(idx);

idx := list1.NEXT(idx);

END LOOP;

CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IStotal NUMBER :=0;BEGIN…RETUNRN total;END;/

CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IStotal NUMBER :=0;BEGIN…RETUNRN total;END;/

CREATE FUNCTION totalEmp (sal IN PLS_INTEGER) RETURN NUMBER IStotal NUMBER :=0;BEGIN…RETUNRN total;END;/

CREATE FUNCTION totalEmp (sal IN BINARY_FLOAT) RETURN NUMBER IStotal NUMBER :=0;BEGIN…RETUNRN total;END;/

CREATE FUNCTION totalEmp (sal IN POSITIVEN) RETURN NUMBER IStotal NUMBER :=0;BEGIN…RETUNRN total;END;/

DECLARE

SUBTYPE new_one IS BINARY_INTERGER RANGE 0..9; my_val new_one; BEGIN my_val :=0; END;

DECLARE

SUBTYPE new_string IS VARCHAR2 (5) NOT NULL; my_str_new_string; BEGIN my_str := ‘abc’;

END;

DECLARE

SUBTYPE new_one IS NUMBER (2, 1); my_val new_one; BEGIN my_val :=12.5; END;

DECLARE

SUBTYPE new_one IS INTEGER RANGE 1..10 NOT NULL; my_val new_one; BEGIN my_val :=2; END;

DECLARE

SUBTYPE new_one IS NUMBER (1, 0); my_val new_one; BEGIN my_val := -1;

END;

CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2RESULT_CACHE RELIES_ON (departments)AUTHID CURRENT_USERISdate_hired DATE;

CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2RESULT_CACHEAUTHID CURRENT_USERISdate_hired DATE;

CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2RESULT_CACHEAUTHID DEFINERISdate_hired DATE;

CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2RESULT_CACHE RELIES_ON (employees)AUTHID CURRENT_USERISdate_hired DATE;

CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2AUTHID DEFINERISdate_hired DATE;

DBMS_LOB can be used to compress SecureFiles columns.

When using DBMS_DATAPUMP, if SecureFiles LOB data needs to be encrypted then ENCRYPTION=ALL must be specified.

If a BasicFiles LOB locator is passed to DBMS_LOB.ISSECUREFILE, an exception will be raised.

An online redefinition of SecureFiles by DBMS_REDEFINIITON can be performed with PDML (Parallel DML).

Change RETURN p_tab.LAST to RETURN p_tab.COUNT.

Declare the collection type inside the function.

Declare the collection type at the schema level instead of the package.

Define the function as stand-alone instead of in a package body.

Change the INDEX BY clause from VARCHAR2 to PLS_INTEGER.

Modify the function return type to return a scalar, VARCHAR2.

The amount parameter can be set to NULL to indicate to load to the end of the BFILE.

The amount parameter can be set to DBMS_LOB.LOBMAXSIZE to indicate to load to the end of the BFILE.

If any error occurs when calling the procedure then it is written to the warning parameter and

DBMS_LOB.LOADCLOBFROMFILE finishes successfully.

An exception will always be raised if amount plus src_offset exceeds the length of the BFILE.

The IN parameters must not include BLOB, CLOB, collection or record data types.

The function must be created with invoker’s rights or in an anonymous block.

The function must be declared as a pipelined table function.

The function must have an OUT or an IN OUT parameter.

It executes successfully only if v_ref_cur := DBMS_SQL.TO_REFCURSOR (V_CUR); is added before the FETCH statement.

It executes successfully.

It executes successfully only if v_ref_cur: = DBMS_SQL.TO_CURSOR_NUMBER (v_cur); is added before the FETCH statement.

It executes successfully only if the FETCH statement is replaced by DBMS_SQL.RETURN_RESULT (v_ref_cur);

It executes successfully only if the FETCH statement is replaced by DBMS_SQL.FETCH_ROWS (v_cur);