Paper presented at the Rocky Mountain Oracle Users Group Training Days, Feb 99
the actual presentation has more info

Oracle is a nice relational database, probably the best one on the market. But it's expensive and once you've bought the basic package, you can spend megabucks to get add-ons such as Developer2000, DesignerFourBillion, JavaFinancials, etc. If you and a couple other coders are trying to get stuff done on a shoestring budget, or if you want to show proof of concept to the managers that control the purse strings, then take advantage of the free software out on the web.

Many managers think free software is worth what you pay. They're wrong. And the newsgroup/web page support found for these freebies is usually much better than the support provided at profit-oriented companies – the ones that hire people off the streets to answer phones and walk callers though the same on-line support that didn't work when you tried it before calling the so-called "help" desk. Many other managers refuse to purchase software from someone who can't be sued. In this litigious society, that may be a good move for a company. But you personally can still develop skunkwork projects and proof of concept systems using freebies and then tell management to spend $50,000 bucks for a "real" development tool. And perhaps Oracle charges what they do because they could get sued.

Linux is a free variant of Unix that can also be purchased with installation support from companies such as Red Hat and Caldera. Available at http://www.linux.org or http://linuxcentral.com

Apache Web Server is a free package available from http://www.apache.org that provides all the robustness and performance of costly software and is used and supported by thousands of professionals worldwide.

Perl programming language is free to install, extremely easy to learn, and many programmers throughout the web offer free Perl programs with source code. This is available on a variety of platforms from the Perl Institute at http://www.perl.org/

If you have a web connection, you can build your own server. Or if you just want to connect a new server to an internal network to show proof of concept without having to worry all those anal-retentive folks in networking and/or management, put one on. Linux on a spare 486 that no one uses will outperform NT on a Pentium (and you probably won't have to reboot for years). Install Perl and configure the Apache Web Server and you're set.

The key component though, is having a connection to an Oracle database someplace. It's not too much trouble whether it's on a different server (just basic networking using tnsnames.ora or something similar) and is even simpler if it's on the same server (don't try this on a 486). What you need to do is figure out what sort of command will get you into sqlplus from the command line. On one of our web servers, this command is

/home/oracle/app/oracle/product/732/bin/sqlplus -S
The "-S" is for silent mode so you don't see those sqlplus messages as it starts.

Sometimes, setting up the connection and getting the right pieces in the tnsnames.ora can be a real pain, but you probably already know that. Once it's working, everything else is a piece of cake.

The sql() connection function is the key part of a Perl file that allows everyone else to write Perl programs using these four basic lines:

• require oracle.pl (include file for the sql() function)

• $sql_login = "USER/PWD"; (set a Perl variable)

• @sql_code = <<ENDOFSQL; (set an array variable)

  select (or insert,update,delete) fields

  from table

  where ...; (don't forget Oracle's semicolon)

  ENDOFSQL

• &sql($sql_login,@sql_code); (call the function with the id/pwd and the SQL stmt)

sql() function

Scott Florcsk at USDA:APHIS wrote this routine as part of the oracle.pl Perl script.

1. It accepts the passed in parameters.
2. Defines the basic sqlplus login.
3. Wraps the SQL code with sqlplus commands before and after (the after is probably unnecessary).
4. Then comes the tricky part: creating a file to write the code to.
   He creates a random filename based on the time, then checks to see if a file with that name already exists. As soon as one doesn't, he defines the $sql_file and prints the code to it. Finally, he calls sqlplus with the login and .sql file to run, then deletes the file he wrote. Here is a truncated version (so it prints well).

local ($sql_login, @sql_code) = @_;
$sqlplus = "/home/.../732/bin/sqlplus -S";
$sql_code_begin = "set echo off verify off \n";
$sql_code_end = "\nset feedback on\nquit;\n";
@sql_array[0..2] = ($sql_code_begin, @sql_code, $sql_code_end);
@sql_code = @sql_array;
$random = time();
$rand = substr($random,length($random)-8,8);
while ( -e "/var/tmp/$rand.sql" ) { $rand = $rand - 600; }
$sql_file = "/var/tmp/".$rand.".sql";
open (SQLCODE, ">$sql_file");
print SQLCODE @sql_code;
close (SQLCODE);
system("$sqlplus $sql_login \@$sql_file");
unlink ("$sql_file");

Quick tutorial

