Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 61:

  Examine the structure of the EMP table:

  Name

  Null?

  Type

  ——————

  ————–

  ———————

  EMPNO ENAME

  NOT

  NULL NUMBER (4)VARCHAR2 (10) SAL

  NUMBER (7, 2)

  Examine this code:

  

  Which code should be inserted to display the collection contents?

  A. 1_indx := 1_list.FIRST; WHILE (1_indx IS NOT NULL) LOOP DBMS_OUTPUT.PUT_LINE (1_indx || ‘ ’ || 1_list (1_indx)); 1_indx := 1_emp.NEXT (1_indx); END LOOP;

  B. FOR indx IN 1_list. COUNT .. -1 LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx)); END LOOP;

  C. FOR indx IN -1 .. 1_list.LIMIT LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx) ); END LOOP;

  D. FOR indx IN 1_list.FIRST . . 1_list.LAST LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx)); END LOOP;

  Correct Answer: C

• Question 62:

  Which two statements are true regarding edition-based redefinition (EBR)?

  A. There is no default edition defined in the database.

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

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

  D. Editions are non-schema objects.

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

  F. Tables are not editionable objects.

  Correct Answer: EF

  Reference: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#BABEHGAF

• Question 63:

  Examine the incomplete code:

  

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

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

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

  C. src_cur= NUMBER;

  D. curid NUMBER;

  E. curid SYS_FEFCURSOR;

  F. src_cur SYS_REFCURSOR;

  Correct Answer: BDF

• Question 64:

  Examine this code:

  

  The anonymous block fails with:

  ERROR at line 1:

  ORA-01403: no data found

  ORA-06512: at line 5

  Which two are valid options to prevent this error from occurring?

  A. Line 5 should be replaced with: DBMS_LOB.CREATETEMPORARY (pdatabuf (1), TRUE, DBMS_LOB.CALL);

  B. Line 5 should be replaced with: DBMS_LOB.CREATETEMPORARY (pdatabuf (1), FALSE, DBMS_LOB.SESSION);

  C. Rewrite the block as: DECLARE TYPE databuf_arr IS TABLE OF CLOB INDEX BY BINATY_INTEGER; pdatabuf databuf_arr; PROCEDURE mytemplob (x OUT CLOB) IS BEGIN DBMS_LOB.CREATETEMPORARY (x, TRUE, DBMS_LOB.SESSION); END; BEGIN mytemplob (pdatabuf (1)); END; /

  D. pdatabuf (1) := NULL; should be added after line 4.

  E. Line 5 should be replaced with: DBMS_LOB.CREATETEMPORARY (pdatabuf, TRUE, DBMS_LOB.SESSION);

  Correct Answer: CE

• Question 65:

  Examine these program units: Which two blocks will execute successfully?

  

  A. BEGIN My_proc; END;

  B. BEGIN pkg2.proc3; END;

  C. BEGIN pkg2.proc2; END;

  D. BEGIN pkg1.proc1a; END;

  E. BEGIN pkg1.proc1b; END;

  Correct Answer: BD

• Question 66:

  Examine this block of code used to calculate the price increase for all the productivity by 1% and then by 2%.

  

  What will be the outcome on execution?

  A. It will give an error because the calculated column in the cursor is not using a column alias in this block.

  B. It will go into an endless loop because the loop exist condition is missing.

  C. It will display the price increase by 1% only for all the products.

  D. It will display the price increase by 1% only for the first product.

  E. It will give an error because PDT_REC is not declared.

  Correct Answer: A

• Question 67:

  Examine this function body:

  BEGIN SELECT hire_date INTO date_hired FROM employees WHERE employee_id=emp_id;RETURN TO_CHAR(date_hired);

  END;

  Which two headers will allow this function to compile successfully and take advantage of both invoker's rights and function result caching?

  A. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (departments) AUTHID CURRENT_USER IS date_hired DATE;

  B. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE AUTHID CURRENT_USER IS date_hired DATE;

  C. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE AUTHID DEFINER IS date_hired DATE;

  D. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (employees) AUTHID CURRENT_USER IS date_hired DATE;

  E. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2 AUTHID DEFINER IS date_hired DATE;

  Correct Answer: BD

• Question 68:

  Refer to the Exhibit.

  

  Examine this procedure created in a session where PLSQL_OPTIMIZE_LEVEL =2:

  

  PL/SQL tracing in enabled in a user session using this command:

  EXEC DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.TRACE_ENABLED_LINES)

  The procedure is executed using this command:

  EXEC PRC_1

  Examine the exhibit for the content of the PLSQL_TRACE_EVENTS table.

  Why is tracing excluded from the PLSQL_TRACE_EVENTS table?

  A. DBMS_TRACE.TRACE_ENABLED_LINES traces only exceptions in subprograms.

  B. PRC_1 is not compiled with debugging information.

  C. Tracing is not enabled with the TRACE_ENABLED_CALLS option.

  D. PRC_1 is compiled with the default AUTHID DEFINER clause.

  E. Tracing will be enabled only for the second execution of PRC_1.

  Correct Answer: E

• Question 69:

  Which statement is true about internal and external LOBs?

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

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

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

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

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

  Correct Answer: E

  Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_lob.htm

• Question 70:

  Which two blocks of code execute successfully?

  A. DECLARE TYPE tab_type IS TABLE OF NUMBER; my_tab tab_type; BEGIN my_tab (1) :=1; END;

  B. DECLARE TYPE tab_type IS TABLE OF NUMBER; my_tab tab_type := tab_type(2); BEGIN my_tab(1) :=55; END;

  C. DECLARE TYPE tab_type IS TABLE OF NUMBER; my_tab tab_type; BEGIN my_tab. EXTEND (2); my_tab (1) := 55; END;

  D. DECLARE TYPE tab_type IS TABLE OF NUMBER; my_tab tab_type; BEGIN my_tab := tab_type (); my_tab (1) := 55; END;

  E. DECLARE TYPE tab_type IS TABLE OF NUMBER my_tab tab_type := tab_type (2, NULL, 50); BEGIN my_tab.EXTEND (3, 2); END;

  Correct Answer: BD