Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 211:

  Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.

  CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,

  deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),

  salary Number(8),

  deptname VARCHAR2(30) DEFAULT 'Sales')

  /

  DECLARE

  SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;

  v_emprec v_emprec_subtype;

  BEGIN

  v_emprec.empid := NULL; v_emprec.salary := 10000.002;

  v_emprec.deptid := 50;

  DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname);

  END;

  /

  Which statements are true about the above PL/SQL block? (Choose two.)

  A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.

  B. Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited.

  C. Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal.

  D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.

  Correct Answer: AD

• Question 212:

  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.

  Correct Answer: B

• Question 213:

  You have an external C procedure stored in a dynamic-link library (DLL). The C procedure takes an integer as argument and returns an integer. You want to invoke the C procedure through a PL/SQL program.

  View the Exhibit.

  Which statement is true about the C_OUTPUT PL/SQL program?

  

  A. It invokes the external C procedure.

  B. It only publishes the external C procedure.

  C. It fails because the external C procedure is not published.

  D. It fails because the input data type is BINARY_INTEGER and the external C procedure expects an integer.

  Correct Answer: C

• Question 214:

  Identify three guidelines for the DBMS_ASSERT package. (Choose three.)

  A. Prefix all calls to DBMS_ASSERT with the SYS schema name.

  B. Embed DBMS_ASSERT verification routines inside the injectable string.

  C. Escape single quotes when you use the ENQUOTE_LITERAL procedure.

  D. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions.

  E. Prefix all calls to DBMS_ASSERT with a schema name that owns the subprogram that uses the DBMS_ASSERT package.

  Correct Answer: ACD

• Question 215:

  Which two statements are true about nested tables and varrays? (Choose two.)

  A. Only varrays must have consecutive numbers as subscripts.

  B. Only nested tables can be used as column types in database tables.

  C. Both nested tables and varrays must have consecutive numbers as subscripts.

  D. Both nested tables and varrays can be used as column types in database tables.

  Correct Answer: AD

• Question 216:

  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.

  A. The RESULT_CACHE_MODE parameter is set to FORCE.

  B. The RESULT_CACHE_MODE parameter is set to MANUAL.

  C. The database instance is not able to allocate memory for the result cache.

  D. The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.

  Correct Answer: D

• Question 217:

  View the Exhibit and examine the PL/SQL code.

  The code takes a long time to execute. What would you recommend to improve performance?

  

  A. using NOT NULL constraint when declaring the variables

  B. using the BULK COLLECT option for query instead of cursor

  C. using WHILE.. END LOOP instead of FOR .. END LOOP

  D. using the SIMPLE_INTEGER data type instead of the NUMBER data type

  Correct Answer: B

• Question 218:

  Which two statements are true about associative arrays and varrays? (Choose two.)

  A. Only varrays must use sequential numbers as subscripts.

  B. Only varrays can be used as column types in database tables.

  C. Both associative arrays and varrays must use sequential numbers as subscripts.

  D. Both associative arrays and varrays can be used as column types in database tables.

  Correct Answer: AB

• Question 219:

  You created a procedure as follows:

  CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)

  IS

  stmt VARCHAR2(100);

  pname VARCHAR2(20);

  BEGIN

  stmt:='SELECT product_name FROM products WHERE product_id=:2';

  EXECUTE IMMEDIATE stmt INTO pname USING twhr;

  DBMS_OUTPUT.PUT_LINE(pname);

  END;

  /

  View the Exhibit to examine the structure of PRODUCTS table.

  Which statement is true about the procedure?

  

  A. It produces an error when invoked.

  B. It can be invoked only from a PL/SQL block.

  C. It reduces the chances of SQL injection by using bind arguments.

  D. The values for bind arguments remain persistent in the session after the execution of the procedure.

  Correct Answer: C

• Question 220:

  View the Exhibit and examine the procedure to create a trigger name based on the table name supplied to the procedure.

  Which three statements are appropriate for protecting the code in the procedure from SQL injection? (Choose three.)

  

  A. Explicitly validate the identifier length limit.

  B. Add AUTHID DEFINER to the definition of the procedure.

  C. Use PRAGMA RESTRICT_REFERENCES in the procedure.

  D. Filter out control characters in user-supplied identifier names.

  E. Use the object ID of the table from the data dictionary to build the trigger name.

  Correct Answer: ADE