100% Real & Accurate 1Z0-144 Questions and Answers with Free and Fast Updates [Q56-Q74]

5/5 - (3 votes)

100% Real & Accurate 1Z0-144 Questions and Answers with Free and Fast Updates

Get Unlimited Access to 1Z0-144 Certification Exam Cert Guide

Certification Path

The Oracle Database 11g certification includes only one 1Z0-144 exam.

QUESTION 56
View the Exhibit and examine the structure of the EMP table.

Which stages are performed when the above block is executed? (Choose all that apply)

Bind

Parse

Fetch

Execute

QUESTION 57
You create a procedure to handle the processing of bank current accounts which rolls back payment transactions if the overdraft limit is exceeded.
The procedure should return an “error” condition to the caller in a manner consistent with other Oracle server errors.
Which construct should be used to handle this requirement?

The SQLERRM function

The PRAGMA EXCEPTION_INIT function

The RAISE_APPLICATION_ERROR procedure

A user-defined exception used with a raise statement

QUESTION 58
View the Exhibit and examine the structure of the employees table.

Execute the following block of code:

What is the outcome?

It gives an error because group functions cannot be used in anonymous blocks.

It executes successfully and correctly gives the result of the sum of salaries in department 60.

It executes successfully and incorrectly gives the result of the sum of salaries in department 60.

It gives an error because the variable name and column name are the same in the where clause of the select statement.

QUESTION 59
View Exhibit 1 and examine the structure of the employees table.

User SCOTT needs to generate a text report that contains the names of all employees and their salaries.
Examine the following commands issued by the DBA:
SQL>CREATE DIRECTORY my_dir AS ‘/temp/my_files’;
SQL>GRANT WRITE ON DIRECTORY my_dir TO scott;
View Exhibit 2 and examine the procedure code.

You issue the following command:
SQL>EXEC sal_status (‘MY_DIR’, ‘EMPREPORT.TXT’)
What is the outcome?

It executes successfully and creates the report.

It gives an error because the text file should be opened in append mode.

It gives an error because the “no data found” condition is not handled to come out of the loop.

It gives an error because user SCOTT should be granted both read and write privileges to the directory alias.

It executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file.

QUESTION 60
View Exhibit1 and examine the structure of the product table.

View Exhiblt2 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
BEF0RE 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?

because the procedure call in the trigger is not valid “

because the condition specified in the when clause is not valid

because both the procedure and trigger access the same table

because the WHEN clause cannot be used with a row-level trigger

because the column list specified with UPDATE in the trigger is not valid

QUESTION 61
View the Exhibit and examine the structure of the EMP table.

Which stages are performed when the above block is executed? (Choose all that apply)

Bind

Parse

Fetch

Execute

QUESTION 62
The STRING_TAB table has the following structure:

View the Exhibit and examine the code.

What is the outcome on execution?

It displays
Out put buffer not long enough.
This is my test string.-.

It displays only
Output buffer not long enough, and exits the anonymous block.

It displays only
This is my test string. – Because EXCEPTION should have been defined in the anonymous
block to get the error message.

It does not display any of the MEMS_PUTPUT messages and gives an error because a
transaction control statement cannot be used in the exception section of a procedure.

QUESTION 63
View Exhibit1 and examine the structure of the EMP table.

View Exhibit2 and examine the code.

EKPNOS 7845 and 7900 exist in the EMP table.
Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? (Choose two.)

call in line 6

call in line 7

call in line 8

call in line 9

QUESTION 64
ORDER_TOTAL is a column in the orders table with the data type and size as number (8, 2) Examine the following code:

Which statement is correct about the above code?

It gives an error in line 3

It gives an error in line 4

It gives an error in line 6

It executes successfully and displays the output.

QUESTION 65
View Exhibit1 and examine the structure of the product table.

View Exhiblt2 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 BEF0RE 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?

Because the procedure call in the trigger is not valid

Because the condition specified in the when clause is not valid

Because both the procedure and trigger access the same table

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

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

QUESTION 66
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 statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as ‘ST_CLERK’ and salary greater than 3000.

OPEN c1 (NULL, 3000);

OPEN c1 (emp_job, 3000);

OPEN c1 (3000, emp_salary);

OPEN c1 (‘ST_CLERK’, 3000)

OPEN c1 (EMP_job, emp_salary);

QUESTION 67
View the Exhibit to examine the PL/SQL block.

Which statement is true about the execution of the PL/SQL block?

It executes successfully and gives the desired output.

It does not execute because the definition of type population is indexed by varchar2.

It executes, and the string keys of an associative array are not stored in creation order, but in sorted order.

It does not execute because the value that is once assigned to the element of the associative array cannot be changed.

QUESTION 68
Examine the following block of code:

Which line in the above code would result in errors upon execution?

line 5

line 8

line 2

line 7

QUESTION 69
Which two statements are true about triggers? (Choose two.)

All the triggers that are created on a table cannot be disabled simultaneously.

Any user who has the alter privilege on a table can create a trigger using that table.

Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database.

Triggers become invalid if a dependent object, such as 3 stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation.

QUESTION 70
/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?

It goes into an infinite loop.

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

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

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

QUESTION 71
View Exhibit 1 and examine the structure of the EMP table.

View Exhlbit2 and examine the code of the packages that you have created.

You issue the following command:
SQL> DROP PACKAGE manage_emp;
What is the outcome?

It drops both the MANAGE_EMP AND EMP__DET packages because of the cascading effect.

It drops the MANAGE_EMP package and invalidates only the body for the EMP_DET package.

It returns an error and does not drop the MAMAGE_EMP package because of the cascading effect.

It drops the MANAGE_EMP package and invalidates both the specification and body for the EMP_DET package.

QUESTION 72
View the Exhibit and examine the structure of the departments table in SCOTT’s schema.

Examine the following block of code:

The above procedure is created by user SCOTT. Another user JONES needs to use the procedure.
Which two statements are true in the above scenario? (Choose two.)

JONES executes the procedure with definer’s rights.

JONES executes the procedure with invoker’s rights.

SCOTT should grant only the execute privilege for the procedure to JONES.

SCOTT should grant both the execute privilege for the procedure and insert privilege for the table to JONES.

QUESTION 73
Examine the following package specification.
SQL>CREATE OR REPLACE PACKAGE emp_pkf IS
PROCEDURE search_emp (empdet NUMBER);
PROCEDURE search_emp (empdet DATE);
PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2
PROCEDURE search_emp (empdet NUMBER); RETURN DATE
END emp_pkg
/
The package is compiled successfully
Why would it generate an error at run tune?

Because function cannot be overload.

Because function cannot differ only in return type.

Because all the functions and procedures in the package cannot have the same number of parameters with the same parameter name.

Because the search EMP (EMPDET NUMBER) procedure and the SEARCH_DEPT (EMPDET NUMBER) cannot have identical parameter names and data types.

QUESTION 74
Examine the following partial code:

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

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

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

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

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