Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 181:

  Identify the method that is used by fine-grained access (FGA).

  A. using policy functions to generate predicates dynamically

  B. creating triggers on corresponding tables to generate dynamic predicates

  C. modifying the existing application code to include a predicate for all SQL statements

  D. creating views with necessary predicates, and then creating synonyms with the same name as the tables

  Correct Answer: A

• Question 182:

  You executed the following command:

  SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

  You create a new package called PACK1. View Exhibit1 to examine the PL/SQL code for the PACK1

  package specification and body.

  You issue the following query to see all unique identifiers with a name, such as %1:

  SQL> SELECT NAME, SIGNATURE, TYPE

  FROM USER_IDENTIFIERS

  WHERE NAME LIKE '%1' AND USAGE='DECLARATION'

  ORDER BY OBJECT_TYPE, USAGE_ID;

  View Exhibit2 to examine the output of the query. Which two statements are true about the output of the

  query? (Choose two.)

  

  

  A. The SIGNATURE column has a unique value for an identifier except for identifiers with the same name.

  B. The TYPE column has the value of packages, function or procedures, object types, PL/SQL types, triggers, or exceptions.

  C. The query shows the output for only those identifiers for PL/SQL objects, which are created by the user and are compiled after the ALTER SESSION command.

  D. The ALTER SESSION command automatically collects identifier data and the query shows the output for all the identifiers for PL/SQL objects, which are created by the user.

  Correct Answer: BC

• Question 183:

  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.

  Correct Answer: A

• Question 184:

  Which 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.

  Correct Answer: BC

• Question 185:

  You created an application context successfully. The user OE was granted the EXECUTE privilege on the DBMS_SESSION package. The user receives this error while setting the value for an attribute within the context:

  SQL> EXECUTE DBMS_SESSION.SET_CONTEXT('SALES_ORDERS_CTX','ACCOUNT_MGR','OE');

  BEGIN DBMS_SESSION.SET_CONTEXT('SALES_ORDERS_CTX','ACCOUNT_MGR','OE'); END;

  *

  ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 94 ORA-06512: at line 1 What is the reason for this error?

  A. The context was created with a package name in the USING clause.

  B. The attribute can be set only in the package associated with the context.

  C. The package associated with the context did not exist at the time of creation of the context.

  D. The value for an attribute of a user-defined context can be set only by the ALTER SESSION command.

  Correct Answer: B

• Question 186:

  Which two conditions must be true for a PL/SQL function to be result cached? (Choose two.)

  A. It must be part of a package.

  B. It must be a pipelined table function.

  C. It must not be defined in an anonymous block.

  D. It must have at least one OUT or IN OUT parameter.

  Correct Answer: CD

• Question 187:

  View Exhibit1 and examine the structure of the EMPLOYEES and DEPARTMENTS tables existing in your schema.

  View Exhibit2 and examine the PL/SQL block that you execute to display the department- wise incremented salary for all the departments in your company.

  The code generates an error on execution.

  What correction should be done to ensure the code executes successfully?

  

  

  A. The cursor variable parameter should be passed in IN OUT mode.

  B. The cursor variable should be defined as a strong REF CURSOR type.

  C. The cursor variable name passed as actual and formal parameters should be identical.

  D. The %NOTFOUND cursor attribute cannot be used with the cursor variables and should be replaced with a user defined exception.

  Correct Answer: A

• Question 188:

  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.

  Correct Answer: AD

• Question 189:

  Identify two strategies against SQL injection. (Choose two.)

  A. Using parameterized queries with bind arguments.

  B. Use subprograms that are run with the definer's right.

  C. Use RESTRICT_REFERENCE clauses in functions that use dynamic SQLs.

  D. Validate user inputs to functions that use dynamic SQLs built with concatenated values.

  Correct Answer: AD

• Question 190:

  Examine the commands:

  CREATE TYPE typ_course_tab IS VARRAY(5) OF VARCHAR2(20)

  /

  CREATE TYPE typ_course_nst

  AS TABLE OF typ_course_tab

  /

  CREATE TABLE faculty

  (faculty_id NUMBER(5),

  faculty_name VARCHAR2(30),

  courses typ_course_nst)

  NESTED TABLE courses STORE AS course_stor_tab / INSERT INTO faculty VALUES (101, 'Jones', NULL); UPDATE (SELECT courses FROM faculty WHERE faculty_id=101) SET courses = typ_course_nst

  (11,'Oracle'); Which statement is true about the execution of these commands?

  A. All the commands execute successfully.

  B. Only the first two commands execute successfully.

  C. Only the first four commands execute successfully.

  D. Only the first three commands execute successfully.

  Correct Answer: C