For this example, (there are several of much greater complexity at my electronic cubicle on the web), we will create a minimal set of data fields for tracking user calls. There will be one main table: CALLS with text fields for name, e-mail address, problem; lookup fields for priority and software; and a couple date fields for when the problem is opened and closed. There will also be a sequence for numbering the entries and a lookup table.

I usually write a Perl program so one file handles all data entry forms from displaying the basic data entry screen to inserting the data to displaying update/delete screens and performing those actions. But that gets complicated and I don't combine the separate functions initially. In fact, step one is to write the data entry screen in straight HTML just to get the look and feel okay. Then I code it into Perl and get the dropdown lookup fields working using SQL, then write the initial insert.

HTML

The HyperText Markup Language forms the basis of web pages which are just flat ASCII files with tags telling the browser how to present text. Most tags enclose text or commands with a beginning <TAG> and an ending one of the same name but containing a slash </TAG>. A few tags have no matching end tag such as paragraph <P> and Horizontal Rule <HR>.

The rudiments of an HTML page are this

<HTML>

<HEAD>

<TITLE> Type in name of page (file) info.</TITLE>

</HEAD>

<BODY>

<H1> Title for users to see. </H1>

This is all the info like the stuff you're reading.

<P>

This would be the second paragraph.

</BODY>

</HTML>

There are all sorts of other codes you can put in to format things prettier (or at least differently). And there are all sorts of tutorials available for showing you how to use them (including one at my electronic cube). What we're concerned with here is creating a form.

Between the <FORM> and </FORM> codes, you can take input as radio buttons, checkboxes, scrolling menus, a line of text, or a box of text, set hidden inputs, and provide buttons to press to submit info or clear the form. Once the form is completed, including dropdown boxes with hardcoded values, you merely wrap some simple Perl code around it and you have a generated form.

Perl is called the Practical Extraction and Report Language for a good reason. It is easy and is text based. I don't know about you, but most of my programming has to do with getting text info out of databases and into a pretty format. C sucks at this. Perl has commands such as open, close, and print that can be passed filehandles and data.

CGI

The first thing you need to do to write out an HTML file from a web server is pass the correct info to the Common Gateway Interface (CGI) that is used to pass data back and forth between the browser and the server. What it expects is two lines, the second is blank; the first is: Content-type: text/html

To do this in Perl, merely write a print statement with two newlines (ala C):

print "Content-type: text/html\n\n";

Print to End_Label

Then you can print out the rest of the file by using a technique where you print everything until the label. The label can be called anything (I call it end_of_text in the following example). The direction to the label must be preceded by two less-than signs and followed by a semicolon. Then anything until the label will be printed (or returned or dumped to a variable depending on the command). NOTE: The ending label must be at the left margin, not followed by a semicolon, and on its own line.

print <<END_OF_TEXT;

whatever your little heart desires

for as many lines as you wish

END_OF_TEXT

The "at" sign, @, signals an array variable in Perl. So in order to print it out in e-mail addresses and links, it must be escaped by preceding it with a backslash.

The Perl Interpreter

Comments in Perl are preceded by an octothorpe (aka "pound sign") "#" except for one important exception. The first line of a Perl program must begin with a pound-bang "#!" followed by the location of the Perl interpreter. For me, this command is:

#!/usr/bin/perl

Use vi or Notepad or some ASCII editor on whatever machine you're going to use (or just download the source from my e-cube). Most Web Servers want their files located under the cgi-bin (or some other executable) directory. One tricky aspect on UNIX boxes is that you must make the file executable first by doing this command:

chmod 755 firstform.pl

About now is when I create a system_name.pl file include file that contains routines for printing the top and bottom of every web page for the system so they have the same titles and links and stuff. And when I want to change something, I only have to do it one place. An include file doesn't need the name of the interpreter at the top, but does need a special return line at the bottom and each subroutine is split out separately as a sub{}. I usually create a $sql_login variable, two print routines, and occasionally an error page.

To call these routines, first we include (require) the file and then we do a print function_name; Here is the top and bottom of a Perl script:

#!/usr/local/bin/perl
require "oratut.pl"; #for the print calls
require "oracle.pl"; #for the SQL call
print "Content-type: text/html\n\n";
print &HtmlTopTut("Second Form Attempt");
...print all the other form stuff...
print &HtmlBotTut;

