Oracle 1Z0-148 Online Practice Questions and Exam Preparation

Oracle 1Z0-148 Online Questions & Answers

• Question 221:

  In your schema, the DEPARTMENTS table contains the columns DEPARTMENT_ID and DEPARTMENT_NAME.

  You want to display the department name for existing department id 10.

  With SERVEROUTPUT enabled, which two blocks of code will give the required output?

  A. DECLARE TYPE dept_cur IS REF CURSOR; cv1 dept_cur; v_dept_name departments. department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; IF cv1 IS NOT NULL THEN FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); END IF CLOSE cv1; END;
  B. DECLARE TYPE dept_cur IS REF CURSOR RETURN departments%ROWTYPE; cv1 dept_cur; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT * FROM departments WHERE department_id=10; FETCH cv1. department_name INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;
  C. DECLARE TYPE names_t IS TABLE OF SYS_REFCURSOR INDEX BY PLS_INTEGER; cv1 names_t; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;
  D. DECLARE cv1 SYS_REFCURSOR; v_dept_name departments.department_name%TYPE; BEGIN EXECUTE IMMEDIATE ‘BEGIN OPEN: cv1 FOR SELECT department_name FROM departmnets WHERE department_id=10: END;’ USING IN cv1; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;
  A. DECLARE TYPE dept_cur IS REF CURSOR; cv1 dept_cur; v_dept_name departments. department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; IF cv1 IS NOT NULL THEN FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); END IF CLOSE cv1; END;
  D. DECLARE cv1 SYS_REFCURSOR; v_dept_name departments.department_name%TYPE; BEGIN EXECUTE IMMEDIATE ‘BEGIN OPEN: cv1 FOR SELECT department_name FROM departmnets WHERE department_id=10: END;’ USING IN cv1; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;

• Question 222:

  When do you use static SQL as a technique for avoiding SQL injection?

  A. when the WHERE clause values are unknown
  B. when the code contains data definition language (DDL) statements
  C. when all Oracle identifiers are known at the time of code compilation
  D. when the SET clause values are unknown at the time of code compilation
  C. when all Oracle identifiers are known at the time of code compilation

• Question 223:

  You are logged on to the SCOTT schema and the schema has EMP and DEPT tables already created: Examine this PL/SQL procedure:

  

  Which PL/SQL block will raise an exception?

  A. EXEC get_tab_row_count (‘emp’);
  B. EXEC get_tab_row_count (‘SCOTT.EMP’);
  C. EXEC get_tab_row_count (‘ “EMP” ’);
  D. EXEC get_tab_row_count (‘DEPT’);
  E. EXEC get_tab_row_count (‘DEPT, EMP’)
  E. EXEC get_tab_row_count (‘DEPT, EMP’)

• Question 224:

  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 = 'SA_REP';

  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?

  

  A. Replace EMP_CV in line 3 with employees.salary%TYPE.
  B. Replace line 2 with TYPE refcur IS REF CURSOR; emp_cv refcur;.
  C. Replace BULK COLLECT in line 8 with the OPEN, FETCH, LOOP, and CLOSE statements.
  D. Replace line 2 with TYPE refcur IS REF CURSOR RETURN employees.salary%TYPE; emp_cv refcur;.
  A. Replace EMP_CV in line 3 with employees.salary%TYPE.

• Question 225:

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

  A. It can be used for transforming data in permanent internal LOBs.
  B. It is used only for the migration of BasicFile to the SecureFile format.
  C. It is used only for the migration of the LONG column to the LOB column.
  D. It creates a LOB variable that is not associated with any table and is stored in the user's temporary tablespace.
  E. It creates a LOB variable that is associated with a specific table and is temporarily stored in the user's default tablespace.
  A. It can be used for transforming data in permanent internal LOBs.
  D. It creates a LOB variable that is not associated with any table and is stored in the user's temporary tablespace.

• Question 226:

  Examine this PL/SQL function:

  

  What happens when the function is created with PLSQL_WARNINGS set to `ENABLE: ALL'?

  A. There are no compilation warnings or errors.
  B. It fails compilation.
  C. An information compilation warning is generated.
  D. A performance compilation warning is generated.
  E. A severe compilation warning is generated.
  E. A severe compilation warning is generated.

• Question 227:

  You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario?

  A. The corresponding shared library is also removed from the system.
  B. PL/SQL subprograms can be used to invoke the external C procedure.
  C. The existing extproc process is terminated and a new extproc is started.
  D. The PL/SQL subprogram that depends on the external C program becomes invalid.
  D. The PL/SQL subprogram that depends on the external C program becomes invalid.

• Question 228:

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

  SQL> 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.)

  

  A. In total, four blocks are used by the result cache.
  B. Currently 52 KB is allocated to the result cache.
  C. Currently 32 KB is allocated to the result cache.
  D. The result cache can be increased by 65628 bytes.
  C. Currently 32 KB is allocated to the result cache.
  D. The result cache can be increased by 65628 bytes.

• Question 229:

  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
  D. ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified

• Question 230:

  Identify two strategies against SQL injection. (Choose two.)

  A. Using parameterized queries with bind arguments.
  B. Use subprograms that are run with the definer's right.
  C. Use RESTRICT_REFERENCE clauses in functions that use dynamic SQLs.
  D. Validate user inputs to functions that use dynamic SQLs built with concatenated values.
  A. Using parameterized queries with bind arguments.
  D. Validate user inputs to functions that use dynamic SQLs built with concatenated values.