Oracle 1z1-148 : Oracle Database: Advanced PL/SQL

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 compiled with the default AUTHID DEFINER clause.
• C. Tracing will be enabled only for the second execution of PRC_1.
• D. PRC_1 is not compiled with debugging information.
• E. Tracing is not enabled with the TRACE_ENABLED_CALLS option.

Examine this code:

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) ASTYPE cv_pordtyp IS REF CURSOR;cv cv_prodtyp;v_prodname prod_info.name%TYPE;v_listprice prod_info.price%TYPE;v_bind VARCHAR2 (400);BEGINv_bind := DBMS_ASSERT.ENQUOTE_LITERAL ('%' | | p_product_name | | '%');OPEN cv FOR 'SELECT name, price FROM prod_info WHERE name LIKE ' | | v_bind;LOOPFETCH 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) ASTYPE cv_pordtyp IS REF CURSOR;cv cv_prodtyp;v_prodname prod_info.name%TYPE;v_listprice prod_info.price%TYPE;v_bind VARCHAR2 (400);BEGINv_bind := '%' | | p_product_name | |
  '%';OPEN cv FOR 'SELECT name, price FROM prod_info WHERE name LIKE :b' USING v_bind;LOOPFETCH 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;
• C. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) ASv_bind VARCHAR2 (400);BEGINv_bind := '%' | | p_prodname | | '%';FOR rec IN ('SELECT name, price FROM prod_info WHERE name like ' | | v_bind) LOOPDBMS_OUTPUT.PUT_LINE ('Product Info: ' | | rec.name | | ',' | | rec.price);END LOOP;END;
• D. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) ASTYPE cv_pordtyp IS REF CURSOR;cv cv_prodtyp;v_prodname prod_info.name%TYPE;v_listprice prod_info.price%TYPE;v_bind VARCHAR2 (400);BEGINv_bind := '%' | | p_product_name | |
  '%';OPEN cv FOR 'SELECT name, price FROM prod_info WHERE name LIKE ' | | v_bind;LOOPFETCH 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) ASTYPE cv_pordtyp IS REF CURSOR;cv cv_prodtyp;v_prodname prod_info.name%TYPE;v_listprice prod_info.price%TYPE;v_bind VARCHAR2 (400);BEGINv_bind := '''%' | | p_product_name | |
  '%''';OPEN cv FOR 'SELECT name, price FROM prod_info WHERE name LIKE ' | | v_bind;LOOPFETCH 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;

Examine this function:

Execute the query:
SELECT remap_schema FROM dual;
Which is the correct output from the query?

• A. CREATE TABLE "EMP" ("EMPNO" NUMBER (4, 0), "ENAME" VARCHAR2 (10), "JOB" VARCHAR2 (9), "MGR" NUMBER (4, 0), "HIREDATE" DATE, "SAL" NUMBER (7, 2), "COMM" NUMBER (7, 2), "DEPTNO" NUMBER (2, 0),CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING INDEX ENABLE,CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE)
• B. CREATE TABLE "EMP" ("EMPNO" NUMBER (4,0), "ENAME" VARCHAR2 (10), "JOB" VARCHAR2 (9), "MGR" NUMBER (4,0), "HIREDATE"DATE, "SAL" NUMBER (7,2) , "COMM" NUMBER (7,2), "DEPTNO" NUMBER (2,0),CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL
  65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2417483645PCTINCREASE 0 FREELISTS
  1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CHACHE DEFAULT
  CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ENABLE,CONSTRAINT
  "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE)
  SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
  255NOCOMPRESS LOGGINGSTORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1
  MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
  1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
  DEFAULT)TABLESPACE "USERS"
• C. CREATE TABLE "SCOTT". "EMP" ("EMPNO" NUMBER (4, 0), "ENAME" VARCHAR2 (10),
  "JOB" VARCHAR2 (9), "MGR" NUMBER (4, 0), "HIREDATE" DATE, "SAL" NUMBER (7, 2),
  "COMM" NUMBER (7, 2), "DEPTNO" NUMBER (2, 0),CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING INDEX ENABLE,CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE)
• D. CREATE TABLE "EMP" ("EMPNO" NUMBER (4,0), "ENAME" VARCHAR2 (10), "JOB" VARCHAR2 (9), "MGR" NUMBER (4,0), "HIREDATE"DATE, "SAL" NUMBER (7, 2) , "COMM" NUMBER (7, 2), "DEPTNO" NUMBER (2,0),CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL
  65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2417483645PCTINCREASE 0 FREELISTS
  1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CHACHE DEFAULT
  CELL_FLASH_CACHE DEFAULT)TABLESPACE "SYSAUX" ENABLE,CONSTRAINT
  "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "DEPT" ("DEPTNO") ENABLE)
  SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
  255NOCOMPRESS LOGGINGSTORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1
  MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
  1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
  DEFAULT)TABLESPACE "SYSAUX"

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

• A. ALL_DESCRIBE
• B. ALL_PROCEDURES
• C. ALL_SOURCE
• D. ALL-DEPENDENCIES
• E. ALL_ARGUMENTS

In which situation will cached results become invalid?

• A. When a session on this database instance invokes the function with the same parameter values
• B. When a session executes an insert, update, or delete statement on a table or view that is queried by the result-cached function
• C. When the memory allocated to the server result cache is increased using the RESULT_CACHE_MAX_SIZE initialization parameter
• D. When the RESULT_CACHE_MODE parameter is set to FORCE.
• E. When a new session is opened to invoke the function which is already cached