Q1. Examine the following DECLARE section of PL/SQL block: 

Which line in the above declaration would generate an error? 

A. Line 2 

B. Line 3 

C. Line 4 

D. Line 5 

E. Line 6 

View Answer

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

Q3. View Exhibit1 and examine the structure of the employees table. 

User SCOTT needs to generate a text report that contains the names of all employees and their salaries. 

Examine the following commands issued by the DBA: 

SQL_CREATE DICTORY my_dir AS '/temp/my_files* ; 

SQL_GRANT WRITE ON DIRECTORY my_dir TO SCOTT; 

View Exhibit2 and examine the procedure code. 

You issue the following command: You issue the following command: 

SQL_EXEC sal_5tatus ('MY_DIR', ‘EMPREPORT.TXT') 

What is the outcome? 

A. It executes successfully and creates the report. 

B. It gives an error because the text file should be opened in append mode. 

C. It gives an error because the "no data found" condition is not handled to come out of the loop. 

D. It gives an error because user SCOTT should be granted both read and write privileges to the directory alias. 

E. It executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file. 

View Answer

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

Q5. Which two statements are correct about PL/SQL package components? (Choose two) 

A. A package must have both specification and body. 

B. A package body can exist without the package specification. 

C. A package specification can exist without the package body. 

D. When a packaged public variable is called for the first time in a session, the entire package is loaded into memory. 

View Answer

Q6. Which statements are true about PL/SQL procedures? (Choose all that apply.) 

A. Users with definer's rights who are granted access to a procedure that updates a table must be granted access to the table itself. 

B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time. 

C. Depending on the number of calls, multiple copies of the procedure are loaded into memory for execution by multiple users to speed up performance. 

D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java. 

View Answer

Q7. 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 procedure. 

E. It executes successfully, but a warning about the unreachable code is not displayed when used for the PROC1 procedure. 

View Answer

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

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

You need to create a trigger to ensure that customers in category “A” and “B” have a credit 

limit of more than 8000. 

Examine the following trigger. 

Which statement is correct about the outcome of this trigger? 

A. It compiles successfully and fires whenever the specified condition is met. 

B. It compiles successfully but does not fire even when the condition is met 

C. It gives an error on compilation because the new qualifier is prefixed with a colon. 

D. It gives an error on compilation because the new qualifier can be used only in row-level triggers. 

View Answer

Q10. View the Exhibit and examine the structure of the SALGRADE table. 

Examine the following code: 

What is the outcome? 

A. It is created successfully. 

B. It gives an error because the return clause condition is invalid. 

C. It gives an error because the usage of the host variables is invalid. 

D. It gives an error because the data type of the return clause is invalid. 

View Answer