Oracle 1Z0-146 問題集

質問 1：
Examine the structure of the LOB_STORE table.

Name Null? Type
LOB_ID NUMBER VIDEO_CLIP BLOB You create a 'DATA_FILES' directory object that contains the 'IMAGE1.GIF'operating system (OS) file. View the Exhibit and examine the LOAD_LOB procedure code that you execute for reading data from the OS file into the BLOB column.
It is created with compilation errors. What is the reason?
A. The return type of DBMS_LOB.FILEEXISTS is not compatible with the IF statement.
B. OPEN, CLOSE, and READONLY routines of DBMS_LOB should be used instead of FILEOPEN, FILECLOSE, and FILE_READONLY.
C. V_BLOB should be initialized to EMPTY_BLOB().
D. The WRITE routine of DBMS_LOB should be used instead of LOADFROMFILE.
正解：A

質問 2：
In which two situations is the body of a result-cached function executed? (Choose two.)
A. if a session on this database instance invokes the function with the same parameter values
B. if the memory allocated for the result cache is increased
C. if a session executes a data manipulation language (DML) statement on a table or view that was specified in the RELIES_ON clause of a result-cached function
D. if the first time a session on this database instance invokes the function with a parameter value
正解：C,D

質問 3：
Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.)
A. When fetching from a cursor, fetch into a record.
B. When fetching from a cursor, fetch required values into individually declared variables.
C. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH, and CLOSE statements to manipulate the cursor instead of using the cursor FOR loop.
D. Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor.
正解：A,D

質問 4：
You executed the following command to alter the session parameter:
SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
Which two statements are true in this scenario? (Choose two.)
A. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope does not collect data for the compiled object.
B. All the identifiers declared in compiled program units before altering the parameter settings appear in the *_IDENTIFIER static data dictionary views.
C. All the identifiers declared in compiled program units before altering the parameter settings do not appear in the *_IDENTIFIER static data dictionary views.
D. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope collects data for the compiled object and stores it in the SYSTEM tablespace.
正解：A,C

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

質問 6：
There is a Java class file in your system and you publish it using the following command:
CREATE OR REPLACE PROCEDURE ccformat
(x IN OUT VARCHAR2)
AS LANGUAGE JAVA
NAME 'FormatCreditCardNo.formatCard(java.lang.String[])';
However, you receive the following error when executing the CCFORMAT procedure:
ERROR at line 1:
ORA-29540: class FormatCreditCardNo does not exist
ORA-06512: at "SH.CCFORMAT", line 1
ORA-06512: at line 1
What would you do to execute the procedure successfully?
A. Change the listener configuration.
B. Use the loadjava utility to load the Java class file into the database.
C. Create a directory object and link it to the Java class file.
D. Rebuild the Java class file when the database instance is running.
正解：B

質問 7：
Which two statements are true about the usage of the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure? (Choose two.)
A. You can describe a stored procedure, stored function, packaged procedure, or packaged function.
B. You can obtain information about the position, name, and data type of the arguments of a procedure.
C. You can describe anonymous PL/SQL blocks.
D. You can describe remote objects.
正解：A,B

質問 8：
You have an external C procedure stored in a dynamic-link library (DLL).
The C procedure takes an integer as argument and returns an integer. You want to invoke the C
procedure through a PL/SQL program.
View the Exhibit.

Which statement is true about the C_OUTPUT PL/SQL program?
A. It fails because the input data type is BINARY_INTEGER and the external C procedure expects an integer.
B. It invokes the external C procedure.
C. It fails because the external C procedure is not published.
D. It only publishes the external C procedure.
正解：C

質問 9：
Examine the structure of the TEXT_TAB table. Name Null? Type
TEXT_ID NUMBER
DOC1 CLOB
DOC2 CLOB
You issue the following INSERT commands:
INSERT INTO text_tab VALUES (1, 'This is line 1',null);
INSERT INTO text_tab VALUES (2, 'This is line 1','This is line 2');
Then you execute the following block of the PL/SQL code:
DECLARE
vc1 VARCHAR2(1000):= 'This is the preface';
lb1 CLOB;
lb2 CLOB;
BEGIN
SELECT doc1 INTO lb1 FROM text_tab WHERE text_id=1;
SELECT doc1 || doc2 INTO lb1 FROM text_tab WHERE text_id=2;
lb2 := vc1|| lb1;
UPDATE text_tab SET doc2 = lb2 WHERE text_id = 1;
END;
/
What is the outcome?
A. It gives an error because VARCHAR2 should be explicitly converted to CLOB.
B. It gives an error because the concatenation operator cannot be used with the CLOB data type.
C. It executes successfully.
D. It gives an error because CLOB variables should be initialized to EMPTY_CLOB().
正解：C