Oracle 1Z0-146 問題集

質問 1：
Which two statements are true about the initialization of internal LOBs? (Choose two.)
A. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize only non-NULL internal LOBs.
B. The CLOB and BLOB columns can be initialized with a character or raw string, respectively, provided they are less than 4000 bytes in size.
C. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize both null and non-NULL internal LOBs.
D. The CLOB and BLOB columns can be initialized only by using the EMPTY_CLOB() and EMPTY_BLOB() functions, respectively.
E. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize only null internal LOBs.
正解：B,C

質問 2：
View the Exhibit and examine the PL/SQL code.

Identify the sections of the code that need to be modified for better performance gains. (Choose all that apply.)
A. Avoid the NOT NULL constraint when declaring the variable.
B. Define and compile procedures independently, and not as part of a package.
C. Remove redundant SQL statements in the procedures.
D. Use cursors to fetch values.
正解：A,C

質問 3：
Which two statements are true about associative arrays and nested tables? (Choose two.)
A. Only associative arrays can hold an arbitrary number of elements.
B. Both associative arrays and nested tables can hold an arbitrary number of elements.
C. Both associative arrays and nested tables can use numbers and strings for subscripts.
D. Only associative arrays can use numbers and strings for subscripts.
正解：B,D

質問 4：
DATA_FILES is a directory object that contains the DETAILS.TXT text file. You have the required permissions to access the directory object.
You create a table using the following command:
CREATE TABLE clob_tab(col2 CLOB);
View the Exhibit and examine the PL/SQL block that you execute for loading the external text file
into the table that currently has no rows. The PL/SQL block results in an error.
What correction must be done to ensure the PL/SQL block executes successfully?

A. The A_CLOB variable has to be declared as a temporary LOB.
B. The L_OUT variable must be initialized to an empty locator.
C. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.
D. The L_OUT variable has to be declared as a temporary LOB.
正解：C

質問 5：
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

質問 6：
To examine the dependencies between all PL/SQL objects and the tables and views they
reference, you executed the following query as the user OE:
SQL> SELECT owner || '.' || NAME refs_table
, referenced_owner || '.' || referenced_name AS table_referenced
FROM all_dependencies
WHERE owner = USER
AND TYPE IN ('PACKAGE', 'PACKAGE BODY','PROCEDURE', 'FUNCTION')
AND referenced_type IN ('TABLE', 'VIEW')
AND referenced_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, NAME, referenced_owner, referenced_name;
Which statement is true about the output of the query?
A. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.
B. It displays no rows because this query needs to be executed as the user SYS for required results.
C. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.
D. It displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM..
正解：D

質問 7：
Which two statements are true about nested tables and varrays? (Choose two.)
A. Only varrays must have consecutive numbers as subscripts.
B. Both nested tables and varrays can be used as column types in database tables.
C. Only nested tables can be used as column types in database tables.
D. Both nested tables and varrays must have consecutive numbers as subscripts.
正解：A,B

質問 8：
View Exhibit1 and examine the structure of the EMPLOYEES table.

View the Exhibit2 and examine the PL/SQL block that you execute for displaying the last name
and hire date of the employees in department ID 60.
Which statement is true about the outcome?
A. It generates an error because RECORD type cannot be used with varrays.
B. It generates an error because BULK COLLECT cannot be used with varrays.
C. It executes successfully only if department ID 60 has five or less than five employees.
D. It executes successfully even if department ID 60 has more than five employees by dynamically extending the varray.
正解：C

質問 9：
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