You need to implement a virtual private database (vpd). In order to have the vpd
functionality, a trigger is required to fire when every user initiates a session in the
database.

What type of trigger needs to be created?

A. DML trigger

B. System event trigger

C. INSTEAD OF trigger

D. Application trigger

Answer: B

QUESTION 59:

You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to
ensure that the new salary value falls within the minimum
and maximum salary for a given job title.

What happens when you try to update a salary value in the EMP table?

A. The trigger fires successfully.

B. The trigger fails because it needs to be a row level AFTER UPDATE trigger.

C. The trigger fails because a SELECT statement on the table being updated is not allowed.

D. The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.

Which overloaded COMBINE procedure declaration can be added to this package specification?

A. PROCEDURE combine;

B. PROCEDURE combine (p_no NUMBER);

C. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER;

D. PROCEDURE concat_all

(p_num_val VARCHAR2, p_char_val NUMBER);

Answer: A

QUESTION 69:

Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and
recompiled at 11 A.M.

The dependency mode is set to TIMESTAMP.

What happens when procedure A is invoked at 1 P.M?

A. There is no affect on procedure A and it runs successfully.

B. Procedure B is invalidated and recompiles when invoked.

C. Procedure A is invalidated and recompiles for the first time it is invoked.

D. Procedure A is invalidated and recompiles for the second time it is invoked.

Answer: D

When the local procedure is invoked, at run time the Oracle server compares the two time
stamps of the referenced remote procedure. If the time stamps are equal (indicating that
the remote procedure has not recompiled), the Oracle server executes the local procedure.
If the time stamps are not equal (indicating that the remote procedure has recompiled),

the Oracle server invalidates the local procedure and returns a runtime error.

If the local procedure, which is now tagged as invalid, is invoked a second time, the Oracle server recompiles it before executing, in accordance with the automatic local dependency mechanism.

So if a local procedure returns a run-time error the first time that it is invoked, indicating that the remote procedure’s time stamp has changed, you should develop a strategy to re-invoke the local procedure.

Incorrect Answers:

A, B, C

QUESTION 70:

Under which two circumstances do you design database triggers? (Choose two)

A. To duplicate the functionality of other triggers.

B. To replicate built-in constraints in the Oracle server such as primary key and foreign
key.

C. To guarantee that when a specific operation is performed, related actions are performed.

D. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.

Which two statements about object dependencies are accurate? (Choose two.)

A. When referencing a package procedure or function from a stand-alone procedure or function, if the package specification changes, the package body remains valid but the stand-alone procedure becomes invalid

B. When referencing a package procedure or function from a stand-alone procedure or

function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid.

C. When referencing a package procedure or function from a stand-alone procedure or

function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct becomes invalid

D. When referencing a package procedure or function from a stand-alone procedure or function, If the package specification changes, the stand-alone procedure referencing a package construct as well as the package body become invalid

Answer: B, D

QUESTION 107:

You need to create a trigger to ensure that information in the EMP table is only modified during business hours, Monday to Friday from 9:00am to 500pm Which types of trigger do you create? (Choose two.)

PL/SQL:FUNDAMENTAL
Lesson -1
1.What is PL/SQL?
2.Write down the four benefit of PL/SQL?
3.What is PL/SQL block structure?
4.Write the available block type in PL/SQL?
Chapter -2
1.What are the use of variable?
2.What are the conventions for variable names?
3.What is the difference of variable and an identifier?
4.What are the available types of PL/SQL variable?
5.What do you mean by scalar data type & composit data type?
6.Write the quidelines for declaring and intializing of PL/SQL variable?

Chapter -4
1.What do you know about SQL curor?
2.What is implicit cursor?

Chapter -5
1.Write an example of:
Simple if statement
If then else statement
If elsif else CLAUSE
2.Define loop statement?What are the types of loop available?
3.What are the quidline of using FOR LOOPS?

Chpter -6
1.What is pl/sql record ?
2.Why we use composit data types?
3.What are the advantage of using %row type?

Chpater 7
1.What is meant by implicit & explicit cursor?
2.What is function of explicit curosor?
3.What are the basic steps to process an explicit cursor?
4.List of briefly describe the explicit cursor attributes with particular emphasis on %ISOPEN.

Chapter -8
1.What is an exception?
2.What are the method for raising exception?
3.How an exception can handled?
4.Write down the name of exception types?
5.Write the quiqline for PL/SQL exception?

PL/SQL:FUNDAMENTAL
Chapter -1
1.What is meant by procedure?
2.What are parameters?
3.What are the formal & actual parameters?
4.What are the methods to spacify the values of the parameters?
5.Write the syntex for passing parameters?

Chapter -2
1.What is meant by function?
2.What are the ways to execute a function?
3.What are the advantages of user defined function in SQL statements?
4.What are restrictions apply when calling a function in a SQL statement?
5.What are the basic difference between a procedure and a functions?

Chapter -3
1.What is package?
2.What are the components of a PL/SQL package?
3.What are the quidline for writing package?
4.What are the advantage of using package?
5.What are the public component (object) & privet component(object)?

Chapter -4
1.What is meant by forward declaration?
2.What is meant by overloading of a package?
3.What is PL/SQL Wrapper?
4.What are the quidelines available for wrapping?
5.What are the overloading subprogrms?
6.What are the restrictions of overloading subprograms?

Chapter -5
1.What is called oracle supplied packages?
2.What are the uses of UTL_MAIL package?
3.How the DBMS_OUTPUT package works?
4.Write five oracle supplied package name?
5.What do you know about DBMS_SCHDULER package?