Oracle 1Z0-146 問題集

質問 1：
Examine the following line of code that is part of a PL/SQL application: stmt:='SELECT session_id FROM sessions WHERE ' || p_where_stmt; Identify a solution for preventing SQL injection in the above code.
A. Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the code.
B. Replace P_WHERE_STMT with a bind variable.
C. Do not use APIs that allow arbitrary query parameters to be exposed.
D. Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection.
正解：C

質問 2：
View the Exhibit and examine the PL/SQL code.
The code takes a long time to execute. What would you recommend to improve performance?

A. using the BULK COLLECT option for query instead of cursor
B. using WHILE.. END LOOP instead of FOR .. END LOOP
C. using the SIMPLE_INTEGER data type instead of the NUMBER data type
D. using NOT NULL constraint when declaring the variables
正解：A

質問 3：
Examine the following settings for a session:
PLSQL_CODE_TYPE = NATIVE
View the Exhibit and examine the PL/SQL code.
You compile the program with the following attributes:
SQL> ALTER PROCEDURE proc1 COMPILE PLSQL_OPTIMIZE_LEVEL = 1;
Which statement is true about the execution of the PROC1 procedure in this scenario?

A. The FUNC1 function would be called inline irrespective of the value set for the PLSQL_OPTIMIZE_LEVEL parameter.
B. The FUNC1 function would not be called inline because the value for the PLSQL_OPTIMIZE_LEVEL parameter is set to a lower value.
C. The FUNC1 function would be called inline because PRAGMA INLINE forces a specific call to
be inlined.
D. The FUNC1 function would be inlined because the value set for the PLSQL_CODE_TYPE parameter is set to NATIVE.
正解：B

質問 4：
The database instance was started up with the following initialization
parameter values:
MEMORY_TARGET = 500M
RESULT_CACHE_MODE = FORCE
RESULT_CACHE_MAX_SIZE = 0
After the database startup, to enable the result cache, you issued the following command:
SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;
Which is the effect of this command?
A. The command produces an error because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
B. The query result cache is enabled and 2 MB of the memory target is allocated to the result cache.
C. The query result cache is enabled and 0.25% of the memory target is allocated to the result cache.
D. The query result cache is not enabled because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
正解：D

質問 5：
View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

Examine the PL/SQL block that you execute to find the average salary for employees in the 'Sales' department: DECLARE TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20); v_emp_sal emp_sal; PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS BEGIN SELECT AVG(salary) INTO p_arr(p_dept_name) FROM employees WHERE department_id= (SELECT department_id FROM departments
WHERE department_name=p_dept_name);
END get_sal;
BEGIN
get_sal('Sales',v_emp_sal);
DBMS_OUTPUT.PUT_LINE( v_emp_sal('Sales'));
END;
/
What is the outcome?
A. It generates an error because the associative array definition is not valid.
B. It generates an error because an associative array cannot be used with the SELECT INTO statement.
C. It executes successfully and gives the correct output.
D. It generates an error because an associative array cannot be passed to a procedure in OUT mode.
正解：C

質問 6：
You created a procedure as follows:
CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)
IS
stmt VARCHAR2(100);
pname VARCHAR2(20);
BEGIN
stmt:='SELECT product_name FROM products WHERE product_id=:2';
EXECUTE IMMEDIATE stmt INTO pname USING twhr;
DBMS_OUTPUT.PUT_LINE(pname);
END;
/
View the Exhibit to examine the structure of PRODUCTS table.

Which statement is true about the procedure?
A. The values for bind arguments remain persistent in the session after the execution of the procedure.
B. It can be invoked only from a PL/SQL block.
C. It reduces the chances of SQL injection by using bind arguments.
D. It produces an error when invoked.
正解：C

質問 7：
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 line 2 with TYPE refcur IS REF CURSOR RETURN employees.salary%TYPE; emp_cv refcur;.
D. Replace BULK COLLECT in line 8 with the OPEN, FETCH, LOOP, and CLOSE statements.
正解：A

質問 8：
You issue this command to create a table called LOB_STORE:
CREATE TABLE lob_store
(lob_id NUMBER(3),
photo BLOB DEFAULT EMPTY_BLOB(),
cv CLOB DEFAULT NULL,
ext_file BFILE DEFAULT NULL)
/
What is the outcome?
A. It generates an error because DEFAULT cannot be set to EMPTY_BLOB() during table creation.
B. It generates an error because DEFAULT cannot be set to null for a CLOB column during table creation.
C. The table is created successfully.
D. It generates an error because DEFAULT cannot be set to null for a BFILE column during table creation.
正解：C

質問 9：
The following command is executed to start tracing in a user session:
SQL> EXECUTE
DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS);
Which statement is true about the effect of the command?
A. It allows tracing of system-defined exceptions raised by all subprograms executed in the session.
B. It allows tracing of user-defined exceptions raised by all subprograms executed in the session.
C. It allows tracing of exceptions raised by subprograms compiled with the DEBUG option and executed in the session.
D. It allows tracing of exceptions raised by all subprograms executed in the session.
正解：C