NEW QUESTION 1
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)

• A. Bind
• B. Parse
• C. Fetch
• D. Execute

Answer: BCD

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

• A. call in line 6
• B. call in line 7
• C. call in line 8
• D. call in line 9

Answer: CD

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

View Exhibit2 and examine the code.

What is the outcome when the code is executed?

• A. Both blocks compile and execute successfully when calle
• B. Both blocks compile successfully but the CALC_SAL procedure gives an error on executio
• C. The CALC_SAL procedure gives an error on compilation because the amt variable should be declared in the RAISE_SALARY procedur
• D. The CALC_SAL procedure gives an error on compilation because the RAISE_SALARY procedure cannot call the stand-alone increase functio

Answer: A

NEW QUESTION 4
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 successfull
• B. It gives an error because the when condition is not vali
• C. It gives an error because when cannot be used for row-level trigger
• D. It gives an error because the statements under updating are not vali
• E. It gives an error because the new qualifier in the when clause requires a colon prefi

Answer: A

NEW QUESTION 5
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 6
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?

• A. The SQLERRM function
• B. The PRAGMA EXCEPTION_INIT function
• C. The RAISE_APPLICATION_ERROR procedure
• D. A user-defined exception used with a raise statement

Answer: B

Explanation:

Reference: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/exceptioninit_pragma.htm#LNPL S01315

NEW QUESTION 7
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 8
Examine the following snippet of code from the DECLARE section of PL/SQL
DECLARE
Cust_name VERCHAR2 (20) NOT NULL : = ‘Tom Jones’:
Same_name cust_name%TYPE:
Which statement is correct about the above snippets of code?

• A. The SAME_NAME variable inherits only the data type from the CUST_NAME variabl
• B. The SAME_NAME variable inherits only the data type and default value from the CUST_NAME variabl
• C. The SAME_NAME variable inherits the data type, constraint, and default value from the CUST_NAME variabl
• D. The SAME_NAME variable inherits only the data type and constraint from the CUST_NAME variable resulting in an error

Answer: D

NEW QUESTION 9
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 10
Examine the following partial code:

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

• A. It generates an error because all the blocks of code in a package body must be name
• B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specificatio
• C. It acts as a package initialization block that executes once, when the package is first invoked within the user sessio
• D. 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 valu

Answer: C

NEW QUESTION 11
In which of the following scenarios would you recommend using associative arrays?

• 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 on memory each time a subprogram is invoke

Answer: CD

NEW QUESTION 12
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 labele
• B. It gives an error because the same variable name cannot be used across all the nested block
• 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.

Answer: C

NEW QUESTION 13
You want to store values of different data types in a PL/SQL block and store one record at a time for processing the information.
Which type of composite data type would you choose to fulfill the requirement?

• A. VARRAYS
• B. Nested table
• C. PL/SQL records
• D. Associative arrays

Answer: C

NEW QUESTION 14
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 15
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 erro
• 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.

Answer: C

NEW QUESTION 16
/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 17
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 immediatel
• B. There is no effect on procedure A and it runs successfull
• C. Procedure B is invalidated and recompiled again when invoke
• D. Procedure A is invalidated and recompiles when invoked the next tim

Answer: D

NEW QUESTION 18
Examine the following code:

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

• A. It executes and displays nul
• B. It executes and the condition returns tru
• C. It executes and control goes to the else statemen
• D. It fails because no value is assigned to the v_myage variabl

Answer: C

NEW QUESTION 19
Examine the following code:

The above code generates an error on execution.
What must you do to ensure that the code executes successfully?

• A. Use the TO_DATE function in line 2.
• B. Use the TO_DATE function in line 7.
• C. Use the TO_NUMBER function in line 6.
• D. Use both the TO_DATE function in line 2 and the TO_NUMBER function in line 6.

Answer: A

NEW QUESTION 20
View the Exhibit and examine the code:

Which statement is true about the COMPILE_CODE procedure?

• A. It gives an error in line 6.
• B. It gives an error in line 8.
• C. It gives an error in line 5.
• D. It executes successfully, but displays a warning about the unreachable code when used for the PROC1 procedur
• E. It executes successfully, but a warning about the unreachable code is not displayed when used for the PROC1 procedur

Answer: D

NEW QUESTION 21
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 22
View the exhibit to examine the PL/SQL code.

Which statement is true about the exception handlers in the PL/SQL code?

• A. All the exceptions in the code are trapped by the exception handle
• B. All the "no data found" errors in the code are trapped by the exception handle
• C. The PL/SQL program does not execute because an exception is not declared in the declare sectio
• D. An exception handler in the code traps the "no data found" error after executing the handler code and the program flow returns to the next line of cod

Answer: B

NEW QUESTION 23
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 24
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?

• A. Because the procedure call in the trigger is not valid
• B. Because the condition specified in the when clause is not valid
• C. Because both the procedure and trigger access the same table
• D. Because the WHEN clause cannot be used with a row-level trigger
• E. Because the column list specified with UPDATE in the trigger is not valid

Answer: B

NEW QUESTION 25
......