Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 101:

  Examine this Java method in class Employee, loaded into the Oracle database:

  Public static int updateSalary (String name, float salary) {...}

  Which PL/SQL specification can be used to publish this method?

  A. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA LIBRARY “Employee” NAME “updateSalary” PARAMETERS (p_nm java.lang. String, p_sal float, RETURN int);

  B. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA

  NAME “Employee.updateSalary”

  PARAMETERS (p_nm java.lang.String, p_sal float, RETURN int);

  C. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA NAME “Employee.updateSalary” PARAMETERS (“name” java.lang.String, “salary” float, RETURN int);

  D. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA NAME ‘Employee.updateSalary (java.lang.String, float) return int’;

  E. CREATE FUNCTION update_salary (p_nm VARCHAR2, p_sal NUMBER) RETURN PLS_INTEGER AS LANGUAGE JAVA NAME ‘int Employee.updateSalary (java.lang.String, float)’;

  Correct Answer: D

• Question 102:

  Which two are correct when migrating BasicFile LOBs to SecureFile LOBs by DBMS_REDEFINITION? (Choose two.)

  A. Online redefinition can be done only at the table level.

  B. Specify only BasicFiles LOB and SecureFiles LOB column names in parameter col_mapping of DBMS_REDEFINITION.START_REDEF_TABLE.

  C. Set the database initialization parameter db_securefile to NEVER.

  D. During migration, specify the NOLOGGING storage parameter for any new SecureFiles LOB columns.

  E. Online redefinition is the recommended method for migration of BasicFile LOBs to Secure LOBs.

  Correct Answer: BE

• Question 103:

  Which two PL/SQL blocks give the output “this is a test message”? (Choose two.)

  A. BEGIN DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_NAME (‘this is a test message’, TRUE)); END;

  B. BEGIN DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_NAME (‘this is a “ “test” ” message’, TRUE)); END;

  C. BEGIN DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_NAME (‘ “this is a “test” message” ’, TRUE)); END;

  D. BEGIN DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_NAME (‘ “this is a test message” ’, TRUE)); END;

  E. BEGIN DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_NAME (‘this is a test message ’, FALSE)); END;

  Correct Answer: DE

• Question 104:

  The STUDENTS table exists in your schema.

  Examine the DECLARE section of a PL/SQL block:

  DECLARE TYPE studentcur_t IS REF CURSOR RETURN students%ROWTYPE; TYPE teachercur_t IS REF CURSOR:

  cursor1 studentcur_t;

  cursor2 teachercur_t;

  cursor3 SYS_REFCURSOR;

  CURSOR stcur IS SELECT * FROM students;

  Which two blocks are valid?

  A. BEGIN OPEN cursor3 FOR SELECT * FROM students; cursor1 :=cursor3; END;

  B. BEGIN OPEN stcur; cursor1 :=stcur; END;

  C. BEGIN OPEN cursor1 FOR SELECT * FROM students; stcur :=cursor1; END;

  D. BEGIN OPEN stcur; cursor3 :=stcur; END;

  E. BEGIN OPEN cursor1 FOR SELECT * FROM students; cursor2 :=cursor1; END;

  Correct Answer: AE

• Question 105:

  A products TABLE exists with a PROD_ID column.

  Examine this PL/SQL block: Which statement is true?

  

  A. It executes successfully only if v_ref_cur := DBMS_SQL.TO_REFCURSOR (V_CUR); is added before the FETCH statement.

  B. It executes successfully.

  C. It executes successfully only if v_ref_cur: = DBMS_SQL.TO_CURSOR_NUMBER (v_cur); is added before the FETCH statement.

  D. It executes successfully only if the FETCH statement is replaced by DBMS_SQL.RETURN_RESULT (v_ref_cur);

  E. It executes successfully only if the FETCH statement is replaced by DBMS_SQL.FETCH_ROWS (v_cur);

  Correct Answer: D

• Question 106:

  You are logged on to the SCOTT schema and the schema has EMP and DEPT tables already created:

  Examine this PL/SQL procedure:

  

  Which PL/SQL block will raise an exception?

  A. EXEC get_tab_row_count (‘emp’);

  B. EXEC get_tab_row_count (‘SCOTT.EMP’);

  C. EXEC get_tab_row_count (‘ “EMP” ’);

  D. EXEC get_tab_row_count (‘DEPT’);

  E. EXEC get_tab_row_count (‘DEPT, EMP’)

  Correct Answer: E

• Question 107:

  Which statement describes the purpose of the plshprof command?

  A. It produces HTML output from raw profiler output.

  B. It produces HTML output from profiler tables in the database.

  C. It populates profiler tables in the database from raw profiler output.

  D. It produces raw profiler output on the most recently run applications.

  Correct Answer: A

• Question 108:

  Examine the structure of the EMPLOYEES table in the SCOTT schema. Name Null? Type

  EMPLOYEE_ID NOT NULL NUMBER(6)

  FIRST_NAME VARCHAR2(20)

  LAST_NAME NOT NULL VARCHAR2(25)

  SALARY NOT NULL NUMBER(8,2)

  COMMISSION_PCT NUMBER(2,2)

  DEPARTMENT_ID NUMBER(4)

  View the Exhibit and examine the code for the EMP_TOTSAL procedure created by user SCOTT.

  Which statement is true regarding the EMP_TOTSAL procedure?

  

  A. It is created successfully, but displays the correct output message only for existent employee IDs.

  B. It is created successfully and displays the correct output message for both existent and nonexistent employee IDs.

  C. It generates an error because the %NOTFOUND attribute cannot be used in combination with a SELECT INTO statement.

  D. It generates an error because a user-defined exception has to be included whenever the % NOTFOUND attribute is used in combination with a SELECT INTO statement.

  Correct Answer: A

• Question 109:

  Which two statements are true about cursor variables? (Choose two.)

  A. Cursor variables can be parameterized like cursors.

  B. The query associated with a cursor variable cannot reference host variables and PL/SQL variables.

  C. The FETCH statement executes the query associated with a cursor variable and identifies the result set.

  D. Cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) can be applied to a cursor variable.

  E. The OPEN FOR statement executes the query associated with a cursor variable and identifies the result set.

  Correct Answer: DE

• Question 110:

  Examine this code:

  

  The anonymous block fails this error stack:

  

  Which two changes, when separately applied, would prevent these errors from occurring?

  A. Define v2 as employees%ROWTYPE.

  B. Initialize v1 and v2 with appropriate constructor functions.

  C. Define v2 as pkg. rec.

  D. Nothing because using the function TABLE (V1) is prohibited.

  E. Define v1 as employees%ROWTYPE.

  Correct Answer: AC