Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 11:

  Identify the three options which make a PL/SQL function highly suitable for using the RESULT_CACHE clause in its definition. (Choose three.)

  A. The tables which are read in the function are very frequently updated.

  B. The code in the function performs more write operations on the database when compared to the read operations.

  C. The function has complex business logic depending on more than five tables to compute its return values.

  D. The function involves highly computational logic with minimal or no database access.

  E. The value returned by the function is deterministic for a given set of input parameters.

  F. The logic in the function accesses tables or uses package variables which are modified very rarely.

  Correct Answer: DEF

• Question 12:

  Examine this statement: Which result is expected after executing this anonymous block?

  

  A. PLS-00316: PL/SQL TABLEs must use a single index

  B. 60

  C. PLS-00382: expression is of wrong type

  D. 30

  E. ORA-06533: Subscript beyond count

  Correct Answer: A

  

• Question 13:

  Which statement is true in regard to minimizing attack surface during application design?

  A. Reusable components should be developed and used extensively.

  B. Only one user account should be used.

  C. All the code should run anonymously.

  D. Private functions and procedures should be declared in package specifications.

  Correct Answer: A

  Reference: https://download.oracle.com/oll/tutorials/SQLInjection/html/lesson1/les01_tm_avoid1.htm

• Question 14:

  Your company's executive team voted to give a one-time special bonus, the amount of which is to be based on number of years of service. The bonus table will be updated using a procedure called

  update_bonus().

  Examine this PL/SQL block which shows the logic to calculate the bonus:

  

  Consider these possible optimization actions:

  1.

  Remove the service_days calculation statement.

  2.

  Move bonus1 and bonus2 initializations out of the loop.

  3.

  Inline the update_bonus() subprogram.

  4.

  Call update_bonus(), passing the literal bonus amount instead of the bonus1 or bonus2 variables. Which set of optimizations will be performed if this block is compiled with PLSQL_OPTIMIZE_LEVEL = 2?

  A. 3 and 4 only

  B. 1, 2, 3, and 4

  C. 1, 2, and 4 only

  D. 1 and 2 only

  E. 1 and 3 only

  Correct Answer: D

• Question 15:

  The EMPLOYEES table exists in your schema and has columns LAST_NAME of data type VARCHAR2

  and DEPARTMENT_ID of data type NUMBER.

  There are six employees in DEPARTMENT_ID, 20.

  Examine this code:

  

  What will be the outcome on execution?

  A. The execution of this code will fail with an invalid cursor error and not display either output message.

  B. The number of employees will be displayed as six for both output messages.

  C. The number of employees will be displayed as six for the first output message and zero for the second output message.

  D. The number of employees will be displayed as six for the first output message and an invalid cursor error will be thrown instead of displaying the second output message.

  Correct Answer: D

• Question 16:

  Examine this PL/SQL block:

  

  If PLSQL_CCFLAGS is set to: 'flag1:TRUE, flag2:2, flag3:NULL' What is the result of submitting this PL/SQL block for execution with SERVEROUTPUT enabled?

  A. It fails compilation.

  B. It executes successfully and outputs "Line: 3".

  C. It executes successfully and outputs "Line: 5".

  D. It executes successfully and outputs "Line: ".

  E. It executes successfully and outputs "Line: 10".

  F. It executes successfully and outputs "Line: 8".

  Correct Answer: E

• Question 17:

  Which statement describes the plshprof command-line utility?

  A. It produces HTML output from raw profiler output.

  B. It executes a script to create hierarchical profiler tables in the database.

  C. It produces raw profiler output for the most recently executed PL/SQL block.

  D. It produces HTML output from hierarchical profiler tables in the database.

  E. It stores hash values in hierarchical profiler tables in the database.

  Correct Answer: A

  Reference: https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_profiler.htm#CHDCIECE

• Question 18:

  Examine these statements:

  1.

  A function with invoker's rights can be result cached by using both AUTHID CURRENT_USER and RESULT_CACHE in its declaration.

  2.

  A function with invoker's rights cannot be result cached because it may allow a user to retrieve data cached by another user.

  3.

  Results in the result cache for an invoker's rights function will be cached by the value of CURRENT_SCHEMA in the invoking environment.

  4.

  By default, declaring a function with RESULT_CACHE makes it an invoker's rights result cached function.

  5.

  A function with invoker's rights can be result cached because the function result cache is created per user for invoker's rights functions.

  Identify the set of correct statements with respect to invoker's rights and function result caching.

  A. 1, 3, 4 and 5

  B. 1 and 3

  C. 1, 3 and 5

  D. 2 only

  E. 1, 3 and 4

  Correct Answer: D

• Question 19:

  Examine this code: Examine this query:

  

  SELECT ename, d.dname, job, sal, comm FROM emp e, dept d WHERE d.deptno = e.deptno;

  Which statement is correct regarding the execution of this SQL query?

  A. The query will only fetch records pertaining to department 30 with SAL and COMM displaying the actual data from the table.

  B. The query will fetch records from all departments with SAL and COMM values displayed as NULL for all records other than department 30.

  C. The query will only fetch records pertaining to department 30 with SAL and COMM data replaced by "xxxxx".

  D. The query will only fetch records pertaining to department 30 with SAL and COMM values displayed as NULL.

  Correct Answer: A

  Reference: http://www.baskent.edu.tr/~tkaracay/etudio/ders/dbase/sql/htmSQL/OracleDers10.htm

• Question 20:

  Which two statements are true about conversion from DBMS_SQL to native dynamic SQL? (Choose two.)

  A. The DBMS_SQL.TO_REFCURSOR function should be used when the number of bind variables is known at compile time, but not the number of items to be selected.

  B. The DBMS_SQL.TO_REFCURSOR function converts a SQL cursor number to a weakly typed cursor variable only after it has been opened, parsed, and executed via DBMS_SQL.

  C. DBMS_SQL operations can access the cursor only as the SQL cursor number, not as a REF CURSOR variable.

  D. The DBMS_SQL.TO_REFCURSOR function allows a query result to be returned implicitly from a non PL/SQL environment to a stored PL/SQL program.

  E. The DBMS_SQL.IS_OPEN function is used to verify that a SQL cursor number is open after it has been converted to a REF CURSOR.

  Correct Answer: AC

  Reference: https://books.google.com.pk/books?id=iWhbFeboD84Candpg=PA556andlpg=PA556anddq=The

  +DBMS_SQL.TO_REFCURSOR+function+should+be+used+when+the+number+of+bind+variables+is +known+at+compile+time,+but+not +the+number+of+items+to+be +selectedandsource=blandots=yAxmV8X7IEandsig=ACfU3U3_BJFUSzRWrtszG1LG7YVTt9j4wandhl=enandsa=Xandved=2ahUKEwiY7ueY-6boAhXBe30KHcBBCDcQ6AEwAHoECBQQAQ#v=onepageandq=The% 20DBMS_SQL.TO_REFCURSOR %20function%20should%20be%20used%20when%20the%20number%20of%20bind%20variables%20is% 20known%20at%20compile%20time%2C%20but%20not%20the%20number%20of%20items%20to % 20be%20selectedandf=false

  https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm