1Z0-144 Online Practice Questions and Answers

/temp/my_files is an existing folder in the server, facultylist.txt is an existing text file in this folder.

Examine the following commands that are executed by the DBA:

SQL>CREATE DIRECTION my_dir AS `/temp/my_files': SQL>GRANT READ ON DIRECTORY my_dir To public:

View the Exhibit and examine the procedure created by user SCOTT to read the list of faculty names from the text file.

SCOTT executes the procedure as follows:

SQL>SET SERVEROUTPUT ON

SQL>EXEC read_file (`MY_DIR', FACULTYLIST.TXT')

What is the outcome?

A. It goes into an infinite loop.

B. It executes successfully and displays only the list of faculty names.

C. It does not execute and displays an error message because the end-of-file condition is not taken care of.

D. It executes successfully and displays the list of faculty names followed by a "no data found" error message.

View the Exhibit and examine the code and its outcome on execution:

What would be the effect on the two procedures if the value of debug is set to FALSE? (Choose two.)

A. MY_PROC2 is not recompiled.

B. MY_PROC1 is recompiled but remains unchanged.

C. MY_PROC2 is recompiled but remains unchanged.

D. MY_PROC1 is recompiled without the debugging code.

View the Exhibit and examine the blocks of code that you plan to execute.

Which statement is true about the blocks of code?

A. All the blocks execute successfully and the anonymous block displays123cant: 4545cnt: 45.

B. All the blocks execute successfully and the anonymous block displays123cut: 045cart: 1.

C. The anonymous block gives an error because the function invocation in line 2 is not valid.

D. The procedure creation gives an error because the function invocation in line 1 is not valid.

Examine the following snippet of PL/SQL code:

View the Exhibit for table description of EMPLOYEES table. The EMPLOYEES table has 200 rows.

Identify OPEN statements for opening the cursor that fetches the result set consisting of employees with JOB_ID as `ST_CLERK' and SALARY greater than 3000. (Choose three.)

A. OPEN c1 (NULL, 3000);

B. OPEN c1 (emp_job, 3000);

C. OPEN c1 (3000, emp_salary);

D. OPEN c1 (`ST_CLERK', 3000)

E. OPEN c1 (emp_job, emp_salary);

Which tasks must be performed during the installation of the UTL_MAIL package? (Choose two.)

A. setting the UTL_FILE_DIR initialization parameter

B. running the utlmail.sql and prvtmail.plb scripts

C. setting the SMTP_OUT_SERVER initialization parameter

D. using the CREATE DIRECTORY statement to associate an alias with an operating system directory

E. granting read and WRITE privileges to control the type of access to files in the operating system

View Exhibit 1 and examine the structure of the product table.

View Exhibit 2 and examine the procedure you created. The procedure uses the prod id to determine whether the list price is within a given range.

You then create the following trigger on the product table.

CREATE OR REPLACE TRIGGER check_price__trg BEFORE INSERT OR UPDATE OF prod_id, prod_list_price ON product FOR EACH ROW WHEN (nev.prod_id <> NVX(old.prod_id,0) OR New.prod__list_price <> NVL(old.prod_list_price, 0) ) BEGIN check_price (: new.prod_id) ; END /

Examine the following update command for an existing row in the product table.

SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115;

Why does it generate an error?

A. Because the procedure call in the trigger is not valid

B. Because the condition specified in the when clause is not valid

C. Because both the procedure and trigger access the same table

D. Because the WHEN clause cannot be used with a row-level trigger

E. Because the column list specified with UPDATE in the trigger is not valid

Examine the following partial code:

Which statement is correct about the unnamed block of code at the end of a package body?

A. It generates an error because all the blocks of code in a package body must be named.

B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specification.

C. It acts as a package initialization block that executes once, when the package is first invoked within the user session.

D. It acts as a package initialization block that executes each time a package subprogram is invoked within the user session and refreshes the initialized variable's value.

Which two statements are true about the %ROWTYPE attribute? (Choose two.)

A. It is used to declare a record that can hold multiple rows of a table.

B. The attributes of fields in the record with the %ROWTYPE attribute can be modified manually.

C. The attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable.

D. It ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered.

View the Exhibit to examine the PL/SQL code.

SERVEROUTPUT is on for the session.

Which statement is true about the execution of the code?

A. The execution fails because of the misplaced else clause.

B. The execution is successful even if there is no employee with EMPLOYEE_ID 115.

C. The execution fails and throws exceptions if no employee with EMPLOYEE_ID 115 is found.

D. The execution is successful, but it displays an incorrect output if no employee with EMPLOYEE_ID 115 is found.

View the exhibit and examine the structure of the EMPLOYEES table.

EMPLOYEES_SEQ is an existing sequence. Examine the following block of code: Which statement is true about the above block of code?

A. It gives an error on execution because sequences cannot be used in anonymous blocks.

B. It consists of a single transaction.

C. It consists of two transactions.

D. The data is automatically committed after the block execution ends.