Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 41:

  Which tablespace is used to store the data collected by PL/Scope?

  A. UNDOTBS1

  B. SYSAUX

  C. SYSTEM

  D. TEMP

  E. USERS

  Correct Answer: B

  Reference: https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm#BABDGJAF

• Question 42:

  Examine the test_tbl table and its contents:

  

  Examine this code:

  

  What is the outcome of this anonymous PL/SQL block?

  A. "It was updated" is displayed.

  B. Successful completion without printing "It was updated".

  C. A NO_DATA_FOUND exception is thrown.

  D. ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified

  E. ORA-22920: row containing the LOB value is not locked

  Correct Answer: D

• Question 43:

  Examine these statements:

  

  Which two corrections will allow this anonymous block to execute successfully?

  A. Add wk# .NEXT; before the 7thline.

  B. Add i PLS_INTEGER; before the 3rdline.

  C. Add wk#. EXTEND (1); before the 5thline.

  D. Change line #2 to wk# tp_test# := tp_test# (tp_rec# ());

  E. Replace lines 5 and 6 with wk# (i) := tp_rec# (i, i);

  Correct Answer: CE

• Question 44:

  Examine the structure and data in the CUSTOMERS table:

  Name

  Null?

  Type

  -------------

  -----------------

  ----------------

  CUST ID

  NOT NULL

  NUMBER

  LAST_NAME DEPTNO

  NOT NULL NOT NULL

  VARCHAR2(20)NUMBER

  CUST ID

  LAST_NAMM

  DEPTNO

  -------------

  -----------------

  ----------------

  1 1

  RogersSmith

  10 20

  1

  Walters

  20

  CREATE FUNCTION (cust_policy_fn(v_schema IN VARCHAR2, v_objname IN VARCHAR2)RETURN VARCHAR2 AS

  con VARCHAR2(200);BEGIN

  con: = 'depno' = '10';

  RETURN con; END cust_policy_fn; /BEGIN

  DBMS_RLS.ADD_POLICY(

  object_schema=>'scott',

  object_name=>'customers',

  policy_name=>'cus_policy',

  policy_function=>'cus_policy_fn',

  sec_relevant_cols=>'LAST_NAME',

  sec_relevant_Cols_opt=>DBMS_RLS.ALL_ROWS);

  END; /

  Examine this code:

  DECLARE TYPE emp_type IS TABLE OF customers% ROWTYPE INDEX BY

  customers.last_name%TYPE; v_customers emp_type;CURSOR c1 IS SELECT*FROM customers; cnt NUMBER;

  BEGIN

  FOR cust_rec IN c1 LOOP

  v_customers(cust_rec.last_name):=cust_rec;

  cnt:=c1%ROWCOUNT;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('RowCount:'||cnt);

  DBMS_OUTPUT.PUT_LINE('Total:' ||v_customers.COUNT);

  END;

  What is the outcome on execution with SERVEROUTPUT enabled?

  A. It throws a NO_DATA_FOUND exception.

  B. It displays 3 for both the row count and the total.

  C. It throws a PL/SQL: numeric or value error: NULL index table key value exception.

  D. It displays 3 for the row count and 2 for the total.

  Correct Answer: B

• Question 45:

  Examine this code:

  CREATE PROCEDURE list_products_dynamic(p_product_name VARCHAR2 DEFAULT NULL) AS

  TYPE cv_pordtyp IS REF CUSRSOR;

  cv cv_prodtyp;

  v_prodname prod_info.name%TYPE;

  v_listprice prod_info.price%TYPE;BEGIN

  OPEN cv FOR 'SELECT name, price FROM prod_info WHERE name LIKE "%' ||p_product_name || '%'";

  LOOP

  FETCH cv INFO v_prodname, v_listprice;

  EXIT WHEN cv%NOTFOUND;

  DBMS_OUTPU.PUT_LINE ('Product Info:'||v_prodname||','||v_listprice);

  END LOOP;

  CLOSE cv; END

  Which two are valid correlations to the code to avoid or mitigate SQL Injection?

  A. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp; v_prodname prod_info.name%TYPE; v_listprice prod_info.price%TYPE; v_bind VARCHAR2 (400); BEGIN v_bind := ‘%’ | | p_product_name | | ‘%’; OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE :b’ USING v_bind; LOOP FETCH cv INTO v_prodname, v_listprice; EXIT WHEN cv%NOTFOUND; DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv; END;

  B. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS v_bind VARCHAR2 (400); BEGIN v_bind := ‘%’ | | p_prodname | | ‘%’; FOR rec IN (‘SELECT name, price FROM prod_info WHERE name like ‘ | | v_bind) LOOP DBMS_OUTPUT.PUT_LINE (‘Product Info: ’ | | rec.name | | ‘,’ | | rec.price); END LOOP; END;

  C. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp; v_prodname prod_info.name%TYPE; v_listprice prod_info.price%TYPE; v_bind VARCHAR2 (400); BEGIN v_bind := ’’’%’ | | p_product_name | | ‘%’’’; OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE ’ | | v_bind; LOOP

  FETCH cv INTO v_prodname, v_listprice;

  EXIT WHEN cv%NOTFOUND;

  DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice);

  END LOOP;

  CLOSE cv;

  END;

  D. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp; v_prodname prod_info.name%TYPE; v_listprice prod_info.price%TYPE; v_bind VARCHAR2 (400); BEGIN v_bind := ‘%’ | | p_product_name | | ‘%’; OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE ’ | | v_bind; LOOP FETCH cv INTO v_prodname, v_listprice; EXIT WHEN cv%NOTFOUND; DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv; END;

  E. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp; v_prodname prod_info.name%TYPE; v_listprice prod_info.price%TYPE; v_bind VARCHAR2 (400); BEGIN v_bind := DBMS_ASSERT.ENQUOTE_LITERAL (‘%’ | | p_product_name | | ‘%’); OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE ’ | | v_bind; LOOP FETCH cv INTO v_prodname, v_listprice; EXIT WHEN cv%NOTFOUND; DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv; END;

  Correct Answer: BD

