NEW QUESTION 1
/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 pubiic:
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 loo
• B. It executes successfully and displays only the list of faculty name
• C. It does not execute and displays an error message because the end-of-file condition is not taken care o
• D. It executes successfully and displays the list of faculty names followed by a "no data found” error messag

Answer: B

NEW QUESTION 2
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 sessio
• B. It is a public variable in a package specification whose value is consistent within a user sessio
• C. It is a private variable in a package body whose value is consistent across all current active session
• D. It is a public variable in a package specification whose value is always consistent across all current active session

Answer: B

NEW QUESTION 3
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 4
View the Exhibit and examine the structure of the customer table.

You create the following trigger to ensure that customers belonging to category "A" or "B" in the customer table can have a credit limit of more than 8000.

What is the outcome?

• A. The trigger is fired, a message is displayed, and the update is successful
• B. The trigger is fired and a message is displayed, but the update is rolled bac
• C. The trigger is not fired because the when clause should be used to specify the condition, however, the update is successfu
• D. The trigger is not fired because column names must be specified with the update event to identify which columns must be changed to cause the trigger to fire, however, the update is successfu

Answer: D

NEW QUESTION 5
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 6
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 7
Which three statements are true about anonymous blocks and subprograms? (Choose three.)

• A. Only subprograms can be parameterize
• B. Only subprograms are persistent database object
• C. Both anonymous blocks and subprograms can be parameterize
• D. Both anonymous blocks and subprograms are persistent database objects
• E. Only subprograms can return values that persist after the execution of the subprogra
• F. Both anonymous blocks and subprograms can return values that persist In SQL*Plus variables after their executio

Answer: BEF

NEW QUESTION 8
View Exhibit1 and examine the structure of the employees table.

View Exhibit2 and examine the code.

What would be the outcome when the code is executed?

• A. It executes successfull
• B. It gives an error because the SAL variable is not visible in the increase functio
• C. It gives an error because the increase function cannot be called from the RAISE_SALARY procedur
• D. It gives an error because the increase function and the RAISE_SALARY procedure should be declared at the beginning of the declare section before all the other declaration

Answer: A

NEW QUESTION 9
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 10
Which two statements are true about anonymous blocks and named subprograms?
(Choose two)

• A. Subprograms are by default executed with definer's right
• B. The declare section is optional for both anonymous blocks and subprogram
• C. Both anonymous blocks and subprograms execute by default with invoker's right
• D. The declare section is mandatory for anonymous blocks and optional for subprogram

Answer: AB

NEW QUESTION 11
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 vali
• B. The SQL does not support the Boolean data typ
• 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

Answer: A

NEW QUESTION 12
View the exhibit and examine the structure of the products table.

Examine the following code

Which statement is true when the procedure DELETE_DETAILS is invoked?

• A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table
• B. It executes successfully and any error messages get recorded in the DEBUG_OUTPUT tabl
• C. It gives an error because PRAGMA AUTONOMOUS_TRANSACTION can be used only in packaged procedure
• D. It gives an error because procedures containing PRAGMA AUTONOMOUS_TRANSACTION cannot be called from the exception sectio

Answer: A

Explanation:
In this case, the debug output will only occur if there is an exception.

NEW QUESTION 13
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 14
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 15
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 successfull
• B. It gives an error on compilation because it is not a row-level trigge
• C. It gives an error on compilation because the exception section Is used in the trigge
• D. It compiles successfully but gives an error on execution because it is not a row-level trigge

Answer: B

NEW QUESTION 16
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

Answer: A

Explanation:

Package variable state is scoped at the session level. So the only user who can see G_COMM=15 will be Jones

NEW QUESTION 17
Which two statements are true about the continue statement? (Choose two.)

• A. The PL/SQL block execution terminates immediatel
• B. The CONTINUE statement cannot appear outside a loo
• C. The loop completes immediately and control passes to the statement after end loo
• D. The statements after the continue statement in the iteration are executed before terminating the LOO
• E. The current iteration of the loop completes immediately and control passes to the next iteration of the loop

Answer: BE

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

• A. All the triggers that are created on a table cannot be disabled simultaneousl
• B. Any user who has the alter privilege on a table can create a trigger using that tabl
• C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed databas
• D. 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 invocatio

Answer: CD

NEW QUESTION 19
View the Exhibit and examine the structure of the SALGRADE table.

Examine the following code:

What is the outcome?

• A. It is created successfull
• B. It gives an error because the return clause condition is invali
• C. It gives an error because the usage of the host variables is invali
• D. It gives an error because the data type of the return clause is invali

Answer: B

NEW QUESTION 20
Which two statements are true about the usage of the cursor for loops? (Choose two.)

• A. The cursor needs to be closed after the iteration is complet
• B. The implicit open, fetch, exit, and close of the cursor happe
• C. The record type must be explicitly declared to control the loo
• D. The PL/SQL creates a record variable with the fields corresponding to the columns of the cursor result se

Answer: BD

NEW QUESTION 21
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 22
......