Finally, we come to the first kewl part of using Perl to access Oracle to display a web page to the user. What I need are a couple select statements that will print out the HTML source code for the dropdown <SELECT> which looked like this in our basic form:

Software <select name="software">

<option value="NOTES">Lotus Notes

<option value="LOTUS">Lotus Suite

</select>

The beginning and ending lines are singular so I'll just write them in the Perl script, but the "option" line can be pulled out in SQL. There are several ways to do this in Oracle, but the most basic is merely to use text constants. I usually login to SQL and write the statement until I get it correct. Then I import it into the Perl script.

Assigning SQL Code

To assign a multiline value to a variable, merely use the end_of_label routine we've already used for printing. Here, I'll assign SQL code to an @get_sw variable which will create the dropdown list.

@get_sw =<<ENDOFSQL;

select '<option value="'||lk_code||'">', lk_desc

from oratut.lookup where lk_type='SW';

ENDOFSQL

Now that we have those code variables, we merely need to run them in the correct spot of the program so they appear where we expect them. That means we need to put in a line that says "dollar pipe equals one" otherwise Perl will spawn a child process to run the system call to sqlplus and go merrily along printing out the rest of the form without waiting for the info to return:

$| = 1; # prevents forking to print in correct order

Now we're set. We just need to put the form together and call our SQL files at the right time.

print <<EOM;

<form>User Name: <INPUT...other fields ...

Software <select name="software">

EOM

&sql($sql_login,@get_sw);

print <<EOM;

</select> rest of form

</form>

EOM

The real form breaks to call SQL twice, one for each dropdown list. Now that we're ready to accept data from users, we might as well load it into a database for safekeeping. Step number one is writing a Perl script to do that. Step number two is making sure our data entry form calls the correct script.

Within the <FORM> tag, you tell the form what script to call and what method to use. I always use Post. Read about CGI if you're interested in other methods. I'll call the script load_data.pl and put it in my basic directory. So now, we have to change the form generator code to modify the <FORM> line to look like this:

<form method="post" action = "/cgi-bin/mmoxcey/oratut/load_data.pl">

cgi_lib.pl

There is an excellent script written by Steven E. Brenner that provides a bunch of useful routines for managing basic data input and output. We use it on all the APHIS web servers. There are others available but this one is well-written, used throughout the web, and is free (as usual). It probably came with your version of Perl, but if not, you can download it from http://www.ether-world.com/cgi.shtml or just look for it on Yahoo or Excite or Hotbot.

ReadParse()

For me, ReadParse() is the heart of Brenner's cgi-lib.pl script. What it does is parse out the values passed from the form into a format that makes them easy to use. Data through CGI comes in as "name=value" pairs. The name comes from the NAME part of whatever form element you're using (text, text area, select, or radio button). The VALUE part is whatever you put in as default or the user selected. To accept data, the first action in a Perl script (after the requires) is:

&ReadParse (*input);

After calling ReadParse, you can then use the $input associative array to assign the inputs to local variables. Associative Arrays are a neat Perl concept: an array of pairs where the value depends on the literal name which is enclosed in single quotes. You can of course use any names, but I try to call my variables the same as I call them in the form. The basic syntax is:

$local_var = $input{'form_var_name'};

• $local_var is a regular variable with one value
• $input{} is the array received from &ReadParse
• form_var_name is the NAME off the <FORM>, and
• $input{'form_var_name'} is the actual value passed in for that particular NAME var.

So to assign all the variables I have in the form, I do a line for each:

$name = $input{'name'};
$email = $input{'email'}; ...

This assignment is also where I do a little bit of data checking or massaging. Here is one of the main drawbacks to this method of data entry: No Form Triggers. Scott Florcsk has written a few that are part of the oracle.pl file such as fix_string() which massages string input, converting double spaces to single space, single quotes to double-quotes, and ampersands to 'and'.

Use them when you assign input data to variables:

$problem = &fix_string($input{'problem'});

There are a few other data checking routines in oracle.pl. Read the code. As we keep playing around, we'll develop some more, and if you develop some, e-mail me.

Another routine is used when data has not been entered in a field. The check is minimal at best:

if (length($name) < 1) {&FieldRequiredError('Name')}

I wrote the FieldRequiredError('field_name') to merely return an error message. I put it in my oratut.pl script where it can take advantage of the other print routines.

