Oracle 1Z0-144 Free Practice Questions

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

View Answer

Q2. Which two statements are correct about the usage of parameters in functions? (Choose two.) 

A. Functions can have only in mode parameters. 

B. Functions called in SQL statements cannot have out or in out mode parameters. 

C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms 

D. Functions having in, out, or in out parameters can be called In PL/SQL procedures and anonymous blocks. 

View Answer

Q3. Which statements correctly describe the features of functions and procedures? (Choose all that apply.) 

A. A procedure can contain a return statement without a value. 

B. A function can return multiple values using a single return clause, 

C. A procedure can be executed as part of a SQL expression or as a PL/SQL statement, 

D. A function can contain zero or more parameters that are transferred from the calling environment. 

View Answer

Q4. View Exhibit 1 and examine the structure of the EMP and dept tables. 

View Exhibit2 and examine the trigger code that is defined on the dept table to enforce the update and delete restrict referential actions on the primary key of the dept table. 

What is the outcome on compilation? 

A. It compiles and executes successfully. 

B. It gives an error on compilation because it is not a row-level trigger. 

C. It gives an error on compilation because the exception section Is used in the trigger. 

D. It compiles successfully but gives an error on execution because it is not a row-level trigger. 

View Answer

Q5. View the Exhibit and examine the structure of the customer table. 

Examine the following trigger code: 

What is the outcome when the above trigger is compiled? 

A. It compiles successfully. 

B. It gives an error because the when condition is not valid. 

C. It gives an error because when cannot be used for row-level triggers. 

D. It gives an error because the statements under updating are not valid. 

E. It gives an error because the new qualifier in the when clause requires a colon prefix. 

View Answer

Q6. Which statement is true about triggers on data definition language (DDL) statements? 

A. They can be used to track changes only to a table or index. 

B. They can be defined by all users in the database or only by a specific user. 

C. They are fired only when the owner of the object Issues the DDL statement. 

D. They can be used to track changes to a table, table space, view, or synonym. 

View Answer

Q7. View Exhibit1 and examine the structure of the EMP table. 

View Exhibit2 and examine the code created by the user SCOTT: 

SCOTT grants the necessary privileges to green to access the EMP table and execute the package. 

Examine the following sequence of activities: 

SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command. 

SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command. 

green starts a session while SCOTT’s session is running and issues THE SQL>EXEC 

CURS_PKG.NEXT command. 

SCOTT issues the SQI>>EXEC SCOTT.CURS_PKG.NEXT command. 

The EMP table contains sequential EMPNOS from 100 through 108. 

Which statement correctly describes the output? 

A. SCOTT’s session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT’s session shows an error. 

B. SCOTT’s session shows the EMPNO 100, GREEN'S session shows EMPNO 100, and SCOTT’s session shows the EMPNO 101. 

C. SCOTT’s session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT’s session shows the second EMPNO 101. 

D. SCOTT’s session shows the EMPNO 100, GREEN'S session shows EMPNO 101, and SCOTT’s session shows the second EMPNO 102. 

View Answer

Q8. 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 Answer

Q9. Consider the following scenario: 

Local procedure a calls remote procedure B 

Procedure A was compiled at 8 AM. 

Procedure A was modified and recompiled at 9 AM. 

Remote procedure B was later modified and recompiled at 11 AM. 

The dependency mode is set to timestamp. 

Which statement correctly describes what happens when procedure A is invoked at 1 PM? 

A. Procedure A is invalidated and recompiled immediately. 

B. There is no effect on procedure A and it runs successfully. 

C. Procedure B is invalidated and recompiled again when invoked. 

D. Procedure A is invalidated and recompiles when invoked the next time. 

View Answer

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

Q11. Which two statements are true about the handling of internally defined or user-defined PL7SQL exceptions? (Choose two.) 

A. Add exception handlers whenever errors occur. 

B. An exception handler should commit the transaction. 

C. Handle named exceptions whenever possible instead of using when others in exception handlers. 

D. Instead of adding exception handlers to your PL/SQL block, check for errors at every point where they may occur. 

View Answer

Q12. You want to maintain an audit of the date and time when each user of the database logs off. 

Examine the following code: 

Which two clauses should be used to fill in the blanks and complete the above code? (Choose two.) 

A. ON SCHEMA 

B. ON QRXABASE 

C. AFTER LOGOFF 

D. BEFORE LOGOFF 

View Answer

Q13. Examine the following code that you plan to execute: 

What correction should be performed in the above code? 

A. The PROC2 procedure code should be defined in the package body. 

B. The PROC3 procedure should be declared in the package specification. 

C. The PROC3 procedure header should be declared at the beginning of the package body. 

D. The variable x must be declared in the package body and removed from the specification, 

View Answer

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

Q15. Examine the following package specification: 

Which statement is true? 

A. g_comm has a value of 15 at 9: 06 AM only for Jones 

B. g_comm has a value of 10 at 9: 03 AM for both Jones and smith 

C. g_comm has a value of 15 at 9: 03 AM for both Jones and smith 

D. g_comm has a value of 20 at 9: 06 AM for both Jones and smith 

View Answer