"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?
|