Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which three actions can be performed by using the DBMS_ASSERT package to prevent SQL injection? (Choose three.)
A. Detect a wrong user.
B. Check input string length.
C. Verify qualified SQL names. D. Validate TNS connect strings.
E. Verify an existing schema name.
F. Enclose string literals within double quotation marks.
Answer: C,E,F
Q2. 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 executes successfully.
B. It gives an error because VARCHAR2 should be explicitly converted to CLOB.
C. It gives an error because CLOB variables should be initialized to EMPTY_CLOB().
D. It gives an error because the concatenation operator cannot be used with the CLOB data type.
Answer: A
Q3. Identify three guidelines for the DBMS_ASSERT package. (Choose three.)
A. Prefix all calls to DBMS_ASSERT with the SYS schema name.
B. Embed DBMS_ASSERT verification routines inside the injectable string.
C. Escape single quotes when you use the ENQUOTE_LITERAL procedure.
D. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions.
E. Prefix all calls to DBMS_ASSERT with a schema name that owns the subprogram that uses the DBMS_ASSERT package.
Answer: A,C,D
Q4. View Exhibit1 and examine the structure of the EMPLOYEES table.
itexamworld.com
View Exhibit2 and examine the code in the PL/SQL block.
The PL/SQL block fails to execute. What could be the reason?
A. Nested tables cannot be returned by a function.
B. The NEWNAMES nested table has not been initialized.
C. The assignment operator cannot be used to transfer all the element values from GROUP1 to GROUP2.
D. The third element of OLDNAMES cannot be assigned to the third element of GROUP1 because they are of inconsistent data types.
E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.
Answer: E
Q5. 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 null internal LOBs.
B. The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize only non-NULL internal LOBs.
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 CLOB and BLOB columns can be initialized with a character or raw string, respectively, provided they are less than 4000 bytes in size.
Answer: C,E
Q6. Examine the structure of the PRINT_MEDIA table: Name Null? Type
ADVT_ID NUMBER ADVT_SOURCE CLOB Examine the following PL/SQL block:
DECLARE
lobloc CLOB;
buffer VARCHAR2(100);
amount NUMBER;
offset NUMBER :=1;
BEGIN
buffer :='This is the second line of a new document'
amount := LENGTH(buffer);
SELECT advt_source INTO lobloc FROM print_media WHERE advt_id=2 FOR UPDATE;
DBMS_LOB.WRITE(lobloc,amount,offset,buffer);
COMMIT;
END;
/
What must be the value in the ADVT_SOURCE column for the above code to execute
successfully?
A. null
B. an empty locator
C. a non-NULL value
D. either null or any non-NULL values
Answer: C
Q7. Examine the structure of the EMPLOYEES table that exists in your schema. Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_ID NUMBER(4)
You successfully create a GET_MAX procedure to find the maximum salary in the department of a specified employee.
You then code a PL/SQL block to display the maximum salary in the departments of the first five employees in the EMPLOYEES table.
View the Exhibit. Examine the procedure and the block of PL/SQL code.
What is the outcome of executing the block of PL/SQL code?
A. It executes successfully and gives the required output.
B. It gives an error because ROWNUM cannot be used in cursor definitions.
C. It gives an error because usage of the %ROWCOUNT attribute is not valid.
D. It executes successfully, but does not give the required output because the procedure call resets the %ROWCOUNT value.
Answer: A
Q8. 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 invokes the external C procedure.
B. It only publishes the external C procedure.
C. It fails because the external C procedure is not published.
D. It fails because the input data type is BINARY_INTEGER and the external C procedure expects an integer.
Answer: C
Q9. Which two statements are true about the query results stored in the query result cache? (Choose two.)
A. If any of the tables used to build a query is modified by an ongoing transaction in the current session, the query result is not cached.
B. A query result based on a read-consistent snapshot of data that is older than the latest committed version of the data is not cached.
C. Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.
D. A query result for a query that has a bind variable is stored in the cache and is reused if the query is equivalent even when the bind variable has a different value.
Answer: A,B
Q10. 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;.
Answer: A
Q11. 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 L_OUT variable must be initialized to an empty locator. B. The L_OUT variable has to be declared as a temporary LOB. C. The A_CLOB variable has to be declared as a temporary LOB.
D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.
Answer: D
Q12. View the Exhibit to examine a Java source file.
You have the corresponding Java class file and you execute the command as follows:
SQL> CREATE OR REPLACE PROCEDURE ccformat
(x IN OUT VARCHAR2)
AS LANGUAGE JAVA
NAME 'FormatCreditCardNo.formatCard()'
Which statement is true about the command?
A. It loads the Java class method into Oracle Database and publishes it.
B. It publishes the Java class method, but the CCFORMAT PL/SQL procedure fails when it is executed.
C. It creates the CCFORMAT PL/SQL subprogram without publishing, which can be used to invoke the Java class method.
D. It publishes the Java class method and the CCFORMAT PL/SQL procedure invokes the Java class method when it is executed.
Answer: B
Q13. You issue the following command to create the PRINT_MEDIA table.
CREATE TABLE print_media
(product_id NUMBER(3),
ad_sourcetext CLOB,
ad_photo BLOB);
Evaluate the following INSERT statements:
INSERT INTO print_media VALUES (1, empty_clob(),empty_blob());
INSERT INTO print_media VALUES (2,'This is a One Line Story',null);
INSERT INTO print_media VALUES (3,'This is another One Line Story',empty_blob());
INSERT INTO print_media VALUES (4,empty_clob(),to_blob('This is new Story'));
Which of the above INSERT statements are valid?
A. Only the first statement is valid.
B. All the statements are valid.
C. Only the first and fourth statements are valid.
D. Only the first and second statements are valid.
E. Only the first, second and third statements are valid.
Answer: E
Q14. 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
Answer: C
Q15. Which two statements are true about SecureFile LOB options? (Choose two.)
A. The COMPRESSION HIGH option can be enabled only for CLOBs.
B. The COMPRESSION HIGH option can be enabled for all internal LOBs.
C. The DECRYPT option can be used to remove encryption only if the LOB column is empty.
D. The DECRYPT option can be used to remove encryption from LOB columns that are empty or contain data.
Answer: B,D
Q16. Which two statements are true about cursor variables? (Choose two.)
A. A cursor variable points to the current row in the result set of a multirow query stored in a work area.
B. A cursor variable is an explicitly named work area in which the results of different multirow queries can be stored.
C. A cursor variable can be used only if a query is performed and its results are processed in the same subprogram.
D. A cursor variable can be used to perform a query in one subprogram, and process the results in a different subprogram.
Answer: A,D