• Question 46:

  Which two can be used to find details of parameters for overloaded PL/SQL routines?

  A. ALL-DEPENDENCIES

  B. ALL_PROCEDURES

  C. ALL_DESCRIBE

  D. ALL_SOURCE

  E. ALL_ARGUMENTS

  Correct Answer: DE

  Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1014.htm#REFRN20015

• Question 47:

  Select a valid reason for using VARRAYS.

  A. When the amount of data to be held in the collection is widely variable.

  B. As a column in a table when you want to retrieve the collection data for certain rows by ranges of values.

  C. When you want to delete elements from the middle of the collection.

  D. As a column in a table when you want to store no more than 10 elements in each row's collection.

  Correct Answer: B

  Reference https://www.go4expert.com/articles/oracle-nested-tables-varrays-t20494/

• Question 48:

  

  Examine this code:

  

  You want to display the contents of CREATE_LIST.

  Which two lines need to be corrected in the PL/SQL block?

  A. Line 2

  B. Line 3

  C. Line 5

  D. Line 6

  E. Line 7

  Correct Answer: AE

• Question 49:

  Examine this code executed in the ORA1 schema:

  

  Examine this code executed by DBA_USER who has been granted the DBA role:

  REVOKE INHERIT PRIVILEGES ON USER dba_user FROM PUBLIC;

  Examine this query:

  SELECT return_date (1) FROM dual;

  What is the result of executing this query in the DBA_USER schema?

  A. It will fail with a compile-time error.

  B. It will execute successfully and return the date but the DBA role will not be granted to ORA1.

  C. It will fail with a runtime error complaining of insufficient INHERIT PRIVILEGES.

  D. It will execute successfully, return the date and the DBA role will be granted to ORA1.

  Correct Answer: D

• Question 50:

  Select the correct statement regarding BEQUEATH CURRENT_USER.

  A. If a view references a PL/SQL function then BEQUEATH CURRENT_USER allows the function to

  B. execute with DBA privileges, regardless of the invoking user's privileges.

  C. The BEQUEATH CURRENT_USER clause allows invoker's rights functions referenced in a view to execute with the privileges of the invoking user.

  D. Any view calling a PL/SQL function with BEQUEATH CURRENT_USER in effect will execute with the privileges of the function owner.

  E. With the BEQUEATH CURRENT_USER clause, a definer's rights function referenced in a view executes with the privileges of the view owner, not the function owner.

  Correct Answer: B

  Reference: https://docs.oracle.com/database/121/DBSEG/dr_ir.htm#DBSEG558