Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 11:

  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 userdefined record.
  D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.
  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 userdefined record.

• Question 12:

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

• Question 13:

  Examine the structure of the TEXT_TAB table. Name Null? Type

  TEXT_ID NUMBER

  DOC1 CLOB

  DOC2 CLOB

  You issue the following INSERT commands:

  INSERT INTO text_tab VALUES (1, 'This is line 1',null);

  INSERT INTO text_tab VALUES (2, 'This is line 1','This is line 2');

  Then you execute the following block of the PL/SQL code:

  DECLARE

  vc1 VARCHAR2(1000):= 'This is the preface';

  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?

  A. It executes successfully.
  B. It gives an error because VARCHAR2 should be explicitly converted to CLOB.
  C. It gives an error because CLOB variables should be initialized to EMPTY_CLOB().
  D. It gives an error because the concatenation operator cannot be used with the CLOB data type.
  A. It executes successfully.

• Question 14:

  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.
  A. It executes successfully and gives the required output.

• Question 15:

  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.
  B. The attribute can be set only in the package associated with the context.

• Question 16:

  Which two queries' results cannot be cached? (Choose two.)

  A. queries having the GROUP BY clause
  B. queries having the ORDER BY clause
  C. the query on dictionary and temporary tables
  D. queries having SYSDATE and SYS_TIMESTAMP SQL functions
  C. the query on dictionary and temporary tables
  D. queries having SYSDATE and SYS_TIMESTAMP SQL functions

• Question 17:

  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
  A. using policy functions to generate predicates dynamically

• Question 18:

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

  A. LAST and COUNT give different values.
  B. LAST and COUNT give the same values.
  C. The four new elements that are added contain the value 33.
  D. The four new elements that are added contain the value 44.
  A. LAST and COUNT give different values.
  C. The four new elements that are added contain the value 33.

• Question 19:

  You created the SALES_ORDERS_CTX context to use the

  OE.SALES_ORDERS_PKG package.

  View Exhibit1 and examine the package that is used with the context. View Exhibit2 to examine the policy defined and the logon trigger.

  A user receives the following error when he or she executes a query:

  ERROR at line 2:

  ORA-28112: failed to execute policy function

  What could be the reason for the error?

  A. The user has insufficient privileges on the DBMS_SESSION package.
  B. The subprograms inside the package have not been created with the invoker's right.
  C. The THE_PREDICATE function has an insufficient number of parameters in the package.
  D. The policy is created by using SALES_ORDERS_PKG.THE_PREDICATE without a parameter.
  C. The THE_PREDICATE function has an insufficient number of parameters in the package.

• Question 20:

  View the Exhibit to examine a Java source file.

  

  You have the corresponding Java class file and you execute the command as follows:

  SQL> CREATE OR REPLACE PROCEDURE ccformat

  (x IN OUT VARCHAR2)

  AS LANGUAGE JAVA

  NAME 'FormatCreditCardNo.formatCard()';

  Which statement is true about the command?

  A. It loads the Java class method into Oracle Database and publishes it.
  B. It publishes the Java class method, but the CCFORMAT PL/SQL procedure fails when it is executed.
  C. It creates the CCFORMAT PL/SQL subprogram without publishing, which can be used to invoke the Java class method.
  D. It publishes the Java class method and the CCFORMAT PL/SQL procedure invokes the Java class method when it is executed.
  B. It publishes the Java class method, but the CCFORMAT PL/SQL procedure fails when it is executed.