Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Jan Oracle Official New Released 1z0-146
100% Free Download! 100% Pass Guaranteed!
http://www.Dumps4cert.com/1z0-146.html

Oracle 11g: Advanced PL/SQL

Question No: 71 – (Topic 1)

Examine the following structure:

SQLgt; DESCRIBE user_identifiers

Name Null? Type

——————— —- ———— NAME VARCHAR2(30)

SIGNATURE VARCHAR2(32)

TYPE VARCHAR2(18)

OBJECT_NAME NOT NULL VARCHAR2(30)

OBJECT_TYPE VARCHAR2(13)

USAGE VARCHAR2(11)

USAGE_ID NUMBER

LINE NUMBER

COL NUMBER

USAGE_CONTEXT_ID NUMBER

Identify two scenarios in which information is stored in the USAGE column. (Choose two.)

  1. an assignment made to VARIABLE

  2. declaration of a variable or formal parameter

  3. an identifier passed to a subprogram in IN OUT mode

  4. execution of the GOTO statement or raise of an exception

Answer: A,B

Question No: 72 – (Topic 1)

Examine the following settings for a session: PLSQL_CODE_TYPE = NATIVE

View the Exhibit and examine the PL/SQL code.

You compile the program with the following attributes:

SQLgt; ALTER PROCEDURE proc1 COMPILE PLSQL_OPTIMIZE_LEVEL = 1;

Which statement is true about the execution of the PROC1 procedure in this scenario?

Dumps4Cert 2018 PDF and VCE

  1. The FUNC1 function would be called inline because PRAGMA INLINE forces a specific call to be inlined.

  2. The FUNC1 function would be inlined because the value set for the PLSQL_CODE_TYPE parameter is set to NATIVE.

  3. The FUNC1 function would be called inline irrespective of the value set for the PLSQL_OPTIMIZE_LEVEL parameter.

  4. The FUNC1 function would not be called inline because the value for the PLSQL_OPTIMIZE_LEVEL parameter is set to a lower value.

Answer: D

Question No: 73 – (Topic 1)

The following command is executed to start tracing in a user session:

SQLgt; EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS)

;

Which statement is true about the effect of the command?

  1. It allows tracing of exceptions raised by all subprograms executed in the session.

  2. It allows tracing of user-defined exceptions raised by all subprograms executed in the session.

  3. It allows tracing of system-defined exceptions raised by all subprograms executed in the session.

  4. It allows tracing of exceptions raised by subprograms compiled with the DEBUG option and executed in the session.

Answer: D

Question No: 74 – (Topic 1)

You executed this command to gather information about the memory allocation for storing query results:

SQLgt; execute dbms_result_cache.memory_report

View the Exhibit and examine the output for the execution of the DBMS_RESULT_CACHE.MEMORY_REPORT procedure.

Which two statements are true about the output in the Exhibit? (Choose two.)

Dumps4Cert 2018 PDF and VCE

  1. In total, four blocks are used by the result cache.

  2. Currently 52 KB is allocated to the result cache.

  3. Currently 32 KB is allocated to the result cache.

  4. The result cache can be increased by 65628 bytes.

Answer: C,D

Question No: 75 – (Topic 1)

The user OE is working on an application that needs to call an external C program multiple times in a single session. However, the extproc.exe file on the server gets accidentally deleted after the OE user connected and made calls to the external C program. Which statement is true about the current session by the OE user?

  1. The session can continue calling the external C program.

  2. The session can call the external C program after republishing it.

  3. The session receives an error for the next call to the external C program.

  4. The session terminates during the subsequent call to the external C program.

Answer: A

Question No: 76 – (Topic 1)

View the Exhibit and examine the PL/SQL code.

Identify the sections of the code that need to be modified for better performance gains. (Choose all that apply.)

Dumps4Cert 2018 PDF and VCE

  1. Use cursors to fetch values.

  2. Remove redundant SQL statements in the procedures.

  3. Avoid the NOT NULL constraint when declaring the variable.

  4. Define and compile procedures independently, and not as part of a package.

Answer: B,C

Question No: 77 – (Topic 1)

Which two statements are true about BFILES? (Choose two.)

  1. BFILES support only sequential reads.

  2. BFILES can be used as attributes in an object type.

  3. When LOB is deleted, the corresponding physical file is automatically deleted.

  4. The RMAN backup automatically backs up the BFILE locators and the corresponding physical files.

  5. The physical file corresponding to a BFILE locator must reside on the file system that is accessible from the server where the database exists.

Answer: B,E

Question No: 78 – (Topic 1)

View the Exhibit and examine the structure of the EMPLOYEES table.

Examine the following PL/SQL block for storing the salary of all sales representatives from the EMPLOYEES table in an associative array:

  1. DECLARE

  2. emp_cv SYS_REFCURSOR;

  3. TYPE list IS TABLE OF emp_cv;

  4. sals list;

  5. BEGIN

  6. OPEN emp_cv FOR SELECT salary FROM employees

  7. WHERE job_id = #39;SA_REP#39;;

  8. FETCH emp_cv BULK COLLECT INTO sals;

  9. CLOSE emp_cv; 10 END;

What should you correct in the above code to ensure that it executes successfully?

Dumps4Cert 2018 PDF and VCE

  1. Replace EMP_CV in line 3 with employees.salary%TYPE.

  2. Replace line 2 with TYPE refcur IS REF CURSOR; emp_cv refcur;.

  3. Replace BULK COLLECT in line 8 with the OPEN, FETCH, LOOP, and CLOSE statements.

  4. Replace line 2 with TYPE refcur IS REF CURSOR RETURN employees.salary%TYPE; emp_cv refcur;.

Answer: A

Question No: 79 – (Topic 1)

Which two statements are true about the DBMS_LOB.CREATETEMPORARY procedure that is used to create a temporary LOB? (Choose two.)

  1. It can be used for transforming data in permanent internal LOBs.

  2. It is used only for the migration of BasicFile to the SecureFile format.

  3. It is used only for the migration of the LONG column to the LOB column.

  4. It creates a LOB variable that is not associated with any table and is stored in the user#39;s temporary tablespace.

  5. It creates a LOB variable that is associated with a specific table and is temporarily stored in the user#39;s default tablespace.

Answer: A,D

Question No: 80 – (Topic 1)

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

Examine the PL/SQL block that you execute to find the average salary for employees in the #39;Sales#39; department:

DECLARE

TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20); v_emp_sal emp_sal;

PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS BEGIN

SELECT AVG(salary) INTO p_arr(p_dept_name) FROM employees WHERE department_id= (SELECT department_id FROM departments

WHERE department_name=p_dept_name);

END get_sal; BEGIN

get_sal(#39;Sales#39;,v_emp_sal); DBMS_OUTPUT.PUT_LINE( v_emp_sal(#39;Sales#39;));

END;

/

What is the outcome?

Dumps4Cert 2018 PDF and VCE

  1. It executes successfully and gives the correct output.

  2. It generates an error because the associative array definition is not valid.

  3. It generates an error because an associative array cannot be passed to a procedure in OUT mode.

  4. It generates an error because an associative array cannot be used with the SELECT INTO statement.

Answer: A

100% Dumps4cert Free Download!
Download Free Demo:1z0-146 Demo PDF
100% Dumps4cert Free Guaranteed!
1z0-146 Dumps

Dumps4cert ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Comments are closed.