Oracle 11g: Advanced PL/SQL
Question No: 81 – (Topic 1)
Which two are major approaches that can be used to reduce the SQL injection by limiting user input? (Choose two.)
Restrict users accessing specified web page.
Use NUMBER data type if only positive integers are needed.
Use dynamic SQL and construct it through concatenation of input values.
In PL/SQL API, expose only those routines that are intended for customer use.
Question No: 82 – (Topic 1)
Which three actions can be performed by using the DBMS_ASSERT package to prevent SQL injection? (Choose three.)
Detect a wrong user.
Check input string length.
Verify qualified SQL names.
Validate TNS connect strings.
Verify an existing schema name.
Enclose string literals within double quotation marks.
Question No: 83 – (Topic 1)
Which guidelines should be considered when designing and using cursors in a PL/SQL block? ? (Choose all that apply.)
When fetching from a cursor, fetch into a record.
Use parameters with cursors so that the result set for the cursor is not tied to a specific variable in a program.
Use the %NOTFOUND attribute in combination with the SELECT INTO statement to check for non existent values.
Whenever possible, explicitly declare the cursor and use the OPEN, FETCH and CLOSE statements to manipulate the cursor instead of using cursor FOR loop.
When using data manipulation language statements, (DML) reference a SQL cursor attribute immediately after the DML statement executes in the same block.
Question No: 84 – (Topic 1)
View the Exhibit and examine the settings for the PLSQL_CODE_TYPE parameter.
After sometime, the user recompiles the procedure DISPLAY_SAL_INFO by issuing the following command:
SQLgt; ALTER PROCEDURE display_sal_info COMPILE; Which statement would be true in this scenario?
The procedure would be invalidated.
The procedure would remain as NATIVE code type.
The procedure would be changed to INTERPRETED code type.
The command would produce an error and the procedure must be compiled using the PLSQL_CODE_TYPE attribute with value INTERPRETED.
Question No: 85 – (Topic 1)
Examine the structure of the TEST_DETAILS table:
Name Null? Type
——– —- —– TEST_ID NUMBER DESCRIPTION CLOB
DESCRIPTION data was entered earlier and saved for TEST_ID 12.
You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION column for TEST_ID 12:
clob_loc CLOB; buf CHAR(12);
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := #39;0123456789#39;; DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf); COMMIT;
It generates an error on execution.
What correction should you do to achieve the required result?
WRITEAPPEND must be replaced with APPEND.
The BUF variable data type must be changed to CLOB.
FOR UPDATE must be added to the SELECT statement.
The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.
Question No: 86 – (Topic 1)
Examine the following parameter values for a session:
PLSQL_CODE_TYPE = NATIVE PLSQL_OPTIMIZE_LEVEL = 2
Which two statements are true in this scenario? (Choose two.)
The compiler automatically inlines subprograms even if you specify that they not be inlined.
The compiler optimizes PL/SQL programs by elimination of unnecessary computations and exceptions.
PL/SQL statements in a PL/SQL program unit are compiled into machine-readable code, and stored in the SYSTEM tablespace.
PL/SQL statements in a PL/SQL program unit are compiled into an intermediate form, machine-readable code, which is stored in the database dictionary.
Question No: 87 – (Topic 1)
Which two statements are true about the query results stored in the query result cache? (Choose two.)
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.
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.
Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.
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.
Question No: 88 – (Topic 1)
Which two statements are true about the extproc process? (Choose two.)
It loads the dynamic library.
It is started by the server process.
It converts the C output back to PL/SQL.
A single extproc process serves all user sessions.
Question No: 89 – (Topic 1)
The PLSQL_OPTIMIZE_LEVEL parameter is set to 2 for the session. Examine the section of code given:
FUNCTION p2 (p boolean) return PLS_INTEGER IS … FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS
PRAGMA INLINE(p2, #39;YES#39;);
x := p2(true) p2(3);
Which statement is true about the INLINE pragma procedure calls?
Only the call to the P2 function with BOOLEAN as the argument is inlined.
INLINE pragma affects both the functions named P2 and is called inline.
Only the call to the P2 function with PLS_INTEGER as the argument is inlined.
None of the functions are inlined because inlining is not supported for overloaded functions.
Question No: 90 – (Topic 1)
Examine the structure of the TEXT_TAB table.
Name Null? Type
——– —- —– TEXT_ID NUMBER
You issue the following INSERT commands:
INSERT INTO text_tab VALUES (1, #39;This is line 1#39;,null);
INSERT INTO text_tab VALUES (2, #39;This is line 1#39;,#39;This is line 2#39;); Then you execute the following block of the PL/SQL code:
vc1 VARCHAR2(1000):= #39;This is the preface#39;; 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?
It executes successfully.
It gives an error because VARCHAR2 should be explicitly converted to CLOB.
It gives an error because CLOB variables should be initialized to EMPTY_CLOB().
It gives an error because the concatenation operator cannot be used with the CLOB data type.