Loading the Data is basic. All you do is write an insert statement and run it. Of course, I've created a problem for myself here because the form only supplies a close_flag of Yes or No whereas the table wants a real date. To get around that, I assign a value to a new variable, $cdate, that is dependent on the $close_flag and then use that variable in the sql_code:

if ($close_flag eq 'Y')

{$cdate='SYSDATE';}

else ### open call set date to null ###

{$cdate="''";} #pass a null string w/no quotes

With all the variables set, merely write an insert statement and execute it:

@sql_code = <<ENDOFSQL;

insert into oratut.calls

(seq, name, priority, open_date, email, problem, software, close_date)

values ( oratut.seq_nr.nextval, initcap('$name'),

'$priority', SYSDATE, '$email', '$problem',

'$software', $cdate);

ENDOFSQL

&sql($sql_login,@sql_code);

Then about all that's left to do is inform the user that the operation was successful. You can merely print out a document, perhaps with the data displayed, but I think a more robust and safer way to do it is to pull the just-inserted data back. This also let's you give them the seq number and check that nothing got hosed.

@sql_show = <<ENDOFSQL;

select '<h3>ID Nr:<em>', seq,

...various other formatted fields...

from oratut.calls

where seq=(select max(seq) from oratut.calls);

ENDOFSQL

print "Content-type: text/html\n\n";

print &HtmlTopTut("Call Data Received");

&sql($sql_login,@sql_show);

print &HtmlBotTut;

Debugging

Of course, once all your code is working, this arrangement of code will perform well. However, in order to debug web scripts, you've got to have a page started by using the print "Content-type: text/html\n\n"; or what I use more often is the routine contained in the cgi-lib.pl: print &PrintHeader;

If you don't use at least one of these, then you'll have a malformed header and nothing will appear on your web page. There are four main ways to debug scripts containing SQL.

1. Check the Perl script syntax: perl -c filename.pl
   This will tell you about a script that won't compile, but can't warn you about a bad web page output or SQL errors.

2. Run the Perl script from the command line: perl filename.pl
   This will show any egregious errors in the structure or logic, but may not demonstrate any CGI errors.

3. Check the web server log: grep your_perl_filename logfile
   This is useful if a script works on the command line, but just gives web page errors. (How do you think I found out about malformed headers?)

4. Display SQL code on the web page.
   I do this if I'm getting a page to appear, but data isn't being entered or retrieved correctly.

print "SQL:@sql_code!";

This will print out the variable. I end it with a bang so I'll know everything between the : and the ! is what Oracle is getting. WARNING: You won't be able to read the code on your browser. Because a browser interprets every tag correctly, then it won't show

"select <em>field</em>",

"select field".

Here is the proper order of performing the steps.

print &PrintHeader;

print &HtmlTopTut("Call Data Received");

##DEBUG print "SQLCode:@sql_code!";

&sql($sql_login,@sql_code);

##DEBUG print "SQLShow:@sql_show!";

&sql($sql_login,@sql_show);

print &HtmlBotTut;

I have the debug lines commented out. I leave them in and just Return the print command to the next line when I'm actually debugging.

Reporting Data

HTML <TABLE> Tag

A basic table would look like this:

<TABLE>

<TR>

<th>Col 1</th><th>Col 2</th><th>Col 3 </th>

</TR>

<TR>

<td>Cell A1</td><td>Cell A2</td><td>Cell A3</td>

</TR>

<TR>

<td>Cell B1</td><td>Cell B2</td><td>Cell B3</td>

</TR>

</TABLE>

Thus all you need to do is print out the initial tag and the header row, then select your data in the correct format. Begin and end the selection with <TR> and </TR> respectively and surround each field by a <TD> and </TD>

Here is the basic report select statement that will display info about every call in the database.

@sql_show = <<ENDOFSQL;

select '<tr><td>', seq, '</td><td>', priority,

'</td><td>', open_date, '</td><td>', to_char(open_date,'HH:MI PM'),

'</td><td>', name, '</td><td>', email,

'</td><td>',substr(problem,1,30),

'</td></tr>' from oratut.calls;

ENDOFSQL

The meat of the report script is this:

print <<EOM;

<table border><tr><th>ID Nr

</th><th>Pri</th><th>Date

</th><th>Time</th><th>Name

</th><th>Email</th><th>Problem</th></tr>

EOM

&sql($sql_login,@sql_show);

