Creating a new package within your schema
1. To ensure that you are only seeing your own items in the browser window, select “My objects” from the drop down menu at the top.
2. In the browser frame, right-click PACKAGES, and select New from the drop-down menu.
3. In the dialog box that appears, enter the name and purpose of your new package. Name the new package “mypack” and in the Purpose field, enter “This is my first package”. Then click OK.
4. The new package will open in the “Specification” or “spec” view. This is like a header that will contain the names of your procedures and all of the parameters that they will accept. Make the spec of your package look like the example by deleting the unnecessary items as shown below.
5. Change the view to the Body view by clicking the right tab indicated by the red arrow below. The body view is where most of your code will be. Make the body of your package look like the example by deleting the unnecessary items as shown below.
6. Now you can compile your empty package either by clicking the , or by pressing F8. After you compile, click the + next to the packages folder in the browser window. You should now see your package under that folder as shown below.
When you need to open it, you can right click on the package and select “edit spec and body”. You don’t need to do this now because it is already open for editing.
Creating a procedure within a package
1. In the spec of the “mypack” package, after the “author, created, and purpose” statements, type the following:
procedure names;
2. In the body of that package, after the “create…” statement, type the following:
/****************************************************************/
/* NAMES */
/****************************************************************/
procedure names is
begin
null;
end names;
Note: The part with all of the asterisks is a block comment that makes it easy to find the start of the procedure. Block comments start with “/*” and end with “*/”.
3. Now you can compile your package either by clicking the , or by pressing F8. You’ll notice now that the procedure name is listed in the frame to the left of the package body.
You now have an empty procedure in your package. Next you’ll make that procedure do something that you can view in your browser.
Make your procedure say something
Now that you have a place to put it, let’s make your procedure say something that we can view in a web browser.
Note: From now on, I will refer to packages and procedures in “package.procedure” format.
1. In the body of mypack.names, between the begin and the end, there is a line that says “null;”. Replace this line with the following one:
htp.prn('Hello! This is my procedure!');
2. The body of mypack.myproc should now look like this:
3. Compile your package either by clicking , or by pressing F8.
4. Open a web browser. The format to use for the URL is as follows:
http://www.wou.edu/login2db/schema.package.procedure
5. Type your URL into the browser and check out your webpage. Your schema in this case is your login name. After logging in, you should get a page that looks like this:
6. Since we don’t want to have to log in every time we visit our page, let’s make this package public. Click the icon and select SQL Window from the drop-down box.
7. After making sure you are under the SQL tab, type the following:
grant execute on mypack to public;
8. Compile this SQL statement either by clicking , or by pressing F8.
9. Now that your package is public, the URL is slightly different. Close the browser you were using and open a new one and enter the new URL. It will now follow this format:
http://www.wou.edu/public2db/schema.package.procedure
10. You can now close the SQL Window that you used to grant execute to public.
Send a value to a parameter in your procedure
Procedures pass values back and forth to perform any number of functions. Let’s give mypack.myproc a parameter so it can accept a value.
1. In the body of mypack.myproc, declare the two parameters as follows:
procedure names (
pfname varchar2 default null,
plname varchar2 default null
) is
2. The same will be done to the spec of mypack.myproc with a slight variation.
procedure names (
pfname varchar2 default null,
plname varchar2 default null
);
3. In the body of mypack.myproc, add the following to the “Hello…” line so it looks like this:
htp.prn('
Hello! This is my procedure!
<br<br>
My name is '||pfname||' '||plname||'.
');
Don’t worry about the colors changing.
4. Compile.
5. In a browser, type the following URL, but substitute your name in place of mine:
http://www.wou.edu/public2db/example.mypack.myproc?pfname=Will&plname=Drury
6. When you view the web page, you can see the result of the code you just wrote. It should look like this:
7. Now, go to the same page, but delete the parameters passed in the address. Your URL should look like this:
http://www.wou.edu/public2db/schema.package.procedure
What do all of these bits mean?
htp.prn(‘’) or htp.print(‘’) - Anything between the single quotes gets printed to the web page. The single quote signifies the start or the end of a string of characters.
|| - This is the concatenate symbol. You used this symbol to concatenate the value of a parameter into a string when you printed your name on the web page.
<br> - This is an HTML tag that creates a line break.
varchar2 – This is a type. The varchar2 type can generally hold any character.
Create a form
Now that you heave a procedure that accepts and displays values, let’s make a form to pass data into those parameters.
1. In mypack.names, create a complete HTML document by typing the following after the end of the previous htp.prn string:
htp.prn(‘
<html>
<head>
<title>My Form</title>
</head>
<body>
</body>
</html>
’);
Our form, and all that will be displayed in our web page will go between the <body> tags. Since we want all of our displayed items to be between the body tags, cut and paste the contents of the other htp.prn statement in there, then get rid of the other htp.prn code. Your procedure should now look like this:
/****************************************************************/
/* NAMES */
/****************************************************************/
procedure names (
pfname varchar2 default null,
plname varchar2 default null
) is
begin
htp.prn(‘
<html>
<head>
<title>My Form</title>
</head>
<body
Hello! This is my procedure!
<br<br>
My name is '||pfname||' '||plname||'.
</body
</html>
‘);
end names;
2. Within the <body> tags and after the “My name…” line, Type the following:
<br<br<hr<br>
<form method=post action=””>
First Name: <input type=text name=pfname value=””>
<br>
Last Name: <input type=text name=plname value=””>
<br>
<input type=submit value=”Submit”>
</form>
3. After compiling your code and refreshing the browser, your page should look like this:
This form still doesn’t know where to go because we left the “action” attribute blank. In the starting <form> tag, between the quotes after the word “action”, enter the schema.package.procedure to which you want to send the form data.
Let’s send the data for this form to names because we have already set it up to accept the very same parameter names as the input names we used in our form. Amazing coincidence, don’t you think? The format for the action attribute is as follow: action=”schema.package.procedure”
4. After compiling your code, test your form. You’ll have to refresh the web page so it will reflect your changes. Then type your name into the fields, click the submit button, and see them appear on the webpage in the “My name is…” line. Try it a couple more times with different names.
Creating a table
Since we are working with a database, I suppose that it’s time to create a table.
1. In the browser frame, right-click TABLES, and select New from the drop-down menu.
2. Under the General tab, enter the name “mynametable” in the Name field. Select “WEB” from the Tablespace drop-down menu. Check the box marked “unlimited” in the lower right corner. It should end up looking like this:
3. Under the columns tab, you will begin entering the column names, types, null ability, default values, and comments.
Make the following entries:
4. When you are finished creating the table, click “Apply” and “Close”.
Creating and using a Sequence
In PL/SQL Developer, you can create sequences. A sequence is basically a counter. These are very helpful when you need a unique identifier for records in your tables.
To create a sequence, right-click the sequences folder in the browser frame and select “New”.
The Create Sequence window will pop up with some fields that you will need to fill in.
The owner field will be filled with the current user. In the name field, enter the name that you want for your sequence. I usually preface my sequence names with “seq_”. This allows easy identification as a sequence later on. The min value field will normally be 1.
The max value field can contain whatever number you want the max to be, but normally I don’t have a maximum so I just enter a bunch of 9s. The Start with field will be 1, unless you want it to start with a higher number. Normally, the Increment field will be 1. For now, just leave cache size, cycle, and order blank.
If your screen looks like the example above, click Apply.
You can now close the Create Sequence window.
We’ll use this sequence in the next section.
Inserting data into a table
Now that we have a sequence to create a unique identifier for each name in our table, we can create an insert statement. The insert statement will take the data from the form inputs and put them into the table.
First of all, I like to have all of my updating procedures contained in their own package. So start by creating a new package and naming it “updates”. If you need help, go back to the section “Creating a procedure within a package”.
Once you have compiled the “updates” package, you can start creating the name updating procedure. If you need help, go back to the section “Creating a procedure within a package”. The name for this procedure will be “editname”.
The package body should look like this:
create or replace package body updates is
procedure editname is
begin
null;
end editname;
end updates;
Since this procedure is handling the editing of records, we need to declare a few parameters. It will need to be able to accept a user_id, first name, last name, and a parameter that will tell the procedure what action to take. The procedure declaration should look like this in the body:
procedure editname (
pname_id varchar2 default null,
pfname varchar2 default null,
plname varchar2 default null,
paction varchar2 default null
) is
You will also need to edit the package spec to reflect your new parameters. If you need help, go back to the section “Creating a procedure within a package”.
Now you will create the structure for handling different actions specified by the “paction” parameter. In place of the “null;” begin writing an IF statement. This IF statement will be set up to handle three actions plus one more incase the “paction” parameter is null.
if (paction is NULL) then
null;
else
if (paction = 'edit') then
null;
elsif (paction = 'add') then
null;
elsif (paction = 'delete') then
null;
end if;
end if;
The first IF here asks whether or not “paction” is null. This is here so you will get something back if you forget to include the “paction” parameter. In this case, we’ll make it send the user back to the form with a message. In place of the “null;”, type this:
rescomp.js.redirect('example.mypack.names');
This command calls a package(js) and procedure(redirect) that uses javascript to redirect the browser to a new package/procedure. This is not the definitive way of getting this done, and likely a better way exists but this will do for now.
After the ‘edit’ if we want to update the table with the new values from the parameters, but only where they match the name id parameter. So the code would look like this:
update mynametable
set fname = pfname, lname = plname
where nameid = pnaame_id;
Don’t forget the where clause, otherwise you will set all instances of the fname and lname field to the values form the parameters. Next we need the code dealing with the add statement. It would look something like the following:
insert into mynametable (nameid, fname, lname)
values (seq_nameid.nextval, pfname, plname);
Notice that in an insert the values from the first enclosed set must match the second, meaning they have to be in the same order. Also note the use of the sequence. The nextval will grab the next value from the sequence. Lastly the delete state should look like the following:
delete from mynametable
where fname = pfname
and lname = plname;
It’s important to note that a database programmer should rarely delete things. There will be rare cases where this might be necessary but in general it’s a better idea to set a Boolean flag to either on or off. This way you never lose precious data. Now to use these new statements we will need to alter the original mynames procedure and add a few radio buttons to reflect the paction parameter. But before we do this we need to add one more line to the update procedure. After the procedure finishes the user has nothing to see. Se we need to ad that javascript redirect line the very end of the procedure, outside of the if statement. So after the last end if; add the following line:
rescomp.js.redirect('example.mypack.names');
This way a user is returned to the original form, it should happen fast enough the user is none the wiser. The only indication that the form submitted will be the empty input boxes. So altering the original HTML code we should have something similar to this: