Identify two situations where the DBMS_SQL package should be used. (Choose two.)
A. The SELECT list is not known until run time.
B. The dynamic SQL statement retrieves rows into records.
C. You do not know how many columns a SELECT statement will return, or what their data types will be.
D. You must use the % FOUND SQL cursor attribute after issuing a dynamic SQL statement that is an INSERT or UPDATE statement.
View the Exhibit and examine the code.
Why does the code give an error on execution?
A. because the WORD_LIST variable is not visible in PROCEDURE WORDING
B. because the LEXICON variable is not visible in PROCEDURE ADD_ENTRY
C. because the LEXICON variable is not initialized in PROCEDURE WORDING
D. because the WORD_LIST parameter in IN OUT mode cannot be of a RECORD data type
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.
Which tasks must be performed during the installation of the UTL_MAIL package? (Choose two.)
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 the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.
EMPLOYEES
DEPARTMENTS
EMP_BY_DEPT
Examine the following code:
What is the outcome on execution of the above code?
A. It executes successfully but the output statements show different values.
B. It executes successfully and both output statements show the same values.
C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT.
D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL
Which two statements are true about triggers? (Choose two.)
A. All the triggers that are created on a table cannot be disabled simultaneously.
B. Any user who has the alter privilege on a table can create a trigger using that table.
C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database.
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 invocation.
Examine the following PL/SQL code: Which statement is true about the fetch statements in the PL/SQL code?
A. Each fetch retrieves the first row and assigns values to the target variables.
B. Each fetch retrieves the next consecutive row and assigns values to the target variables.
C. They produce an error because you must close and reopen the cursor before each fetch -statement.
D. Only the first fetch retrieves the first row and assigns values to the target variables- the second produces an error.
View Exhibit 1 and examine the structure of the EMP and DEPT tables.
View Exhibit 2 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.
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 the Exhibit and examine the partial data in the PRODUCTS table. PROD_ID is the primary key.
Examine the following code: What is the outcome on execution of the above code?
A. It executes successfully.
B. It gives an error because the DECODE function can be used only in a SQL statement.
C. It gives an error because the SUBSTR and INSTR functions can be used only in a SQL statement.
D. It gives an error because both the MAX and DECODE functions can be used only in a SQL statement.