print "</table>";

print &HtmlBotTut;

Passing Variables to Perl

The next step is restricting the type of data returned – adding a where clause to get only open calls or only closed ones or only ones entered between a certain date range or something similar. To show only specific sorts of data on a report, you must be able to pass variables in. This is identical to passing in data. However, one of the easiest ways to make user-friendly reports is to take advantage of the hidden text option in a form.

One of the options for <INPUT> Type= is "hidden" which means the text doesn't display on a screen. NOTE: It will still display if the user Views Source. You can still use all the other parameters to Name the field and assign it a Value. Here are two sample lines for a report type of either open or closed.

<INPUT TYPE="hidden" NAME="call_type" VALUE="open">

<INPUT TYPE="hidden" NAME="call_type" VALUE="closed">

These are, of course, mutually exclusive options. You could also give them independent names, such as NAME="open_call" and NAME="close_call" each with a VALUE="yes" but I don't like that approach.

Creating Single Function Buttons

One way of selecting these options is using a Radio Button approach where the user clicks on one of the choices and then presses Submit. But I've found it much nicer to just give them the button to press. In order to do this, you place multiple forms on a single page with each form displaying only a button. Here is the complete code for two buttons to show either open or closed calls from the report2.pl script.

<form method="post"

action="/cgi-bin/mmoxcey/oratut/report2.pl">

<input type="hidden" name="call_type" value="open">

<input type=submit value="Show Open Calls">

</form>

<form method="post"

action="/cgi-bin/mmoxcey/oratut/report2.pl">

<input type="hidden" name="call_type" value="closed">

<input type=submit value="Show Closed Calls">

</form>

Now we have to modify our sql_show to select the correct type of data.
Step one is to assign the incoming parameter to a local $variable just as we do with data to be inserted.

Call

&ReadParse (*input);

then

$call_type = $input{'call_type'};

Then we need to create and assign a variable to insert into the select statement. Perl is a bit odd in that you do not have to declare a variable before you use it, so I don't bother. Here is the code to test the incoming data and assign the correct SQL code to a $get_call variable.

if ($call_type eq "open")

{$get_call="where close_date is null";}

else

{$get_call="where close_date is not null";}

And of course the final step is inserting this information into the actual sql_show code. That's as easy as just putting the variable in.

@sql_show = <<ENDOFSQL;

select ... from oratut.calls $get_call;

ENDOFSQL

That's all there is to it. However, once the page shows up, we won't know whether the calls displayed were open or closed calls! To fix that, merely create another variable to appear on the display. That's what $prn_type is for in the example code displayed a little bit later.

It is just as easy to accept user-entered data such as a date range. Of course, the logic requires some forethought, but you merely use <FORM> input, assign the values to Perl vars, and then figure out how to manipulate the SQL code correctly (which is by far the most difficult part of the process). Sometimes, it is easier to just create a second Perl script or create new select parameters and hide them in the calling form. That's a judgment call.

Fancy Ordering

One other thing users like to do is sort the report by a certain field. That is simple enough. The only problem is the output isn't intuitive then. I like the field that I sort by to be the first column in the report so I abstract the header and select fields out to make the code easier to manipulate and read. The $h_vars are the column headers and the $f_vars are the fields selected out of the database.

$h_name="<th>Caller Name</th>";
$h_pri="<th>Pri</th>";
$f_name= "'<td align=center>', name,'</td>'";
$f_pri= "'<td align=center>', priority,'</td>'";

Then just write if/else statements and assign the proper fields to variables. In the section below, the select list items are separated by commas for SQL, the head list ones by spaces for HTML. I could have just written the actual codes, but this way is a bit clearer for maintenance and for ensuring the column heads match the column fields. One thing I do backwards is not check for the default. In case, by some remote, infinitesimally tiny chance that I messed up the code in the calling form, then I want it to default to the number one choice.

if ($sort_opt eq "2") {

$sort_opt = "email";

$sort_name = "E-mail";

$var_list="'<tr>',$f_email, $f_name, ..., '</tr>'";

$table_head="<table><tr>$h_email $h_name..</tr>";

}

elsif ($sort_opt eq "3") {

$sort_opt = "open_date desc";

$sort_name = "Entry Date Descending";

$var_list="'<tr>',$f_date,$f_pri, ...,'</tr>'";

$table_head= "<table><tr>$h_date $h_pri ... </tr>";

}

