Oracle 1Z0-144 Dumps 2021

NEW QUESTION 1
In which of the following scenarios would you recommend using PL/SQL records?

• A. when you want to retrieve an entire row from a table and perform calculations
• B. when you know the number of elements in advance and the elements are usually accessed sequentially
• C. when you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries
• D. when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked

Answer: CD

NEW QUESTION 2
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.

• 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);

Answer: D

NEW QUESTION 3
View the Exhibit and examine the structure of the employees table.

Execute the following block of code:

What is the outcome?

• A. It gives an error because group functions cannot be used in anonymous blocks
• B. It executes successfully and correctly gives the result of the sum of salaries in department 60.
• C. It executes successfully and incorrectly gives the result of the sum of salaries in department 60.
• D. It gives an error because the variable name and column name are the same in the where clause of the select statemen

Answer: C

NEW QUESTION 4
User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database.
SCOTT creates the following trigger:
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object');
END:
SCOTT does not grant the execute privilege on this trigger to any other users.
For which user(s) would this trigger fire by default when they drop an object in the hr schema?

• A. Only HR
• B. SCOTT and HR
• C. Only SCOTT
• D. SCOTT, HR, and SYS

Answer: A

NEW QUESTION 5
Which two guidelines are recommended by Oracle to reduce invalidation of dependent objects? (Choose two.)

• A. Reference tables indirectly by using view
• B. Reference tables directly avoid using view
• C. When adding new items to a package, add them to the end of the packag
• D. When adding new items to a package, add them to the beginning of the packag

Answer: AC

NEW QUESTION 6
Examine the following command:
SQL>ALTER SESSION
SET plsql_warnings *
'enable: severe',
'enable: performance',
'ERROR: 05003';
What is the implication of the above command?

• A. It issues a warning whenever ERROR: 05003 occur during compilatio
• B. It causes the compilation to fail whenever the warning ERROR.05003 occur
• C. It issues warnings whenever the code causes an unexpected action or wrong results performance problem
• D. It causes the compilation to fail whenever the code gives wrong results or contains statements that are never execute

Answer: C

NEW QUESTION 7
Which two tasks should be created as functions instead of as procedures? (Choose two.)

• A. Reference host or bind variables in a PL7SQL block of code
• B. Tasks that compute and return multiple values to the calling environment
• C. Tasks that compute a value that must be returned to the calling environment
• D. Tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application

Answer: AC

Explanation: Explanation/Reference:
Functions are used to return a value. Functions must return only a single value.
Procedure are used to perform an action.
Both functions and procedures are using to do a special task or action. In functions it is
must to return a single value, where as in procedures it’s not compulsory

NEW QUESTION 8
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?

• A. Because function cannot be overload
• B. Because function cannot differ only in return typ
• C. Because all the functions and procedures In the package cannot have the same number of parameters with the same parameter name
• D. Because the search EMP (EMPDET NUMBER) procedure and the SEARCH_DEPT (EMPDET NUMBER) cannot have identical parameter names and data types

Answer: B

NEW QUESTION 9
You want to create a trigger that fires whenever rows are deleted from the customer table and that displays the number of rows remaining in the table.
Which two statements are correct about the trigger to be created for the above requirement? (Choose two.)

• A. It should be an after trigge
• B. It should be a before trigge
• C. It should be a row-level trigge
• D. It should be a statement-level trigge
• E. It can be a before or an after trigge

Answer: AC

NEW QUESTION 10
View the exhibit and examine the structure of the EMPLOYEE table.
EMPLOYEE_SEQ is an existing sequence.
Examine the following block of code:

Which statement is true about the above block of code?

• A. It consists of two transactions
• B. It consists of a single transaction,
• C. The data is automatically committed after the block execution ends,
• D. It gives an error on execution because sequences cannot be used in anonymous block

Answer: A

NEW QUESTION 11
View the Exhibit to examine the PL/SQL code.

The record for the employee with employee__id 100 in the employees table is as follows;

Identify the correct output for the code.

• A. King 17-JUN-87 1500
• B. King 17-JUN-87 24000
• C. King current sysdate 1500
• D. King current sysdate 24000

Answer: A

NEW QUESTION 12
View the Exhibit to examine the PIVSQL block.

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

• A. It executes and the Output is emprec.deptname: .
• B. It executes and the Output is emprec.deptname: Sale
• C. It produces an error because NULL is assigned to the emprec.empid field in the recor
• D. It produces an error because the CHECK constraint is violated while assigning a value to the emprec.deptid field in the recor

Answer: A

NEW QUESTION 13
Examine the following partial declare section from a block of PL/SQL code

Which line(s) in the above code are NOT valid? (Choose all that apply.)

• A. line 2
• B. line 3
• C. line 4
• D. line 5

Answer: BD

NEW QUESTION 14
Which two statements correctly differentiate functions and procedures? (Choose two.)

• A. A function can be called only as part of a SQL statement, whereas a procedure can be called only as a PL7SQL statemen
• B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environmen
• C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statemen
• D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environmen

Answer: BC

NEW QUESTION 15
Identify two situations where the DBMS_SQL package should be used. (Choose two.)

• A. The SELECT list is not known until run tim
• B. The dynamic SQL statement retrieves rows into record
• C. You do not know how many columns a select statement will return, or what their data types wil
• D. You must use the %found SQL cursor attribute after issuing a dynamic SQL statement that is an insert or update statemen

Answer: AC

NEW QUESTION 16
Which statements are true about the WHEN OTHERS exception handler? (Choose all that apply)

• A. It can be the first exception handle
• B. It can be the only exception handler for the cod
• C. It traps all the exceptions that are not already trappe
• D. You can have multiple OTHERS clauses to trap all the multiple unhandled exception

Answer: C

Explanation: Reference:
http://www.techonthenet.com/oracle/exceptions/when_others.php

NEW QUESTION 17
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 tabl
• B. The attributes of fields in the record with the %ROWTYPE attribute can be modified manuall
• C. The attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variabl
• D. It ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altere

Answer: CD