Select the When Clause window and provide the condition for the when clause as given below

NEW.job = „SALESMAN‟

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 5: Provide the trigger body as given below.

BEGIN

ORACLE – Exercises for Hands on

Step 6: Finally you can see the trigger created from Schema browser.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Assignment 4

Objective: To learn how to create a Store Procedure, SP template and how to call a SP from an anonymous block using TOAD.

ORACLE – Exercises for Hands on

Step 1:

Go to Database>>Schema Browser option.

The following window will appear.

Select the Treeview from the Browser Style.

ER/COPR/CRS/DB46SC/019 Education & Research Department

The following window will appear.

ORACLE – Exercises for Hands on

Step 2:

Expand My Schema tab

Step 3:

To see the existing Stored Procedures expand the tab of Procedure.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 4:

Right click on the Procedure Tab and go for View/Edit package save option

ORACLE – Exercises for Hands on

Step 5:

Go for the first procedure tab file path

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 6:

Click on edit file and the file opens With the following code

CREATE OR REPLACE PROCEDURE %YourObjectname% IS tmpVar NUMBER;

Automatically available Auto Replace Keywords:

Object Name: %YourObjectName%

Sysdate: %SYSDATE%

Date and Time: %DATE%, %TIME%, and %DATETIME%

ORACLE – Exercises for Hands on

Let the file remain open

Then click on add to add a new SP template

Step 8:

Fill in the following details

ER/COPR/CRS/DB46SC/019 Education & Research Department

Path will be the same as old Procedure template path and name of file will be the name of the new SP template.

Step 9:

Try editing the new file.

The following screen appears.

Click on “yes” to save the file, as it does not exist it will create a new file with this name and save it.

ORACLE – Exercises for Hands on

Step 10:

Copy the contents of the old Procedure template in this new file with the following changes highlighted in blue color

CREATE OR REPLACE PROCEDURE %YourObjectname%

(

ER/COPR/CRS/DB46SC/019 Education & Research Department

Automatically available Auto Replace Keywords:

Object Name: %YourObjectName%

Apply the changes and say ok.

Now this will be the new Procedure template in addition to the old procedure template.

Step 12:

Now Come back to the schema browser and right click on the procedure tab to create a new SP.

ORACLE – Exercises for Hands on

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 13:

Fill in the following details and choose the new proc template as directed below.

ORACLE – Exercises for Hands on

Step 14:

Save it.

After this the following screen would appear.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Complete the procedure body by adding the following stmt in it.

pRes:= pNum1 + pNum2;

Now execute the procedure by pressing F11 Step 15:

After the Procedure is created, create an anonymous block for calling it.

declare

Now execute this SP.

Set serveroutput on.

It will ask for 2 inputs at the run time Supply the following data

ORACLE – Exercises for Hands on

Step 17:

After execution the following output will appear in the output window.

ER/COPR/CRS/DB46SC/019 Education & Research Department

ORACLE – Exercises for Hands on

Assignment 5

Objective: To learn how to use Debug options of TOAD

Step 1:

In TOAD Select Database Schema Browser Select Procedures from Schema Browser

Step 2:

Select the Procedure to be debugged.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 3:

As shown in the figure below, load the procedure in the Procedure editor.

ORACLE – Exercises for Hands on

Once it is loaded, you can see a new window for the procedure editor.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 4:

Before using any debug options, we need to set the values for input parameters of the procedure selected. In Debug menu select Set Parameters options.

ORACLE – Exercises for Hands on

Step 5 :

Now you can see a new window opened and the parameters of the procedure are listed. One by one we need to set the values for all IN and INOUT parameters. The following pictures illustrate this method for two input parameters (pNum1 and pNum2).

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 6:

Once the parameters are set with the values, the procedure is ready for debug operation.

Here we will see how to set a break point. Go to Debug menu in Procedure editor, and select Set Breakpoint option after placing the cursor in the appropriate line. Debug  Set

Breakpoint.

ORACLE – Exercises for Hands on

Once the Breakpoint is set, it will be highlighted with a red line, as shown below.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 7:

We can add watches too. Go to Debug menu and select Watches option.

ORACLE – Exercises for Hands on

The Watch window will be opened as shown below. Here, select the add watch option available in the watch window. (See the below snap shot)

ER/COPR/CRS/DB46SC/019 Education & Research Department

Once you click on Add watch option, a window will be popped and it will prompt for

expression to be watched. Type an expression, or variable to be watched. The following snap shots illustrate, to add watches for variables pNum1 and pNum2.

ORACLE – Exercises for Hands on

ER/COPR/CRS/DB46SC/019 Education & Research Department

ORACLE – Exercises for Hands on

ER/COPR/CRS/DB46SC/019 Education & Research Department

ORACLE – Exercises for Hands on

Please find below, the snap shot to display the added watch variables in the watch window.

(It shows process not accessible because we have not yet started the debug/ trace operation)

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 8:

Now we can start tracing the execution of the procedure. Go to Debug menu and select Trace Into option. Debug Trace Into.

ORACLE – Exercises for Hands on

It will prompt with the following message “Do you want to compile referenced objects with Debug information? This process can take several seconds”. Click on Yes.

ER/COPR/CRS/DB46SC/019 Education & Research Department

The Tracing is started and it will show the tracing line with highlighted color.

ORACLE – Exercises for Hands on

Every time press shift+F7 or select the Trace Into option from Debug menu.

ER/COPR/CRS/DB46SC/019 Education & Research Department

While tracing the procedure execution, we can watch the variables added for watch option in the watch window.

ORACLE – Exercises for Hands on

ER/COPR/CRS/DB46SC/019 Education & Research Department

ORACLE – Exercises for Hands on

Assignment 6

Objective: To learn how to use the DBMS output window of SQL Editor.

The following demo is to show the use of DBMS Output window available in SQL Editor of TOAD.

Step 1:

Type the following code in SQL Editor, as given in the snap shot. Click on DBMS Output window, and click on the clear window button.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 2:

Click on the Turn Output On button as shown in the below snap shot.

ORACLE – Exercises for Hands on

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step 3:

Go to Debug menu and select the Run option.

ORACLE – Exercises for Hands on

Once you Run the script, the DBMS Output window will show the output.

ER/COPR/CRS/DB46SC/019 Education & Research Department

Assignment 7

Objective: To learn how to use the CodeXpert option of SQL Editor.

Step 1:

Type the following query in SQL Editor.

Select emp.empno, emp.deptno, dept.dname from emp, dept;

ORACLE – Exercises for Hands on

ER/COPR/CRS/DB46SC/019 Education & Research Department

Step2 :

Select CodeXpert window and click on button available in CodeXpert window.

ORACLE – Exercises for Hands on

Now we can see the suggestions given by CodeXpert in the bottom window.

ER/COPR/CRS/DB46SC/019 Education & Research Department

In document Oracle TOAD Lab Guide (Page 34-85)