else {

$sort_opt = "priority";

$sort_name = "Priority";

$var_list="'<tr>',$f_pri,$f_seq,..., '</tr>'";

$table_head= "<table><tr> $h_pri $h_seq ... </tr>";

}

The $sort_opt and $var_list are used in the SQL code like this:

@sql_code =<<ENDOFSQL;

select $var_list

from oratut.calls

order by $sort_opt;

ENDOFSQL

print &PrintHeader;

print &HTop("$prn_type Calls Report");

print "Sorted by $sort_name\n $table_head";

It gets really convoluted when you start combining options, such as whether the calls are open or closed and if they're in-between a date range or if you want every call. I have some examples of arrangements on my web page, but the best thing to do is figure out what all the options might be, then see if you can code it into an if-else arrangement with appropriate $var_list, $get_call (or use a broader name), and $sort_opt assignments. If so, then create the calling form with the appropriate input variables. For extending the sql_code, I've also created $table_list and used "from $table_list." But if the logic is convoluted, bizarre, or impossible, then see about creating two or more separate Perl scripts.

Even More Logic: Using PL/SQL

If you still can't get the report format your users want, you can use PL/SQL in place of the sql_code. I use it often for summary reports to print out subtotals and grand totals. Here's the basic outline. The really important stuff that isn't necessarily obvious is in bold. Don't forget the final slash.

@sql_code =<<ENDOFSQL;

set verify off;

set serveroutput on;

begin

declare

variables and cursors such as

row_cnt NUMBER(4);

cursor c_call_info IS

select ...

begin

dbms_output.enable(1000000);

for call_rec in c_call_info LOOP

row_cnt := c_call_info%ROWCOUNT;

process (IF THEN) and print when needed

dbms_output.put_line('<tr><td

align=center>'yadda,yadda,yadda');

END LOOP;

IF row_cnt > 0 THEN

dbms_output.put_line('<tr><td

align=center>'summary stuff');

ELSE

dbms_output.put_line(' *** No data for

$input_vals ***');

END IF;

end;

end;

/

ENDOFSQL

Updating Information

Now that users can enter data and get reports, we've got to let them update the data. This is actually very simple. Use the report capabilities (SQL code with embedded HTML tags) to display the initial data entry form with the fields set to what is already in the database. Then the form has to call an update.pl. The key thing we have to do is pass in the ID of the call (which is seq in this example). Rather than set it in a hidden variable, I like to pass it in on the command line so I can use it in an [Update] button when displaying all the open calls. To pass call ID #10 to the show_4_update.pl script, the call would be:

action="/cgi-bin/mmoxcey/oratut/show_4_update.pl?10

Accepting command line parameters is about like C.

if (!(defined($ARGV[0])))

{ &NoARGV; # exits with error msg }

else {$ID = $ARGV[0]; }

When you display the form for the user to update, the $ID must be kept on the form as a hidden text field with the correct name and value. To display existing data between the form text tags, you merely pull it out in SQL surrounded appropriately. But for fields that are radio buttons or select fields, we need to do something special to set them correctly.

Decode for Defaults

To set one of the fields in a radio button or menu list, you add the word SELECTED to the HTML tag. In order to do this in SQL with regular values such as those I'll pull from the lookup table, I perform a union.

select '<option value = "'||rtrim(lk_code)||'">'||lk_desc

from oratut.lookup

where lk_type = 'SW' and rtrim(lk_desc) !=

(select rtrim(software) from oratut.calls where seq=$seq)

union

select '<option value="'||rtrim(software)||'" SELECTED>'||lk_desc

from oratut.calls, oratut.lookup

where seq=$seq and lk_type='SW'

and lk_code=rtrim(software)

order by 1;

This pulls out all the lookup values we want and puts SELECTED next to the one the user has already chosen.

The Yes/No flag for if the call is closed needs to set the default radio button based on the existence of close_date. Simply use SQL's DECODE function on the date.

select ' Closed?', '<INPUT TYPE="radio" NAME="close_flag" Value="N"',

decode (close_date,'','Checked',''), '>No ',

'<INPUT TYPE="radio" NAME="close_flag" Value="Y"',

decode (close_date,'','','Checked'),'>Yes'

from oratut.calls where seq=$seq;

Updating Data

The update is exactly straightforward. You can simply make a copy of load_data.pl and call it update_data.pl and just change the sql_code to something like

@sql_code = <<ENDOFSQL;

update oratut.calls

set name=initcap('$user_name'),

email='$email', yadda, yadda, yadda

where seq=$seq;

ENDOFSQL

but that's a maintenance nightmare because then you have to make changes to two programs when the user requirements change, and worst of all to me, it is inelegant. I like to combine the two functions because they are almost identical. An easy check is to make use of the fact that a variable that doesn't get a value is undefined in Perl. Because every update will by necessity have a seq number, I get it after the &ReadParse.

$seq = $input{'seq'};

If it isn't passed in, then $seq has no value so I perform the test

if ($seq_nr) # if existing call

{set update display vars and sql_code}

else {set insert display vars and sql_code}

Then because you're using the same variable names for either updating or inserting, you merely

1. run the sql_code

2. print a success form using the variables where needed.

3. For the verification part, you run the existence check again and then either select for the passed in ID or select the new info where it's the max seq (which of course doesn't work correctly in a heavily accessed, multi-user database).

Go For It

Addtional Resources

This complete presentation with in-depth examples and all the source code is available on the web at my electronic cubicle: http://www.aphis.usda.gov/~mmoxcey/ora_tut.

Of course, things could change between the time I'm writing this paper (Nov 98) and when it is presented at RMOUG (Feb 99), so if I find a real job or my bosses discover exactly what it is I'm really doing at work, then there will still be a link from my personal home page at http://info2000.net/~moxcey.

My email addresses will be available on one of those pages. Happy coding ;-)

