Oracle 1Z0-144 Free Practice Questions

Q1. 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 statement. 

B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment. 

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

D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environment. 

View Answer

Q2. You create the following table and execute the following code: 

Which statement is true about the outcome of the above code? 

A. It executes successfully and all the rows are updated. 

B. It gives an error but saves the inserted rows and the update to the first row. 

C. It gives an error but saves the inserted rows; however, no rows are updated. 

D. It gives an error and all the data manipulation language (DML) statements are rolled back 

View Answer

Q3. 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 compilation. 

B. It causes the compilation to fail whenever the warning ERROR.05003 occurs. 

C. It issues warnings whenever the code causes an unexpected action or wrong results performance problems. 

D. It causes the compilation to fail whenever the code gives wrong results or contains statements that are never executed. 

View Answer

Q4. Which statement is true about transactions in PL/SQL? 

A. A transaction can span multiple blocks. 

B. A block can contain only a single transaction. 

C. SERVERPOINTS cannot be created in a PL/SQL block. 

D. The END keyword signals the end of a PL/SQL block and automatically commits the transaction in the block. 

View Answer

Q5. What is the correct definition of the persistent state of a packaged variable? 

A. It is a private variable defined in a procedure or function within a package body whose 

value is consistent within a user session. 

B. It is a public variable in a package specification whose value is consistent within a user session. 

C. It is a private variable in a package body whose value is consistent across all current active sessions. 

D. It is a public variable in a package specification whose value is always consistent across all current active sessions. 

View Answer

Q6. Which statements are true about database triggers? (Choose all that apply.) 

A. They can invoke only PL/SQL procedures 

B. They can include SQL and PL/SQL or calls to Java procedures. 

C. They are implicitly fired by an event that must occur within an application 

D. They are implicitly fired when a triggering event occurs, depending on which user is connected 

View Answer

Q7. The STRING_TAB table has the following structure: 

View the Exhibit and examine the code. 

What is the outcome on execution? 

A. It displays 

Output buffer not long enough. 

This is my test string.-. 

B. It displays only 

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

C. It displays only 

This is my test string. - Because EXCEPTION should have been defined in the anonymous 

block to get the error message. 

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

View Answer

Q8. View the Exhibit and examine the structure of the employees table. 

Examine the following block of code: 

What is the outcome when the above code is executed? 

A. It executes successfully. 

B. It gives an error because decode cannot be used in a PL/SQL block. 

C. It gives an error because the AVG function cannot be used in a PL/SQL block 

D. It gives an error because the MONTHS_BETWEEN function cannot be used in a PL/SQL block. 

E. It gives an error because both the AVG and decode functions cannot be used in a PL/SQL block. 

View Answer

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

The salary of EMPLOYEE_ID 195 is 2800. 

You execute the following code 

What is the outcome? 

A. It gives an error because only the innermost block is labeled. 

B. It gives an error because the same variable name cannot be used across all the nested blocks. 

C. It executes successfully and displays the resultant values in the following sequence-1000, 2800 50000, 2800. 

D. It executes successfully and displays the resultant values in the following sequence: 1000, 2800, 50000, 1000. 

View Answer

Q10. Which tasks must be performed during the installation of the UTL_MAIL package? (Choose all that apply.) 

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 Answer

Q11. Which statements are true about the WHEN OTHERS exception handler? (Choose all that apply) 

A. It can be the first exception handler. 

B. It can be the only exception handler for the code. 

C. It traps all the exceptions that are not already trapped. 

D. You can have multiple OTHERS clauses to trap all the multiple unhandled exceptions. 

View Answer

Q12. View Exhibit1 and examine the structure of the DO table. 

View Exhibit2 and examine the code. 

The anonymous block gives an error on execution. What is the reason? 

A. The assignment in line 7 is not valid. 

B. The SQL does not support the Boolean data type. 

C. A null value cannot be applied to the bind arguments In the using clause in line 10 

D. The names of bind variables must be the same as the using clause bind arguments in line 10 

View Answer

Q13. Which two statements are true about PL/SQL exception propagation? (Choose two.) 

A. The exception reproduces Itself In successive enclosing blocks until a handler is found. 

B. Exception- can propagate across the remote subprograms that are called through database links. 

C. If you declare a local exception in a subblock and a global exception in the outer block, the local declaration overrides the global exception. 

D. If you declare a local exception in a subblock and a global exception in the outer block, the global declaration overrides the local exception. 

View Answer

Q14. 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: Sales. 

C. It produces an error because NULL is assigned to the emprec.empid field in the record. 

D. It produces an error because the CHECK constraint is violated while assigning a value to the emprec.deptid field in the record. 

View Answer

Q15. Which three statements are true about wrapping? (Choose three.) 

A. The PL/SQL wrapper detects and reports only syntax errors. 

B. The PL/SQL wrapper detects and reports both syntax and semantic errors. 

C. When wrapping a package or object type, both the body and specification should be wrapped. 

D. When wrapping a package or object type, only the body should be wrapped, not the specification. 

E. To change a wrapped object, the original source code needs to be modified and then wrapped again 

F. To change a wrapped object, the wrapped code can be unwrapped, modified In a text file, and then wrapped again. 

View Answer