January 22, 2021 by admin Latest Updated Oracle 1Z0-148 practice test Welcome to your Latest Updated Oracle 1Z0-148 practice test QUESTION 1In your schema, the DEPARTMENTS table contains the columns DEPARTMENT_ID and DEPARTMENT_NAME.You want to display the department name for the existing department id 10.With SERVER OUTPUT enabled, which two blocks of code will give the required output? A. DECLARE TYPE dept_cur IS REF CURSOR; cv1 dept_cur; v_dept_name departments. department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; IF cv1 IS NOT NULL THEN FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); END IF CLOSE cv1; END; B. DECLARE TYPE dept_cur IS REF CURSOR RETURN departments%ROWTYPE; cv1 dept_cur; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT * FROM departments WHERE department_id=10; FETCH cv1. department_name INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END; C. DECLARE TYPE names_t IS TABLE OF SYS_REFCURSOR INDEX BY PLS_INTEGER; cv1 names_t; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END; D. DECLARE cv1 SYS_REFCURSOR; v_dept_name departments.department_name%TYPE; BEGIN EXECUTE IMMEDIATE ‘BEGIN OPEN: cv1 FOR SELECT department_name FROM departmnets WHERE department_id=10: END;’ USING IN cv1; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END; QUESTION 2The following command is executed to start tracing in a user session:SQL> EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS); Which statement is true about the effect of the command?A. It allows tracing of exceptions raised by all subprograms executed in the session.B. It allows tracing of user-defined exceptions raised by all subprograms executed in the session.C. It allows tracing of system-defined exceptions raised by all subprograms executed in the session.D. It allows tracing of exceptions raised by subprograms compiled with the DEBUG option and executed in the session. QUESTION 3Examine this code (all compiled into schema HR): Execute this code:SET SERVER OUTPUT ON;EXEC test_pkg.do_stuff What is the correct output?A. Option AB. Option BC. Option CD. Option DE. Option E QUESTION 4Examine the following line of code that is part of a PL/SQL application: stmt:=\\'SELECT session_id FROM sessionsWHERE \\' || p_where_stmt; Identify a solution for preventing SQL injection in the above code.A. Replace P_WHERE_STMT with a bind variable.B. Do not use APIs that allow arbitrary query parameters to be exposed.C. Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the code.D. Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection. QUESTION 5Which two statements are true about REF CURSOR types? (Choose two.) A. REF CURSOR types cannot be defined inside a package. B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions. C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a user- defined record. D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function. QUESTION 6Examine the test_tbl table and its contents:What is the outcome of this anonymous PL/SQL block?A. "It was updated" is displayed.B. Successful completion without printing "It was updated".C. A NO_DATA_FOUND exception is thrownD. ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specifiedE. ORA-22920: row containing the LOB value is not locked QUESTION 7Which statements are true about the SecureFile storage paradigm? (Choose two.) A. SecureFile storage can be used for internal and external LOBs. B. Automatic Segment Space Management must be enabled for a tablespace to store SecureFile LOBs. C. SecureFile options enabled for a LOB column can be overridden on a per-LOB basis within the column. D. SecureFile is the default storage paradigm for all LOBs that are stored in locally managed tablespaces if the DB_SECUREFILE parameter is set to ALWAYS. QUESTION 8Examine the DBMS_LOB.LOADCLOBFROMFILE procedure:Which statement is true about DBMS_LOB.LOADCLOBFROMFILE?A. The amount parameter can be set to NULL to indicate to load to the end of the BFILE.B. The amount parameter can be set to DBMS_LOB.LOBMAXSIZE to indicate to load to the end of the BFILE.C. If any error occurs when calling the procedure then it is written to the warning parameter and DBMS_LOB.LOADCLOBFROMFILE finishes successfully.D. An exception will always be raised if amount plus src_offset exceeds the length of the BFILE. QUESTION 9You 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. QUESTION 10Select the correct statement regarding BEQUEATH CURRENT_USER.A. If a view references a PL/SQL function then BEQUEATH CURRENT_USER allows the function toB. execute with DBA privileges, regardless of the invoking user\\'s privileges.C. The BEQUEATH CURRENT_USER clause allows invoker\\'s rights functions referenced in a view to execute with the privileges of the invoking user.D. Any view calling a PL/SQL function with BEQUEATH CURRENT_USER in effect will execute with the privileges of the function owner.E. With the BEQUEATH CURRENT_USER clause, a definer\\'s rights function referenced in a view executes with the privileges of the view owner, not the function owner. QUESTION 11Examine these two columns:Which set of paired items is valid with respect to the DBMS_PARALLEL_EXECUTE package?A. 1-c, 2-b, 3-e, 4-a, 5-dB. 1-b, 2-d, 3-c, 4-e, 5-aC. 1-c, 2-a, 3-b, 4-e, 5-dD. 1-c, 2-d, 3-b, 4-e, 5-aE. 1-e, 2-a, 3-c, 4-b, 5-d QUESTION 12Examine these statements regarding the DBMS_RESULT_CACHE package:1.DBMS_RESULT_CACHE provides subprograms to administer the part of the shared pool used by the PL/SQL functionresult cache only.2.DBMS_RESULT_CACHE provides subprograms to administer the part of the shared pool used by both the PL/SQLfunction result cache and the SQL result cache.3.DBMS_RESULT_CACHE package is owned by the SYS user.4.RESULT_CACHE_MODE can be set using a subprogram defined in the DBMS_RESULT_CACHE package. Which setof statements is correct?A. 2, 3 and 4B. 1 and 4C. 2 and 4D. 1 and 3E. 2 and 3 QUESTION 13This result cache is enabled for the database instance. Examine this code for a PL/SQL function:Which two actions would ensure that the same result will be consistently returned for any session when the same inputvalue is passed to the function? A. Add a parameter, fmt, and change the RETURN statement to: RETURN TO_CHAR (date_hired, fmt); B. Set the RESULT_CACHE_MODE parameter to FORCE. C. Increase the value for the RESULT_CACHE_MAX_SIZE parameter. D. Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function. E. Set the RESULT_CACHE_MAX_RESULT parameter to 0. Time is Up!