secondform.pl

@get_pri =<<ENDOFSQL;

select '<option value="'||lk_code||'">', lk_desc

from oratut.lookup where lk_type='PRI';

ENDOFSQL

@get_sw =<<ENDOFSQL;

select '<option value="'||lk_code||'">', lk_desc

from oratut.lookup where lk_type='SW';

ENDOFSQL

print <<EOM;

<form method="post" action="load_data.pl">

<kbd>

User Name: <INPUT TYPE="text" NAME="name" SIZE=30 maxlength=30><p>

Email Adr: <INPUT TYPE="text" NAME="email" size=30 maxlength=100 value = "name\@domain"> <p>

Problem:<br> <TEXTAREA COLS=60 ROWS=4 NAME="problem">default data entry</TEXTAREA><p>

Priority <select name="priority">

EOM

print '</select> Software <select name="software">';
&sql($sql_login,@get_sw);

print <<EOM;

</select><P> Closed?

<INPUT TYPE="radio" NAME="close_flag" Value="N" Checked >No

<INPUT TYPE="radio" NAME="close_flag" Value="Y">Yes

</kbd>

<input type=submit value="Insert">

<input type=reset value="Clear Form">

</form>

EOM

load_data.pl annotated

$name = $input{'name'};
$email = $input{'email'};
$problem = &fix_string($input{'problem'});
$priority = $input{'priority'};
$software = $input{'software'};
$close_flag = $input{'close_flag'}; # Y or N

if (length($name) < 1) {&FieldRequiredError('Name')}

if ($close_flag eq 'Y')

{$cdate='SYSDATE';}

else ### open call - set date to null

{$cdate="''";} #pass a null string w/no quotes

@sql_code = <<ENDOFSQL;

insert into oratut.calls

(seq, name, priority, open_date, email,

problem, software, close_date)

values (

oratut.seq_nr.nextval, initcap('$name'),

'$priority', SYSDATE, '$email',

'$problem', '$software', $cdate);

ENDOFSQL

@sql_show = <<ENDOFSQL;

select

'<h3>ID Nr:<em>', seq,

'</em></h3> Priority:<em>', priority,

'</em> Date:<em>', open_date,

'</em> Time:<em>', to_char(open_date,'HH:MI PM'),

'</em><blockquote><p>User:<em>', name,

'</em><br>Email:<em>', email,

'</em></blockquote><p>', problem

from oratut.calls

where seq=(select max(seq) from oratut.calls);

ENDOFSQL

print &PrintHeader;
print &HtmlTopTut("Call Data Received");

##DEBUG print "SQLCode:@sql_code!";
&sql($sql_login,@sql_code);

##DEBUG print "SQLShow:@sql_show!";
&sql($sql_login,@sql_show);

print &HtmlBotTut;