In this Hands-On, you write a PL/SQL stored procedure to add a record into the department table (dept).  You use three input parameters to pass the department's columns (Department number “deptno,” department name “dname,” and department location “loc”); and use one output parameter to check the status of the insert transaction.

You will:

    1- Write a procedure to add a record using Procedure Builder.

    2- Use input and output parameters.

    3- Run the stored procedure.

    4- Test the stored procedure.

Go to "MS-DOS" and start the listener.

Once you start the listener, you will be able to access the database from any of the Oracle tools.

===

Close the window.

===

Open the "Procedure Builder" tool.

Resize the window.

Highlight "database objects."

Choose "File" and select "connect.”

===

Login as "iself” password “schooling", and then click "CONNECT."

===

Click on the plus sign (+) next to "Database Objects" to expand it.

===

Highlight "Program Units."

Click on the "create" icon.

===

Type the procedure name—add_department.

Click “OK.”

===

Write a stored procedure to add a record into the department table.

Use three parameters as an input parameter to pass the department's columns

Department number, department name, and department location).

Always, use the "p_" prefix to name the parameters.

Use one parameter as an output parameter to check the status of the transaction.

=

Always, use comments in your programs.

Use double dashes for a single line comment.

And use “/*” ended with “*/” for a multiple lines comment.

=

In the PL/SQL body, insert the input parameters' value into the department table.

Save the transaction.

Assign "OK" to the “Status” parameter if transaction was successful.

=

In the “EXCEPITON” section, define the exception.

Use the “duplicate value on index” exception, the “invalid number” exception, and the “OTHERS” exception.

Always use the others in case you are missing other exceptions.

=

Compile the procedure.

=

Notice the procedure was "Successfully compiled".

=

Create an intentional error and compile the procedure.

Read the error messages.

Correct the error and compile it again.   "Successfully Compiled."

Close the window.

=

Adjust the window.

=

Create a new procedure to test the add_department procedure.

Name the procedure “test_add_dept.”

===

Declare a status variable.

call the “add_department” procedure.

Notice, the called procedure has three input parameters and one output parameter.

Department number can not be more than two digit numbers.

Enter an invalid department number to see the exception error message.

Display the status of your transaction value.

Use the "text_io" instead of the "dbms_output", when you run the procedure locally.

=

compile the procedure.

Then close the windows.

===

On the "PL/SQL" interpreter, query the department table.

=

Run the calling procedure—“test_add_dept.”

You should get an error since the department number is more than 99.

Notice the error message “Other problem.”

=

Double click on the icon next to the test_add_department.

Change 100 to 40.

Compile and close the window.

=

Run the calling procedure—“test_add_dept” once more.

“OK” means the transaction was added successfully.

=

Query the department table again.

Notice, that the Finance department was added.

=

To save the program in the database server.

=

Expand the iself schema.

Expand the stored program units.

Drag the procedure into the “Stored Program Units.”

Solid line means: you can drop the object.

=

To save the program to the local library.

Highlight PL/SQL libraries and click on the create icon.

Click “OK.”

Choose the “File” option and select “Save as.”

Save the department library in the iself folder.

Click “OK” as “File System.”

Library was created.

=

Drag the procedure into the “Program Units.”

Highlight the library name and save it again to save the changes.

=

Now, you should practice this over and over, until you become a master at it.

Good Luck!