iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 08

“The secret of happiness is to make others believe they are the cause of it.” Al Batt, in National Enquirer

 

Read first then play the video:

   PLS-VIDEO -Create PL/SQL to add department row using Procedure Builder

  

Create PL/SQL to add department row using procedure builder

 

Hands-On Introduction

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 should use the Procedure Builder.

 

Start Listener

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

MS-DOS> lsnrctl start

Normally when you start your NT, the SERVICE processor will start the listener. Once the listener was started, you will be able to access the database from any of the Oracle tools. A listener is an Oracle agent that monitors a specific port. It is a gateway of communication between clients and Oracle server.

 

Procedure Builder Tool

The procedure Builder tool is a software utility that helps developers to write, debug, save, and test their PL/SQL programs.

Open the "Procedure Builder" tool.

 

Object Navigator

The Object Navigator window is a place that a developer can browse and navigate all its created objects.

In the ‘Object Navigator’ window, highlight "database objects." Go to the Main menu at the top of the window, choose the "File" sub-menu and select the "connect” option.

In the ‘Connect’ window, login as "iself” password “schooling", and then click on the "CONNECT" button.

 

Open the Database Objects

In the ‘Object Navigator’ window, click on the ‘+’ sign next to "Database Objects" to expand it. Navigator all the database objects (schemas, tables, views, triggers, procedures, packages, functions, etc.)

 

Create a Procedure

In the ‘Object Navigator’ window, highlight "Program Units” and click on the green ‘+’ sign which is the ‘Create’ icon.

 

In the ‘New Program Unit’ window, type the procedure name as “add_department” and then click “OK.” Make sure the ‘Procedure’ radio button is selected. That is the default option.

 

In the ‘Program Unit’ window, 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.

 

Use the "p_" prefix to name the parameters. Use one parameter as an output parameter to check the status of the transaction.

 

Use comments in your programs. Use double dashes for a single line comment. And use “/*” ended with “*/” for a multiple lines comment.

 

Writing a PL/SQL procedure

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. Use the others in case you are missing other exceptions.

 

(Procedure Builder)

PROCEDURE add_dept

    (p_deptno IN dept.deptno%TYPE,

    p_dname IN dept.dname%TYPE,

    p_loc IN dept.loc%TYPE,

    p_status OUT VARCHAR2)

 

IS

    -- No variable

 

BEGIN

 

    /* This program add dept. record. */

    INSERT INTO dept

        VALUES (p_deptno, p_dname, p_loc);

 

    --- Save record.

    COMMIT;

 

    -- Added successfully if the get to this line.

    p_status := ‘OK’;

 

EXCEPTION

    -- Check for an Unique or Primary Key

    WHEN dup_val_on_index THEN

        p_status := ‘DUPLICATE RECORD’;

 

    -- Check for invalid input data

    WHEN invalid_number THEN

        p_status := ‘INVALID INPUT DATA’;

 

    -- Check for any other problems

    WHEN others THEN

        p_status := ‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;

 

END add_dept;

/

Compile a PL/SQL procedure

Compile the procedure. You should not have any error and you should get "Successfully compiled" message.

 

Errors in Procedure Builder

Try to create an intentional error and compile the procedure.

 

For example:

p_status_was_not_defined := ‘DUPLICATE RECORD’;

 

Read the error messages. Notice that the error message most of a time is very preside and tells the line number such as:

Error 201 at line 11, column2

Identifier ‘p_status_was_not_defined’ must be declared

 

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

 

Create a test PL/SQL program

In the ‘Object Navigator’ window, highlight "Program Units” and click on the ‘+’ sign ‘Create’ icon.

In the ‘New Program Unit’ window, type the procedure name as “test_add_dept” and then click “OK.” Make sure the ‘Procedure’ radio button is selected. That is the default option.

 

In the ‘Program Unit’ window, write a stored procedure to test the ‘add_department’ procedure.

 

Declare a status variable and make sure to call the “add_department” procedure.

Notice, the called procedure has three input parameters and one output parameter; and department number can not be more than two digit numbers.

 

Enter an invalid department number to see the exception error message. To display the status of your transaction value, use the "TEXT_IO" instead of the "DBMS_OUTPUT", when you run the procedure locally.

 

(Procedure Builder)

 

PROCEDURE test_add_dept

    -- This procedure will test add_dept procedure

 

    --define variable

    v_status VARCHAR2(40);

 

BEGIN

 

    -- Call add_dept with an invalid number.

    add_dept(100, ‘FINANCE’, ‘OHIO’, v_status);

 

    -- Print ‘OK’ value if there is no error.

    TEXT_IO.PUT_LINE(v_status);

 

EXCEPTION

    WHEN others THEN

        p_status := ‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;

 

END test_add_dept;

/

Compile and test the program

Compile the procedure.

 

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

PL/SQL> SELECT * FROM dept

ORDER BY deptno;

 

Run a PL/SQL procedure (Test program)

Run the calling procedure-“test_add_dept.”

PL/SQL> test_add_dept;

 

Error messages

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

Notice the error message says “CHECK THIS WE HAVE UNKNOWN PROBLEM.”

 

Correct the Error, compile and run the program

In the ‘Object Navigator’ window, double click on the icon next to the test_add_department to open the ‘Program Unit’ window; and change 100 to 40.

Compile and close the window. Then run the calling procedure-“test_add_dept” once more. Now, you should see the ‘OK’ message. That means the transaction was added successfully.

 

Query the department table again to check that the transaction was successfully added.

PL/SQL> SELECT * FROM dept

ORDER BY deptno;

Notice, that the Finance department was added.

 

Save the PL/SQL procedure in the Database server

To save the program in the database server, expand the iself schema in the ‘Object Navigator’ window. Expand the stored program units, too. Then drag the procedure into the “Stored Program Units.” Notice that the solid line means: you can drop the object.

 

Save the PL/SQL procedure in the Local Library

To save the program in the local library, go to the ‘Object Navigator’ window, highlight PL/SQL libraries and click on the create icon. Click “OK.” Choose the “File” option and select “Save as.” Save a library as my_library in the iself folder. Then click “OK” as “File System.” A library should be created.

 

Now, drag the procedure into its “Program Units.” Highlight the library name and save it again.

 

“Education is the best provision for old age.” Aristotle (384 BC - 322 BC)

 

Questions:

Q: What is the Procedure Builder Tool?

Q: What is the listener in the Oracle database?

Q: How do you start or stop your listener?

Q: What is the Object Navigator in the Procedure Builder tool?

Q: How to you open a database using the Procedure Builder tool?

Q: What is a user’s schema?

Q: What type of objects can you have under a schema?

Q: How do you create a procedure using the Procedure Builder Tool?

Q: What is a Program Unit?

Q: 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 should use the Procedure Builder.

Note that you should use the "p_" prefix to name the parameters. You use this parameter as an output parameter to check the status of your transaction. Use comments in your programs. Use double dashes for a single line comment. And use “/*” ended with “*/” for a multiple lines comment. In the “EXCEPITON” section, define the exception. Use the “duplicate value on index” exception, the “invalid number” exception, and the “OTHERS” exception. Use the others in case you are missing other exceptions.

Q: Write a stored procedure to test the ‘add_department’ procedure. Declare a status variable and make sure to call the “add_department” procedure. Enter an invalid department number to see the exception error message. To display the status of your transaction value, use the TEXT_IO instead of the DBMS_OUTPUT, when you run the procedure locally.

Q: What is the client side environment?

Q: What is the server side environment?

Q: How do you save the above PL/SQL procedure in your local library?