Oracle 1Z0-148 Online Practice Questions and Exam Preparation

Oracle 1Z0-148 Online Questions & Answers

• Question 151:

  View the Exhibit and examine the settings for the PLSQL_CODE_TYPE parameter.

  After sometime, the user recompiles the procedure DISPLAY_SAL_INFO by issuing the following command:

  SQL> ALTER PROCEDURE display_sal_info COMPILE;

  Which statement would be true in this scenario?

  

  A. The procedure would be invalidated.
  B. The procedure would remain as NATIVE code type.
  C. The procedure would be changed to INTERPRETED code type.
  D. The command would produce an error and the procedure must be compiled using the PLSQL_CODE_TYPE attribute with value INTERPRETED.
  C. The procedure would be changed to INTERPRETED code type.

• Question 152:

  You issue the following command to create the PRINT_MEDIA table.

  CREATE TABLE print_media

  (product_id NUMBER(3),

  ad_sourcetext CLOB,

  ad_photo BLOB);

  Evaluate the following INSERT statements:

  INSERT INTO print_media VALUES (1, empty_clob(),empty_blob());

  INSERT INTO print_media VALUES (2,'This is a One Line Story',null);

  INSERT INTO print_media VALUES (3,'This is another One Line Story',empty_blob());

  INSERT INTO print_media VALUES (4,empty_clob(),to_blob('This is new Story')); Which of the above INSERT statements are valid?

  A. Only the first statement is valid.
  B. All the statements are valid.
  C. Only the first and fourth statements are valid.
  D. Only the first and second statements are valid.
  E. Only the first, second and third statements are valid.
  E. Only the first, second and third statements are valid.

• Question 153:

  Which codes executes successfully?

  A. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); PROCEDURE calc_price (price_rec IN OUT rec_typ); END pkg; / CREATE PACAKGE BODY pkg AS PROCEDURE calc_price (price_rec IN OUT rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END calc_price; END pkg; / DECLARE 1_rec pkg. rec_typ; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50; EXECUTE IMMEDIATE ‘BEGIN pkg. calc_price (:rec); END;’ USING IN OUT 1_rec; END;
  B. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); END pkg; / CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END / DECLARE 1_rec pkg.rec_typ; BEGIN EXECUTE IMMEDIATE ‘BEGIN calc_price (:rec); END;’ USING IN OUT 1_rec (100, 50); END;
  C. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); END pkg; / CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END ; / DECLARE 1_rec pkg. rec_typ; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50; EXECUTE IMMEDIATE ‘BEGIN calc_price (1_rec); END;’; END;
  D. DECLARE TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); 1_rec rec-typ; PROCEDURE calc_price (price_rec IN OUT rec_typ) AS BEGIN price_rec.price := price-rec.price+ (price_rec.price * price_rec.inc_pct)/100; END; BEGIN 1_rec_price :=100; 1_rec.inc_pct :=50; EXECUTE IMMEDIATE ‘BEGIN calc_price (:rec); END;’ USING IN OUT 1_rec; END;
  B. CREATE PACKAGE pkg AS TYPE rec_typ IS RECORD (price NUMBER, inc_pct NUMBER); END pkg; / CREATE PROCEDURE calc_price (price_rec IN OUT pkg. rec_typ) AS BEGIN price_rec.price := price_rec.price + (price_rec.price * price_rec.inc_pct)/100; END / DECLARE 1_rec pkg.rec_typ; BEGIN EXECUTE IMMEDIATE ‘BEGIN calc_price (:rec); END;’ USING IN OUT 1_rec (100, 50); END;

• Question 154:

  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.
  E. Tracing will be enabled only for the second execution of PRC_1.

• Question 155:

  Consider a function totalEmp () which takes a number as an input parameter and returns the total number of employees who have a salary higher than that parameter.

  Examine this PL/SQL package AS

  

  Which two definitions of totalEmp () result in an implicit conversion by Oracle Database on executing this PL/SQL block?

  A. CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /
  B. CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /
  C. CREATE FUNCTION totalEmp (sal IN PLS_INTEGER) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /
  D. CREATE FUNCTION totalEmp (sal IN BINARY_FLOAT) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /
  E. CREATE FUNCTION totalEmp (sal IN POSITIVEN) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /
  B. CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /
  C. CREATE FUNCTION totalEmp (sal IN PLS_INTEGER) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /

• Question 156:

  Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.

  CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,

  deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),

  salary Number(8),

  deptname VARCHAR2(30) DEFAULT 'Sales')

  /

  DECLARE

  SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;

  v_emprec v_emprec_subtype;

  BEGIN

  v_emprec.empid := NULL; v_emprec.salary := 10000.002;

  v_emprec.deptid := 50;

  DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname);

  END;

  /

  Which statements are true about the above PL/SQL block? (Choose two.)

  A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.
  B. Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited.
  C. Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal.
  D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.
  A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.
  D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.

• Question 157:

  The STUDENTS table exists in your schema.

  Examine the DECLARE section of a PL/SQL block:

  DECLARE TYPE studentcur_t IS REF CURSOR RETURN students%ROWTYPE; TYPE teachercur_t IS REF CURSOR:

  cursor1 studentcur_t;

  cursor2 teachercur_t;

  cursor3 SYS_REFCURSOR;

  CURSOR stcur IS SELECT * FROM students;

  Which two blocks are valid?

  A. BEGIN OPEN cursor3 FOR SELECT * FROM students; cursor1 :=cursor3; END;
  B. BEGIN OPEN stcur; cursor1 :=stcur; END;
  C. BEGIN OPEN cursor1 FOR SELECT * FROM students; stcur :=cursor1; END;
  D. BEGIN OPEN stcur; cursor3 :=stcur; END;
  E. BEGIN OPEN cursor1 FOR SELECT * FROM students; cursor2 :=cursor1; END;
  A. BEGIN OPEN cursor3 FOR SELECT * FROM students; cursor1 :=cursor3; END;
  E. BEGIN OPEN cursor1 FOR SELECT * FROM students; cursor2 :=cursor1; END;

• Question 158:

  Which two statements are true about REF CURSOR types? (Choose two.)

  A. REF CURSOR types cannot be defined inside a package.
  B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
  C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a user- defined record.
  D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.
  B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
  C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a user- defined record.

• Question 159:

  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.
  E. When using DBMS_LOB.INSTR for internal and external LOBs, DBMS_LOB.OPEN should be called for each LOB.

  ---

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

• Question 160:

  The STUDENTS table with column LAST_NAME of data type VARCHAR2 exists in your database schema. Examine this PL/SQL block:

  

  Which two actions must you perform for this PL/SQL block to execute successfully?

  A. Replace the FOR loop with FOR name_rec IN names_varray.FIRST .. names_varray.LAST LOOP.
  B. Replace the L_NAME_TYPE declaration with TYPE 1_name_type IS VARRAY (25) OF SYS_REFCURSOR;
  C. Add name_rec name_cur%ROWTYPE; at the end of the DECLARE section.
  D. Replace the NAMES_VARRAY declaration with names_varray 1_name_type := 1_name_type ();
  E. Replace the NAMES_VARRAY declaration with names_varray 1_name_type := null;
  F. Add names_varray.EXTEND after the FOR ...LOOP statement.
  E. Replace the NAMES_VARRAY declaration with names_varray 1_name_type := null;
  F. Add names_varray.EXTEND after the FOR ...LOOP statement.