1Z0-146 - Oracle 11g: Advanced PL/SQL

Go back to Oracle

Exam FAQ

How many questions are there on the 1Z0-146 exam? How long is the 1Z0-146 exam? How much does the 1Z0-146 exam cost? What is the best way to study for the 1Z0-146 exam?

Example Questions

Which two statements are true about the context of an application? (Choose two.) You created a PL/SQL function with the RESULT_CACHE and RELIES_ON clauses. In which scenarios is the cached reuslt bypassed? (Choose all that apply) Match the following external C procedure components with their descriptions: 1. External procedure a. a process that starts the extproc process 2. Shared library b. a session-specific process that executes the external procedure 3. Alias library c. schema object that represents the operating system (OS) shared library 4. The extproc process d. operating system file that stores the external procedure 5. Listener process e. a unit of code written in C Which two types of query results cannot be stored in the query result cache? (Choose two.) Which statement describes the purpose of the plshprof command? 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: DECLARE clob_loc CLOB; buf CHAR(12); BEGIN SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ; buf := '0123456789'; DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf); COMMIT; END; / It generates an error on execution. What correction should you do to achieve the required result? Examine the following PL/SQL statements: Stmt='SELECT session_id FROM sessions WHERE '|| p_where_stmt; Identify a solution for preventing SQL injection in the above code. Identify the component of the PL/SQL hierarchical profiler that uploads the result of profiling into database tables. Which two statements are true about the migration of BasicFile to the SecureFile format by using the DBMS_REDEFINITION package? (Choose two.) Which two statements are true about the tuning of PL/SQL code? (Choose two.) 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? You set RESULT_CACHE_MAX_SIZE to a nonzero value to enable result caching. You executed the following command to check the status for the result cache:, SQL> select dbms_result_cache.status() from dual; You receive the following output: DBMS_RESULT_CACHE.STATUS() DISABLED Identify the reason for the output. Match the following external C procedure components with their descriptions: 1 External procedure a. a process that starts the extproc process 2 Shared library b. a session-specific process that executes the external procedure 3 Alias library c. schema object that represents the operating system (OS) shared library 4 The extproc process d. operating system file that stores the external procedure 5 Listener process e. a unit of code written in C Identify two methods for immunizing PL/SQL code against SQL injection attacks. (Choose two.) Which two statements correctly describe the features of SecureFiles? (Choose two.) Examine the code snippet from the declarative section of a PL/SQL block: DECLARE TYPE va1 IS VARRAY(10) OF VARCHAR2(20); SUBTYPE scale IS NUMBER(1,0); TYPE tb1 IS TABLE OF departments.department_name%TYPE INDEX BY departments.department_id%TYPE; TYPE tb2 IS TABLE OF va1 INDEX BY PLS_INTEGER; TYPE tb3 IS TABLE OF scale INDEX BY VARCHAR2(10); TYPE tb4 IS TABLE OF DATE INDEX BY DATE; TYPE tb5 IS TABLE OF NUMBER INDEX BY CHAR(2); .... Which of the above are valid definitions for associative arrays? (Choose all that apply.) 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.) Which two statements are true about REF CURSOR types? (Choose two.) Which two statements are true about associative arrays and varrays? (Choose two.) Identify the two type of PL/SQL programs for which you consider setting the compilation method to native mode. (choose two.) 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.) Which two conditions must be true for a PL/SQL function to be result cached? (Choose two.) The result cache is enabled for the database instance. Examine the following code for a PL/SQL function: CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR RESULT_CACHE RELIES_ON (HR.EMPLOYEES) IS date_hired DATE; BEGIN SELECT hire_date INTO date_hired FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN TO_CHAR(date_hired); END; Which statement is true in this scenario? You have an OE_ACCESS_ORDERS_POLICY security policy implemented on the ORDERS table in the OE schema. The user sessions are able to access only the desired rows. The database administrator (DBA) uses the following command: SQL> EXECUTE DBMS_RLS.ENABLE_POLICY('OE','ORDERS','OE_ORDERS_ACCESS_POLICY',FALSE); Which statement is true about user sessions that are connected currently? Which two statements are true about associative arrays and nested tables? (Choose two.) Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.) You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario? When do you use static SQL as a technique for avoiding SQL injection? The database instance was started up using the automatic memory management feature. No value was set for the RESULT_CACHE_MAX_SIZE parameter. Examine the following initialization parameter settings for your database: MEMORY_TARGET = 500M RESULT_CACHE_MODE=MANUAL You execute a query by using the result_cache hint. Which statement is true in this scenario? Which two statements are true about the query results stored in the qeury result cache? (Choose two.) Which statements are true about the SecureFile storage paradigm? (Choose two.) Which two statements are true about the working of fine-grained access? (Choose two.) Identify the method that is used by fine-grained access (FGA). Which two statements are true about the extproc process? (Choose two.) Examine the code in the following PL/SQL block: DECLARE TYPE NumList IS TABLE OF INTEGER; List1 NumList := NumList(11,22,33,44); BEGIN List1.DELETE(2); DBMS_OUTPUT.PUT_LINE ( 'The last element# in List1 is ' || List1.LAST || ' and total of elements is '||List1.COUNT); List1.EXTEND(4,3); END; / Which two statements are true about the above code? (Choose two.) Which two statements are true about the inlining of PL/SQL subprograms? (Choose two.) 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? Which statements are true about temporary LOBs? (Choose all that apply.) The result cache is enabled for the database instance. Examine the following code for a PL/SQL function: CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR RESULT_CACHE RELIES_ON (HR.EMPLOYEES) IS date_hired DATE; BEGIN SELECT hire_date INTO date_hired FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN TO_CHAR(date_hired); END; You notice that results for the functions are not used effectively. What do you recommend for better utilization of the result cache? (Choose all that apply.) Which guidelines should be considered when designing and using cursors in a PL/SQL block? ? (Choose all that apply.) Which two statements are true about the initialization of internal LOBs? (Choose two.) Identify the two types of PL/SQL programs for which you consider setting the compilation method to native mode. (Choose two.) Identify two strategies against SQL injection. (Choose two.) A procedure is created in the SYS schema to allow users to change the password as follows: CREATE OR REPLACE PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL, p_new_password VARCHAR2 DEFAULT NULL) IS v_sql_stmt VARCHAR2(500); BEGIN v_sql_stmt := 'ALTER USER '||p_username ||' IDENTIFIED BY ' || p_new_password; EXECUTE IMMEDIATE v_sql_stmt; END change_password; The SYS user has granted EXECUTE privilege on the procedure to the OE user. But OE is able to change the password for SYS by using this procedure. How would you protect this? Which two statements are true about the SQL Query Result Cache? (Choose two.) Which two statements are true about the usage of the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure? (Choose two.) In which two situations is the body of a result-cached function executed? (Choose two.) Identify two factors that you consider for compiling a PL/SQL program unit for interpreted mode. (Choose two.) Which statements are true about internal LOBs? (Choose all that apply) Identify the component of the PL//SQL hierarchical profiler that uploads the result of profiling into database tables:

Study Guides