Running SQL Plus and Awk Scripts via the Web

The purpose of this document is to demonstrate a model for running SQL Plus and Awk scripts via the Web for an Oracle environment in which the Oracle database is running on a Linux box and the web server is an Oracle Application Server. More specifically, the authentication and parameter definition aspects of the model assume that your Oracle database is running Sungard Higher Education's (SHE) Banner Unified Digital Campus, alhtough the techniques described herein could also be modified to systems not running Banner as well.

I. Introduction

Intended Audience:

System administratores, database administrators, programmer analysts, and other IT personnel responsible for the provision and maintenance of reporting platforms for their institution.

Foreword

At our institution, we developed over the years a collection of various scripts that perform various functions related to text manipulation, file transfer, database management, and Banner reporting. These various scripts have been tied to a menu system built on top of a multi-purpose utility called Userbase developed in the early 80s by Ross Systems. All this runs on our AlphaServer running OpenVMS.

Given that

1. There will come a point when the gap between the version of the Oracle Server and that of the Oracle client will be so great that the client will no longer be able to connect to the database
   
2. As time goes on replacement parts get harder and harder to find for the AlphaServer
3. We don't have a whole lot of technical exptertise with regards to the AlphaServer, VMS, or Userbase, and chances are they're not teaching too much of it in colleges these days

we have been looking for a new way to migrate many of the business processes that run on our old system onto a new one. This has been the motivation behind this project/prototype.

Many within our Banner community, like Stephen Rea and Chad Boersema have already worked on and even implemented ways in which SQL scripts can be run from Banner job submission. This has been proposed in the past at our institution, but we never got around to converting any of our existing SQL scripts to be run through job submission. Our previous director might have pushed for it at one point, but my guess is that there was never a serious attempt to make this transition, partly because many of us were so used to running SQL scripts from menus. What I've done here is build upon the SQL script/Job Submission model, added an HTML interface, and created a template for running not only SQL scripts, but SQL Loader, Awk, and Bash shell scripts as well -- all by clicking on links in a web menu. Of great concern in this endeavor was being able to create a system that, once developed (and debugged, of course!), could be used by certain tech-savvy managers with minimal intervention of IT. This is important for us in IT and espcially for those managers.

My approach throughout this presentation is to provide the reader a level of understanding in each chapter that will be built upon in successive chapters until the reader is able to finally read the last chapter "Tying it all Together" and implement the complete system in his/her own Banner environment. I try to explain only what I think is necessary, hopefully erring on the side of excess. Those who are advanced in deploying Web pages using the PL/SQL Web Toolkit or in creating and calling Java stored procedures should be able to skip chapters II or III without precluding themselves from being able to understand the later chapters and implement the system.

The heart of this technology is the passing of web parameters to scripts that reside on the database's server. Much of what the reader will find in this presentation can be found elsewhere, especially the content of chapters II and III. I encourage you to find other tutorials out there that may be more helpful for him or her in terms of grasping or mastering these subjects. I also encourage you to practice on your own computer, to compile code, to make mistakes, to experiment -- these may protract the process, but they will sharpen the skills you will need to fix any bugs that will arise from a wooden implementation of this system or any other system.

The PL/SQL Web Toolkit

This section assumes that you have some experience with developing web pages using Oracle's PL/SQL Web Toolkit. If you do not, I strongly encourage you to find any user-friendly tutorials you can on the Web. I'll try to take a stab at explaining the basics here, but you would do well to find more complete references.

The PL/SQL Web Toolkit is an Oracle plugin (modplsql) for an Apache HTTP Server which allows a user to execute PL/SQL procedures on an Oracle database via HTTP requests. For example, let's suppose the following procedure existed in the WTAILOR schema of my database:

CREATE PROCEDURE HELLO_WORLD IS
BEGIN
  htp.print( '<html>' );
  htp.print( '<body>' );
  htp.print( 'Hello World!' );
  htp.print( '</body>' );
  htp.print( '</html>' );
END;
/

I could execute this procedure, which would in turn publish the greeting "Hello World!" on my browser, by entering the following URL in the address bar of my browser:

https://[ServerName]:[port]/[DADname]/wtailor.hello_world

where ServerName is the name of my Oracle Application Server, port is the port number on which it is listening for modplsql requests, and DADname is the name of the Data Access Descriptor I set up for executing HELLO_WORLD and other databas procedures, too. The connection need not be secure (https), but it is at our institution.

See Understanding mod_plsql in the OracleŽ HTTP Server mod_plsql User's Guide. To create a DAD via a GUI, you can also log into your ORACLE Enterprise Manager Application Server Control, click on HTTP Server, click on Administration, click on PL/SQL Properties, scroll down to the "DADs" section and click the Create button. If you wish to create a DAD by hand, see Creating a DAD in OracleŽ HTTP Server Administrator's Guide.

From my experience, the main challenge in getting the PL/SQL Web Toolkit to work is to initially set up the Data Access Descriptor (which, by the way, should already be done if you are using Banner Self Service) and remembering to grant EXECUTE privilege to the user defined in this DAD for any procedures you want run from the Web. You need to do the former only once; the latter must be done for each new procedure that you create. Finally, you have to make sure that the "Before Procedure" setting is set to empty, and if not, that you let whatever security mechanism you are invoking via that setting know what procedures it should allow to be executed.

Receiving Web Parameters from Web Forms

The previous example showed how to display a simple web page. But how would you do something more useful like putting up a web form to request information from a user, and how do you make sure the database receives that information once it is submitted from the form?

Putting up a web form is fairly straightforward. It's a simple matter of creating a form in a web page that contains two things:

• A form element whose action attribute specifies the corresponding database procedure that will receive and process the form's parameters
• input elements for each form parameter that you wish to pass on to the database procedure

In this example and in following examples, I will assume that the name of my Data Access Descriptor (DAD) is "/prod". The following HTML will publish a web form that requests a user's name and birthday and send these parameters to a database procedure named "HOW_LONG_MUST_I_WAIT" in the wtailor schema:

<html>
<body>
<form action="/prod/wtailor.HOW_LONG_MUST_I_WAIT">
<p>Please tell me your name: <input type="text" name="p_name"><p>
<p>
And your birthdate:
Month <input type="text" maxlength="2" name="p_month">
Day <input type="text" maxlength="2" name="p_day">
<p>
<input type="submit" value="Tell Me">
</body>
</html>

The procedure HOW_LONG_MUST_I_WAIT must be defined to accept three parameters whose names are "p_name", "p_month", and "p_day". These must be defined as IN parameters of some PL/SQL character type (I typically use VARCHAR2). Here is what such a procedure could look like:

CREATE OR REPLACE PROCEDURE HOW_LONG_MUST_I_WAIT(
  p_name IN VARCHAR2, p_month VARCHAR2, p_day IN VARCHAR2
) IS
  v_wait PLS_INTEGER;
BEGIN

SELECT TO_DATE( p_month ||'-'|| p_day, 'MM-DD' ) - SYSDATE 
  INTO v_wait
  FROM DUAL;

IF v_wait < 0 THEN
  v_wait := v_wait + 365;
END IF;

htp.print( '<html>' );
htp.print( '<body>' );
htp.print( p_name ||', you must wait '|| v_wait ||' days until your birthday' );
htp.print( '<body>' );
htp.print( '<body>' );

END;

If you're trying this example yourself and you get a 404 Not Found error after trying to submit your form, make sure that you remebered to grant EXECUTE privilege to the database user defined on your DAD in the PlsqlDatabaseUsername directive for the HOW_LONG_MUST_I_WAIT procedure (or your equivalent). If you're a Banner user and you're using the DAD defined for Banner Self-Service this user should be web_user. What I want to point here is the beauty of server-side scripting: it allows you to create dynamic content. In this example, we were able to calculate the days an individual has to wait for his/her birthday, then spit that number out onto his/her browser, all in the same PL/SQL procedure. That's powerful stuff, and we'll be using it later on to do things like grab SQL Plus parameter descriptions from database tables and checking whether or not a user should be allowed to run a given menu item based on his/her Banner roles.

Running Host Commands via Java Stored Procedures

If it is possible to run host (operating system) commands from a PL/SQL procedure, then it must be possible to run SQL*Plus, SQL Loader, Awk, and bash scripts from PL/SQL procedures, too. Furthermore, since we can execute PL/SQL procedures over the web, it follows that we should also be able to run SQL*Plus, SQL Loader, Awk, and bash scripts over the web as well. And this is in fact the case.

There are several methods for running host commands on the operating system that your database server runs on. One is to use the pipe mechanism provided by the dbms_pipe package (Job Submission uses this, by the way). Another way is to use an external C procedure and invoke the system() command. The only other way I know of is to invoke the exec() method of the Runtime class from within a Java Stored Procedure. I chose this method over the other two because I wanted to be able to capture what was sent to standard output after a command was run (as opposed to writing to a temporary file on the database server's file system and processing it from there), and I had seen something on a web site that showed me how to do this via a Java Stored Procedure in a (relatively) straightforward way. It wouldn't surprise me, however, if a more astute and experienced reader may be able to point out even cleaner, less complicated ways of achieving the same thing via pipes or external C procedures.

Creating a Java Stored Procedure

This section is for those who have never created a Java stored procedure. There are many online tutorials for doing so, and if you haven't figured it out by now, Google is a great tool for figuring out stuff on your own. Just visit www.google.com and enter the words "Java stored procedures" for example, to find other tutorials.

In order to create any Java stored procedure, you need to perform two steps:

1. Ceate a class definition on the database
2. Create call specifications for the methods within that class to allow PL/SQL procedures to call them

In order to create the class definition, you can either execute the CREATE JAVA command via SQL*Plus, and include below it the code for the class you wish to define, or you can create a .java file contining your java code and use the Oracle loadjava utility on your database's server or wherever else it may be installed. I've used both methods in the past, but I prefer to run the CREATE JAVA command directly in TOAD as a SQL*Plus script since I usually have TOAD open and doing so saves me an extra login. Here is an example of using the CREATE JAVA command to create a class called HelloWorld:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorld AS
  public class HelloWorld {
    public static String sayIt() {
      return "Hello World!";
    }
  }
/

And in order to create the call specification, you just need to execute the CREATE FUNCTION or CREATE PROCEDURE command. In this case we will use the CREATE FUNCTION command since the method we wish to call returns a value:

CREATE OR REPLACE FUNCTION HELLO_WORLD RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'HelloWorld.sayIt() return java.lang.String'; /

You can also pass in variables to java methods, which we will do later in this presentation. In fact if you would like to practice creating Java stored procedures (and functions, like the one above), try creating a function that takes a string and returns its uppercase equivalent. Hint: Java's string class has a toUpperCase() method to help you do this. If you get stuck, visit 12.46.245.173/help/sblitmwr.htm for an example.

If by this point, you've gotten the hang of creating basic Java stored procedures, the rest of this section should make sense. Suppose I wanted to see what processes were running on my database's server using the Linux ps command. The following line of Java code would do this:

Process p = Runtime.getRuntime().exec( '/bin/ps', '-ef' );

The /bin/ is required before the ps becomes no PATH environmental variable has been set. Keep in mind (and this took me a while to figure out) that the oracle user who is attempting to run the "ps" command is not going through a login or shell or anything else that tells the operating system what path it should take to find its executable; we need to be explicit about it.

Running this command isn't enough, however. We need to find some way of getting its output. In order to do that, we'll need to take advantage of the BufferedReader and StreamInputReader Java classes. I try to give credit where credit is due, so just to let you know, I did not come up with this approach on my own. I borrowed it from the web. My apologies to the man or woman who originally posted this technique and who deserves the credit that I should be giving to him or her now. Here's some Java code that uses the BufferedReader and StreamInputReader classes to grab the output of the ps command:

try {

  CLOB clob;
  Process p = Runtime.getRuntime().exec( '/bin/ps', '-ef' );
   
  InputStreamReader isr = new InputStreamReader( p.getInputStream() );
  InputStreamReader esr = new InputStreamReader( p.getErrorStream() );

  BufferedReader br = new BufferedReader( isr );
  BufferedReader er = new BufferedReader( esr );

  c.getEmptyCLOB();
  c.open( CLOB.MODE_READWRITE );
  inputClobWriter = c.setCharacterStream( 0L );

  while ((line = br.readLine()) != null)
  {
    inputClobWriter.write( line + "\n" );
  }
  while ((line = er.readLine()) != null)
  {
    inputClobWriter.write( line + "\n" );
  }
  inputClobWriter.flush();
  inputClobWriter.close();
  c.close();

}
catch (Exception e) {
  ;
}

The CLOB type is an Oracle extension to the Java language. If you're going to include it in your code don't forget to include the line

import oracle.sql.CLOB;

at the top of your Java source. CLOBs can hold hold up to 4 GB of characters, and we use it in this example as opposed to the native Java type String because in a little bit we'll be passing a CLOB as a reference (IN OUT) variable into a Java stored procedure and then publishing the contents of that CLOB after that procedure is finished executing. The calls to getEmptyCLOB(), open(), and setCharacterStream() are standard initialization calls I've found in various javadoc's of the oracle.sql.CLOB as well as various web sites/forums. At the moment I cannot seam to find a canonical copy of the Oracle JDBC API Reference to which I can direct the reader. There are copies that are out there on other people's web sites, however, and you'll be able to find the class definition for oracle.sql.CLOB on them.

The reader is encouraged to find out for himself/herself what the InputStreamReader and BufferedReader classes do. Sun's description of Java's InputStreamReader class states that an instance of this class can take a byte stream and convert it into a character stream. The Sun docs also reccomend the use of the BufferedReader class to save time by buffering the input stream in one fell swoop instead of reading sections of the input stream per run-time request and having to convert each portion read into the character set requested by the calling java program.

The reader will also observe that the above code snippet is encapuslated in a try/catch clause with the catch clause having being empty. One real life approach to encountering an exception would be to return a status code or error message to the calling PL/SQL procedure or -- and I know this is ugly -- include the error message in the CLOB that is to be returned to the calling procedure.

Implmenting Timeouts via Threads

This is the last and perhaps most difficult part of executing host commands via Java stored procedures. Suppose you ran a command that took a really long time to complete and it was blockeing the PL/SQL procedure that called it from doing anything in the meantime. You'd be stuck. In such a scenario you'd want some way of prematurely exiting the Java stored procedure if some predefined time limit were exceeded. There may be some way of implementing such a timeout on the PL/SQL end, but if there is I'm not aware of it. See Billy Verreynne's allusions to such possibilities. But I do know how to do this in Java using the Thread and Process classes.

The implementation of threads on the Java platform, in my opinion, was well thought out, relatively easy to understand, and easy to implement, especially given the (mental) complexity of keeping track of multiple threads to begin with. Sun's own Java Thread tutorial helped me very much to understand the Thread class enough to be able to implement timeouts. Once I stumbled upon Sun's documentation for the the p.waitFor() method of the Process class, it became clear to me how to put together the following code:

public static class PS_t implements Runnable {
  private oracle.sql.CLOB[] c;
  public PS_t(oracle.sql.CLOB[] initClob) {
    this.c = initClob;
  }
  public void run() {
    Writer inputClobWriter;
    try {
      Process p = Runtime.getRuntime().exec('/bin/ps', '-ef');
      p.waitFor(); // Wait for the system call above to complete
   
      InputStreamReader isr = new InputStreamReader( p.getInputStream() );
      InputStreamReader esr = new InputStreamReader( p.getErrorStream() );

      BufferedReader br = new BufferedReader( isr );
      BufferedReader er = new BufferedReader( esr );

      c[0].getEmptyCLOB();
      c[0].open( CLOB.MODE_READWRITE );
      inputClobWriter = c[0].setCharacterStream( 0L );

      while ((line = br.readLine()) != null)
      {
        inputClobWriter.write( line + "\n" );
      }
      while ((line = er.readLine()) != null)
      {
        inputClobWriter.write( line + "\n" );
      }
      inputClobWriter.flush();
      inputClobWriter.close();
      c[0].close();
    }
    catch (InterruptedException ie) {
      // timed out: this exception is raised by t.interrupt() in runPS_t() below
      return;
    }
    catch (Exception e) {
      return;
    }
    return;
 }


public static void runPS_t( oracle.sql.CLOB[] inputClob ) {
  Thread t = new Thread( new PS_t( inputClob ) );
  t.start();
  try {
    t.join( 10000 ); // Wait ten seconds or until t finishes, whichever comes first
                     // then proceed to the next line of code

    if ( t.isAlive() ) {
      t.interrupt(); // Raises the InterruptedException of t's run() method if by the time
                     // this line is reached run() is still "stuck" on p.WaitFor()
      t.join();
    }
    return;
  } catch (Exception e) {
    return;
  }
}

Although my official title here at Fuller Theological Seminary is "Database Administrator" I'm a computer programmer by experience and by my love for the discipline. As a programmer, I encourage anyone reading this who plans to simply copy the code and insert it into their application to really first understand Java thread operation by playing with some of your own sample code and make sure you can run and stop a thread. While it may be tempting to forego this in order to expedite the completion of whatever project you may be working on at the moment (especially if you're under tight deadlines), understanding technology such as this from the bottom-up will pay big dividends in the future, not only in being a more capable programmer, but in reducing debugging time, when you need to tweak the code you copied because you can't figure out why my code isn't working as expected.

Getting back to the code above, the way it would work is as follows:

1. The runPS_t() method is called by a PL/SQL procedure that passes to it as an argument a CLOB object. If the CLOB variable were named v_clob in the calling PL/SQL procedure, that procedure would call runPS_t with:
   

   runPS_t( v_clob );

2. runPS_t() starts a PS_t thread by executing "t.start()".
3. The execution thread of runPS_t() waits at most 10 seconds for the t thread to complete. This is what "t.join( 10000 )" means. In the meaintime, t's run() method is being invoked, and at some point, the line "p.waitFor()" is executed, which in turn causes t to wait for its p Process to complete its exec() call.
4. If p completes before ten seconds have expired, execution returns to the line "if ( t.isAlive() ) {" in runPS_t(), and since t.isAlive() will evaluate to false, runPS_t() will terminate.
5. If p exceeds its ten second limit, execution will still return to the line "if ( t.isAlive() )", but this time since t.Alive() will evaluate to true, the next line, "t.interrupt();" will execute and cause the InterruptException to be triggered in t's run() method. The key to making this work, by the way, is p.WaitFor(); without this, the InterruptException will not be triggered.

Server-Side SQL Plus Scripts and Parameter Passing

Running SQL scripts, and thus executing the sqlplus executable on your Oracle RDBMS's server, is simply an extension of Running Host commands in Java using the exec() method of the Runtime class. In our example above, we commanded the operating system to run "ps -ef". In this example, we're going to run the sqlplus executable instead. Conceptually, we're using the same mechanism, namely the Runtime method's exec() method. In practice, however, because of the way the exec() method parses its parameters and sends them to the operating system, we'll need to run the sqlplus executable indirectly through a shell script called "run_sqlplus.sh", which is what we'll ask the OS to execute via the exec() method.

String login = "scott/tiger@prod"
String scriptName = "courseroster.sql"
String scriptParameters = "G12345678 200404"
Process p = Runtime.getRuntime().exec( '/home/oracle/bash/run_sqlplus.sh', login, scriptName, scriptParameters );

#!/bin/bash

/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus -S $1 @/home/oracle/sql/$2 $3

Pretty straightforward. But the problem with this approach is that you're stuck with whatever parameters are hardcoded. If this script courseroster.sql were supposed to find out, say, all course that a given person registered for in a given quarter, it would always return the same set of courses for the same G# for the same term! Not ver flexible, is it? The question becomes, then, how to get parameters from a user at rutime. The solution is to get them via web forms and the PL/SQL Web Toolkit, which we discussed earlier.

For SQL Plus scripts, I have it set up on my own box so that the login is hardcoded in the PL/SQL procedure itself. The script name and parameters are read at runtime from the table general.gjbpdef which is populated using the form GJAPDEF. I opted for this method as opposed to hardcoding the sql script names and parameters in the PL/SQL code because I wanted those working outside of our IT department to be able to create their own sql scripts and define their parameters using standard Banner forms. I feel more comfortable doing this than teaching them PL/SQL! Of course even granting access to object maintenance forms such as GJAPDEF to non-IT staff may not work at your institution depending on your institution's security needs and concerns. Luckily for us, we have several technically savvy-managers whom we can trust with this type of power.

I'll run through the object and security maintenance forms a little bit later. One of those forms, GJAPDEF, is typically used to populate the table general.gjbpdef with the parameters that the user will need to specify whenever he/she tries to run a job in job submission. My model also uses the values in the table general.gjbpdef to dynamically generate prompts for the user whenever he/she is trying to run a SQL*Plus script via the web.

For now let's assume that the table general.gjbpdef has been populated for a job called "COURSEROSTER". Let's assume that for that job there exist two rows on that table, one where gjbpdef_desc = "Student's G#" and another where gjbpdef_desc = "Term". We grab these parameters at run-time with the following code:

CREATE OR REPLACE SQLPLUS_COURSEROSTER AS
TYPE    t_VARCHAR2_50 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
v_parms t_VARCHAR2_50;
BEGIN

<html>
<body>

SELECT gjbpdef_desc
  BULK COLLECT INTO v_parms
  FROM gjbpdef
 WHERE gjbpdef_job = 'COURSEROSTER'
 ORDER BY gjbpdef_number;

htp.print( '<p>'|| p_job || '</p>' );
htp.print( '<form action="RUN_SQL_SCRIPT">' );
htp.print( '<table>' );
FOR i IN 1..v_parms.COUNT LOOP
  htp.print( '<tr>' );
  htp.print( '<td>'|| v_parms(i) ||':</td><td><input type="text" name="p_parm"></td>' );
  htp.print( '</tr>' );
END LOOP;
htp.print( '</table>' );
htp.print( '<input type="hidden" name="p_script" value="'|| lower( p_job ) ||'.sql">' );
htp.print( '<input type="hidden" name="p_home" value="'|| p_home ||'">' );
htp.print( '<input type="submit" value="submit">' );
htp.print( '</form>' );

</body>
</html>

When this procedure is executed a form will be published with the following HTML:

<html>
<body>
<form action="RUN_SQL_SCRIPT">

<table>
<tr>
<td>Student's G#:</td><td>input type="text" name="p_parm"></td>
</tr>
<tr>
<td>Term:</td><td>input type="text" name="p_parm"></td>

</tr>
</table>
<input type="hidden" name="p_script" value="courseroster.sql">
<input type="submit" value="submit">
</form>
</body>
</html>

Now if the number and description of the parameters in the script changes, they can be changed using the form GJAPDEF. I'll show later how you can grab the name of the SQL script (in this case courseroster.sql) from a base menu instead of hard-coding it in the PL/SQL procedure as shown above.

The reader may notice that both parameters are named "p_parm". Won't that create a conflict in the receiving PL/SQL procedure? Actually, whenever two form elements are named the same, the PL/SQL Web Toolkit sends those parameters as an array of VARCHAR2s, or to be more specific, an index-by table of VARCHAR2s. In our example, the above form will try to send an array named p_parm to a procedure named RUN_SQL_SCRIPT. However, index-by tables cannot be defined globally as other Oracle types can; they must be defined in a PL/SQL package specification or within the procedure itself. This is why RUN_SQL_SCRIPT must be encapsulated within a package in order for it to be able to receive the web form's parameter.

A simple demonstration will suffice. Here's one way to encapuslate RUN_SQL_SCRIPT in a package called WEB_MENU where the index-by table type t_VARCHAR2_50 can be defined so that parameter passing is successful and a 404 Not Found error can be avoided:

CREATE OR REPLACE PACKAGE WEB_MENU AS

  TYPE t_VARCHAR2_50 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  PROCEDURE RUN_SQL_SCRIPT( p_parm t_VARCHAR2_50, p_script VARCHAR2 );

END WEB_MENU;
/


CREATE OR REPLACE PACKAGE BODY WEB_MENU AS

  PROCEDURE RUN_SQL_SCRIPT( p_parm t_VARCHAR2_50, p_script VARCHAR2) IS
    v_parmString VARCHAR2(32767) := NULL;
  BEGIN

    v_parmString := p_parm(1);

    FOR i in 2..p_parm.COUNT LOOP
      v_parmString := v_parmString ||' '|| p_parm(i);
    END LOOP;

    -- At this point we would call our Java stored procedure that would run courseroster.sql; 
    -- one of the parameters we would pass to it is the parameter string v_parmString formed above

  END;

END WEB_MENU;
/

All we need to do is modify the original action attribute of the calling form's tag element from:

<form action="RUN_SQL_SCRIPT">

<form action="WEB_MENU.RUN_SQL_SCRIPT">

Using the PL/SQL Document Table

Originally I inteded to show the reader how to load database tables running SQL Loader scripts over the web. While writing that section, however, I realized my design might not handle well the scenario in which two users try to run a given SQL Loader control file simultaneously, since the .dat files to be uploaded need to have the same filename, the name specified in the control file. It occurred to me that if one user were to place his/her .dat file in this directory, and a split second later another user were to do the same with an identically named .dat file, the first user might run a SQL Loader script and load the other user's data instead of his/her own.

Furthermore, I originally wanted to be able to monitor what tables were being loaded. Given that there weren't a whole lot of SQL Loader scripts out there on our current system, and that I didn't see a whole lot more being created, I figured that it wouldn't overly burdensome to convert each of these by hand to web forms that requested the user to upload the files using the file select control on a web form and reading the contents of the file from the PL/SQL Document table once the form is processed.

This method amounts to putting on a Web form a widget that would allow the user to browse and select any file on his/her computer that he/she would want to upload. The file could be a text file or binary file, though here we're concerned only with the former. Once the form, i submitted the PL/SQL Web Toolkit would do two things:

1. Load the contents of the file to the PL/SQL Document Table specified in the PlsqlDocumentTablename directive of the Data Access Descriptor.
2. Sends the name of the file, along with any other parameters embedded in the form, to the PL/SQL procedure defined in the action attribute of the web form's <form> tag. Note that this filename will be a sequence of random characters ("F448442039/", for example) followed by the actual name of the file.

If the PlSqlDocumentTablename directive is not defined, mod_plsql will try to load the contents into a table called wpg_document. If it is not defined, then you must create it with the following script:

create table wpg_document
(
 NAME                                     VARCHAR2(64),
 MIME_TYPE                                VARCHAR2(48),
 DOC_SIZE                                 NUMBER,
 DAD_CHARSET                              VARCHAR2(128),
 LAST_UPDATED                             DATE,
 CONTENT_TYPE                             VARCHAR2(128),
 CONTENT                                  long raw,
 BLOB_CONTENT                             BLOB,
 constraint wpg_document_pk primary key( name )
);
grant select, insert, update, delete on wpg_document to public;
create public synonym wpg_document for wpg_document;

A couple of things. First, since you are creating a public synonym for it, it doesn't matter what schema you create this table in (mine happens to be in the SYSTEM schema). Second, depending on whether or not you have defined the PlsqlUploadAsLongRaw directive(s) in your DAD, the contents of the file you upload will be stored either in the CONTENT or the BLOB_CONTENT columns of the PL/SQL Document Table. If the file you upload has an extension that is included in a PlsqlUploadAsLongRaw directive, it will be uploaded into the CONTENT column of wpg_document; otherwise it will go into the BLOB_CONTENT column. See the Oracle HTTP Server mod_plsql User's Guide for more information.

Now let me show you a sample PL/SQL procedure generating a Web form that requests from the user a file to upload:

PROCEDURE REQUEST_INPUT_FILE IS
BEGIN

htp.print( '<html>' );
htp.print( '<body>' );
htp.print( '<p>Submitting this form will take the contents of the file you specify and load them '||
  'into the wpg_document table of your database, or whatever table is defined in your Data Access Descriptor.<p>' );
htp.print( '<hr>' );
htp.print( '<form enctype="multipart/form-data" action="/prod/wtailor.READ_INPUT_FILE" method="post">' );
htp.print( '<p>Please select your file</p>' );
htp.print( '<p style="margin-top:25px">Input File: <input type="file" name="p_input_file"></p>' );
htp.print( '<p><input type="submit" value="submit"></p>' );
htp.print( '</form>' );
htp.print( '</body>' );
htp.print( '</html>' );

EXCEPTION WHEN OTHERS THEN
  htp.print( SQLERRM );
END;

One thing I will point out is that the <form> tag's enctype attribute is set to "multipart/form-data". The enctype must be included and set to this value in order for all of this to work. Once the submit button is pushed, the LOAD_INPUT_FILE can then access the contents of the file by reading the "blob_content" field of the row that was just loaded into the PL/SQL Document Table. This row will be the one whose "name" field equals the value of the p_input_file parameter passed to LOAD_INPUT_FILE. But since this field will contain a BLOB value, it will be necessary to convert the BLOB to a table of VARCHAR2s. (It is possible to simply read the "content" field, but we would have to be assured that the file uploaded was no larger than 32K).

Here is an example of what LOAD_INPUT_FILE might look like, as well as blobToVC2table, which will convert a BLOB to a table of VARCHAR2s:

PROCEDURE READ_INPUT_FILE( p_input_file VARCHAR2 ) IS
         TYPE t_VARCHAR2_32767 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
v_fileName    VARCHAR2(50);
v_linesOfText t_VARCHAR2_32767;
v_serverInput utl_file.file_type;
v_awkDir      VARCHAR2(50);     
BEGIN



v_linesOfText := blobToTableOfVc( p_input_file );

DELETE FROM wpg_document WHERE NAME = p_input_file;
COMMIT;

htp.print( '<html>' );
htp.print( '<body>' );

FOR i IN 1..v_linesOfText.COUNT LOOP
  htp.print( v_linesOfText(i) );
END LOOP;

htp.print( '</html>' );
htp.print( '</body>' );

EXCEPTION WHEN OTHERS THEN
  htp.print( SQLERRM );
END;

FUNCTION blobToTableOfVc( p_file VARCHAR2 ) RETURN t_VARCHAR2_32767 IS
v_linesOfText  t_VARCHAR2_32767;
v_beginLine    NUMBER := 1;
v_endLine      NUMBER;
v_BLOBlength   NUMBER;
v_safetyValve  NUMBER := 0;
v_LF           NUMBER := 0;
BEGIN

SELECT LENGTH( blob_content ) INTO v_BLOBlength FROM wpg_document WHERE NAME=p_file;

LOOP
  -- Exit this loop once we've hit the safety valve.
  v_safetyValve := v_safetyValve + 1;
  IF v_safetyValve > 10000 THEN
    v_linesOfText( v_linesOfText.COUNT + 1 ) := 'blobToTableOfVc has looped 10000 times';
  END IF;
  EXIT WHEN v_safetyValve > 10000;
  
  -- Find the first line feed beggining at v_beginLine
  SELECT dbms_lob.INSTR( blob_content, '0A', v_beginLine ) INTO v_LF FROM wpg_document WHERE NAME = p_file;

  IF v_LF = 0 THEN
    -- last line
	SELECT utl_raw.cast_to_varchar2( dbms_lob.SUBSTR( blob_content, v_BLOBlength - v_beginLine + 1, v_beginLine ) )
	  INTO v_linesOfText( v_linesOfText.COUNT + 1 )
	  FROM wpg_document
	 WHERE NAME = p_file;
	EXIT;
  ELSIF v_LF = v_beginLine THEN
    -- no string to grab
	v_linesOfText( v_linesOfText.COUNT + 1 ) := '';
  ELSIF v_LF > 0 THEN
    NULL;
  END IF;

  -- The outer call to translate strips out any carriage returns in case the
  -- file was uploaded from a DOS/Windows machine. Tom Kyte suggests that the
  -- CHR(1) character is a good dummy character to use with the translate
  -- command.
  SELECT translate( 
           utl_raw.cast_to_varchar2( dbms_lob.SUBSTR( blob_content, v_LF - v_beginLine, v_beginLine ) ), 
           chr(1)||chr(13), chr(1)
         )
    INTO v_linesOfText( v_linesOfText.COUNT + 1 )
    FROM wpg_document
   WHERE NAME = p_file;

  v_beginLine := v_LF + 1; 

  IF v_beginLine > v_BLOBlength THEN
	EXIT;
  END IF;

END LOOP;

RETURN v_linesOfText;

EXCEPTION WHEN OTHERS THEN
  htp.print( 'SQLERRM );
END;

The reader would do well to study the preceding examples carefully and try them out in his/her own environment. In the code above, I simply print out each line of the file. If you wanted to upload the contents of each line into a given table, you would have to "tokenize" each element of the line (that is, separate its contents into different tokens), then issue an INSERT command to upload these token together as one partial or complete row of that table. Here is one "tokenizer" that I wrote a while back which will allow you to choose which character you use as a delimiter:

FUNCTION getTokens( p_line VARCHAR2 ) RETURN t_VARCHAR2_32767 IS
v_sepPtr      INTEGER;
v_leftDelPtr  INTEGER;
v_rightDelPtr INTEGER;
v_tokens      t_VARCHAR2_32767;
v_lineLeft    VARCHAR2( 32767 ) := p_line;
BEGIN

LOOP
  v_leftDelPtr := INSTR( v_lineLeft, '''' );
  v_rightDelPtr := INSTR( v_lineLeft, '''', v_leftDelPtr + 1 );
  
  v_tokens( v_tokens.COUNT + 1 ) := 
    SUBSTR( v_lineLeft, v_leftDelPtr + 1, v_rightDelPtr - v_leftDelPtr - 1 );

  v_sepPtr := INSTR( v_lineLeft, ',', v_rightDelPtr + 1 );
  EXIT WHEN v_sepPtr = 0 OR v_sepPtr IS NULL;
  v_lineLeft := SUBSTR( v_lineLeft, v_sepPtr + 1 ); 
END LOOP;

RETURN v_tokens;

END;

The above "tokenizer" will work with commas, tabs, you name it. One thing you have to make sure you don't do, however, is use a comma as a delimiter when the various tokens themselves contain commas. For example, it wouldn't make sense to use a comma as a delimiter when uploading contact information if some of the names in my list contained a comma in the name field itself ("Joseph Bautista, Jr.", for example). The reader is left on his/her own to implement the getTokens procedure above with the READ_INPUT_FILE, replacing the HTML code that merely displays the lines of the user input file, with code that actually uploads the file's contents to a table on the database.

utl_file: Writing to Your Database Server's File System

When running Awk scripts as shown in the next section, we'll need to take a copy of the user's input file and store it somewhere on the database server's file system. So far we've learned how to take a copy of a local file and store it on the PL/SQL document table. We even know how to read its contents from the document table (SELECT blob_content FROM wpg_document). But how would we take its contents and write them to the database sever's file system so that the Awk script can access its own local copy?

utl_file is a package that comes with every standard Oracle rdbms installation that allows the oracle user to write to the file system on which its database resides. The first step in using utl_file from PL/SQL is to create a directory object that will represent the directory to which you will write. Later on we will place our awk scripts and input files in the directory /home/oracle/awk_scripts. In order to create a directory object named 'AWK_SCRIPTS' and have it readable by the wtailor user, you must run the following script:

CREATE OR REPLACE DIRECTORY 
AWK_SCRIPTS AS 
'/home/oracle/awk_scripts';

GRANT READ, WRITE ON DIRECTORY AWK_SCRIPTS TO WTAILOR WITH GRANT OPTION;

Keep in mind that at the operating system level this directory must also be granted read privileges to the oracle user. Also if my memory serves me correctly, for whatever reason, the directory must also be executable. I don't know if this was a bug introduced by Oralce if there are reasons for this strangeness. Let's now go back to the example of the previous section. Instead of writing the output to the web, let's write it to the directory AWK_SCRIPTS directory. To do that, the code would change to the following:

PROCEDURE READ_INPUT_FILE( p_input_file VARCHAR2 ) IS
         TYPE t_VARCHAR2_32767 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
v_linesOfText t_VARCHAR2_32767;
BEGIN

v_linesOfText := blobToTableOfVc( p_input_file );

DELETE FROM wpg_document WHERE NAME=p_input_file;
COMMIT;

-- Write the file onto the server
v_fileName := substr( p_input_file, instr( p_input_file, '/' ) + 1 );
v_serverInput := utl_file.fopen( 'AWK_SCRIPTS', v_fileName, 'W' );
FOR i IN 1..v_linesOfText.COUNT LOOP
  utl_file.put_line( v_serverInput, v_linesOfText(i) );
END LOOP;
utl_file.fclose( v_serverInput );

END;

Tying it all together

By this point in the presentation we have show the elementary methods upon which a more robust and much "fancier" menuing system can be built -- a system that can run from the Web not only PL/SQL scripts, but other types of scripts as well. This final section will show, one possible implementation of that system.

Running SQL Plus Scripts via the Web

The first step will be to FTP your SQL plus scripts to your database server. I suggest FTPing all the scripts to a single directory that can be read by the oracle user. I created a "sql_scripts" directory under my oracle user's home directory (/home/oracle/sql_scripts) and that is the directory I will assume you are using, although by no means is this required.

Keep in mind also that your SQL scripts may need to be configured so that they can accept parameters from the command line via the '&&' syntax. Let me use our courseroster.sql script as an example:

SET     ECHO off
SET     VERIFY off
SET     RECSEP off
SET     NEWPAGE 1

TTI  LE 'Course Roster' -
     R  'CHKCLAS' SKIP 2 -
     CE 'Course registration by student/term' SKIP2

COLUMN spriden_id        HEA ID FOR A10
COLUMN sfrstcr_term_code HEA Term FOR A6
COLUMN sfrstcr_crn       HEA CRN FOR A5
COLUMN sfrstcr_rsts_code HEA Status FOR A2
SELECT spriden_id, sfrstcr_term_code, sfrstcr_crn, sfrstcr_rsts_code
  FROM spriden, sfrstcr
 WHERE spriden_id = '&&1' AND
       spriden_change_ind IS NULL AND
       sfrstcr_pidm = spriden_pidm AND
       sfrstcr_term_code = '&&2';

EXIT;

SQL Plus will replace '&&1' with the first parameter sent to the script and '&&2' with the second.

As mentioned earlier, we can't run sqlplus directly via a call to the Runtime class's exec() method, but instead must use the exec() method to call a bash shell script which will in turn call sqlplus. Create your own copy of run_sqlplus.sh in /home/oracle/sql_scripts/.

Now let's create the Java stored procedure that will be used to call run_sqlplus.sh. You can do this by running the following script on your database:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Host" AS

import java.io.*;
import oracle.sql.CLOB;

public class Host {
  public static void startSqlScriptThread( String login, String script, String parms, String[] error, oracle.sql.CLOB[] output ) {
    Thread t = new Thread( new SqlScriptThread( login, script, parms, error, output ) );
    t.start();
    try {
      t.join(10000); // Wait ten seconds
      if (t.isAlive()) {
        t.interrupt();
        t.join();
      }
    } catch (Exception e) {
      error[0] = e.getMessage(); // One chance in a million that error[0] already contains something from
                                 // entering one of the two exception clauses in t's run() method
    }
  }

  public static class SqlScriptThread implements Runnable {
    private String login;
    private String script;
    private String parms;
    private String[] error;
    private oracle.sql.CLOB[] output;
    public SqlScriptThread( String login, String script, String parms, String[] error, oracle.sql.CLOB[] output ) {
      this.login = login;
      this.script = script;
      this.parms = parms;
      this.error = error;
      this.output = output;
    }
    public void run() {
      String line = new String();
      String[] execArray = {"/home/oracle/sql_scripts/run_sqlplus.sh", login, script, parms};
      Writer inputClobWriter;
      try {
        Process p = Runtime.getRuntime().exec( execArray );
        p.waitFor();
   
        InputStreamReader isr = new InputStreamReader( p.getInputStream() );
        InputStreamReader esr = new InputStreamReader( p.getErrorStream() );

        BufferedReader br = new BufferedReader( isr );
        BufferedReader er = new BufferedReader( esr );

        output[0].getEmptyCLOB();
        output[0].open( CLOB.MODE_READWRITE );
        inputClobWriter = output[0].setCharacterStream( 0L );
        while ((line = br.readLine()) != null)
        {
          inputClobWriter.write( line + "\n" );
        }
        while ((line = er.readLine()) != null)
        {
          inputClobWriter.write( line + "\n" );
        }
        inputClobWriter.flush();
        inputClobWriter.close();
        output[0].close();
      }
      catch (InterruptedException ie) {
        error[0] = "Your job timed out after 10 seconds";
      }
      catch (Exception e) {
        error[0] = e.getMessage();
      }
    }
  }
}
/

And don't forget the call specification:

CREATE OR REPLACE PROCEDURE RUN_SQLPLUS( p_login VARCHAR2, p_script VARCHAR2, p_parms VARCHAR2, p_error VARCHAR2, p_output IN OUT CLOB )
AS LANGUAGE JAVA
NAME 'Host.startSqlScriptThread( java.lang.String, java.lang.String, java.lang.String, java.lang.String, oracle.sql.CLOB[] )';
/

It's now time to work on the PL/SQL side of things. Let's create the WEB_MENU package specification as follows:

CREATE OR REPLACE PACKAGE WEB_MENU AS

TYPE t_VARCHAR2_50 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

PROCEDURE GET_SQL_PARMS( p_job VARCHAR2, p_home VARCHAR2 );
PROCEDURE SHOW_SQL_RESULTS( p_parm t_VARCHAR2_50, p_script VARCHAR2, p_home VARCHAR2 );

END WEB_MENU;
/

And now for the package body of WEB_MENU:

CREATE OR REPLACE PACKAGE BODY WEB_MENU AS

TYPE t_PLSINT_a IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
PROCEDURE WriteCLOBtoWeb( p_output CLOB );

PROCEDURE htmlOpen IS
BEGIN
  htp.print( '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"' );
  htp.print( '"http://www.w3.org/TR/html4/loose.dtd">' );
  htp.print( '<html>' );
  htp.print( '<body>' );
END;

PROCEDURE htmlClose IS
BEGIN
  htp.print( '</body>' );
  htp.print( '</html>' );
END;



-- Looks up p_job on general.gjbpdef and publishes its parameters to an HTML form to be filled in by the user

PROCEDURE GET_SQL_PARMS( p_job VARCHAR2, p_home VARCHAR2 ) IS
  v_parms    t_VARCHAR2_50;
  v_exists   t_PLSINT_a;
  v_allowed  BOOLEAN := FALSE;
  v_jobName  bansecr.guruobj.guruobj_object%TYPE;
  v_period   PLS_INTEGER := 0;
  v_username gobeacc.gobeacc_username%TYPE;
  v_pidm     spriden.spriden_pidm%TYPE;
BEGIN

  IF NOT Twbkwbis.F_ValidUser( v_pidm ) THEN
    htp.print( 'You do not have the necessary credentials to call GET_SQL_PARMS().' );
    RETURN;
  END IF;

  -- Get the user's Banner name
  SELECT gobeacc_username
    INTO v_username
    FROM gobeacc
   WHERE gobeacc_pidm = v_pidm;

  -- Check the Banner security tables guruobj and gurucls to verify this user has permission to access the object p_job
  IF OKtoRun( p_job, v_username ) = FALSE THEN
    htp.print( 'You do not have the necessary credentials to run '|| lower( p_job ) ||'.sql.' );
    RETURN;
  END IF;

  SELECT gjbpdef_desc
    BULK COLLECT INTO v_parms
    FROM gjbpdef
   WHERE gjbpdef_job = p_job
   ORDER BY gjbpdef_number;

  htmlOpen; 
  htp.print( '<p>'|| p_job || '</p>' );
  htp.print( '<form action="WEB_MENU.SHOW_SQL_RESULTS" method="post">' );
  htp.print( '<table>' );
  FOR i IN 1..v_parms.COUNT LOOP
    htp.print( '<tr>' );
    htp.print( '<td>'|| v_parms(i) ||':</td><td><input type="text"   name="p_parm"></td>' );
    htp.print( '</tr>' );
  END LOOP;
  htp.print( '</table>' );
  htp.print( '<input type="hidden" name="p_script" value="'|| lower( p_job ) ||'.sql">' );
  htp.print( '<input type="hidden" name="p_home" value="'|| p_home ||'">' );
  htp.print( '<input type="submit" value="submit">' );
  htp.print( '</form>' );
  htmlClose;

EXCEPTION WHEN OTHERS THEN 
  htp.print( SQLERRM );
END;



-- Receives the parameters sent in from the HTML form published by GET_SQL_PARMS and passes these to the shell
-- script run_sqlplus.sh. That shell script will in turn call the SQL Plus script p_script and the output
-- of that will be captured in v_output.

PROCEDURE SHOW_SQL_RESULTS( p_parm t_VARCHAR2_50, p_script VARCHAR2, p_home VARCHAR2 ) IS
  v_errString  VARCHAR2(500) := NULL;
  v_parmString VARCHAR2(500) := NULL;
  v_output     CLOB := CHR(0); -- this will bomb without the CHR(0)
BEGIN

  -- At this point if you have not accounted for the script injection vulnerability mentioned in SHE FAQ 1-2PE6V7
  -- you should again verify that the user should be allowed to run p_script as done in GET_SQL_PARMS

  v_parmString := p_parm(1);
  FOR i in 2..p_parm.COUNT LOOP
    v_parmString := v_parmString ||' '|| p_parm(i);
  END LOOP;

  run_sqlplus( 'wtailor/u_pick_it@prod', p_script, v_parmString, v_errString, v_output );

  twgzssut.htmlOpen;
  htp.print( '<p style="text-align:center"><input type="button" onClick="location.href=''/'||
    p_home ||''';" value="Return to Menu"></p>'
  );
  WriteCLOBtoWeb( v_errString );
  WriteCLOBtoWeb( v_output );
  htp.print( '<p style="text-align:center"><input type="button" onClick="location.href=''/'||
    p_home ||''';" value="Return to Menu"></p>'
  );
  twgzssut.htmlClose;

EXCEPTION WHEN OTHERS THEN
  htp.print( SQLERRM );
END;


-- Converts a CLOB type to HTML

PROCEDURE WriteCLOBtoWeb( p_output CLOB ) IS
  v_length     PLS_INTEGER;
  v_blockCount PLS_INTEGER := 1;
  v_Pos        PLS_INTEGER := 1;
  v_Buffer     VARCHAR2(32767);
  BUFFER_SZ    CONSTANT PLS_INTEGER := 32767;
  v_readAmt    PLS_INTEGER := BUFFER_SZ;
BEGIN

  v_length := dbms_lob.getlength( p_output );
  IF v_length > BUFFER_SZ THEN
    v_blockCount := v_length/BUFFER_SZ + 1;
  END IF;

  htp.print( '<pre>' );
  FOR i IN 1..v_blockCount LOOP
    dbms_lob.READ( p_output, v_readAmt, v_Pos, v_Buffer );
    htp.prn( v_Buffer );
    v_Pos := v_Pos + BUFFER_SZ;
  END LOOP;
  htp.print( '</pre>' );

EXCEPTION WHEN OTHERS THEN
  htp.print( SQLERRM );
END;

END WEB_MENU;
/

The PL/SQL procedure GET_SQL_PARMS is itself called from a static web page on your application server. If you want other non-IT personnel to be able to modify their reporting menus, which was the primary motivation beind the development of this reporting model for our institution, you'll need to give them FTP access to your application server. I'd suggest creating a user, one for each department or departmental manager who will want to be the keeper of his/her department's reporting menus, then creating a directory just below the document root for each department. The document root of your application server is $ORACLE_HOME/Apache/Apache/htdocs. Since our SQL script courseroster.sql checks registration status, create the directory "registrar" right underneath your document root. Below is a sample file mainmenu.htm; create it locally and FTP it to that "registrar" directory or just log into the server and create it there:

<!-- mainmenu.htm -->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<h1>Registrar's Main Menu</h1>

<p><a href="/prod/WEB_MENU.GET_SQL_PARMS?p_job=COURSEROSTER&p_home=mainmenu.htm">Course Roster</a></p>
<!-- You may place future menu items here -->
</body>
</html>

Once mainmenu.htm exists on $ORACLE_HOME/Apache/Apache/htdocs/registrar/, you can access it by entering "http://[AppServerName]:[port]/registrar/mainmenu.htm" on your broswer.

Now we need to do create and define an object called COURSEROSTER on the Banner Security and Object Maintenance forms. To access the Banner Security forms, you need to log in as the BANSECR user. If you forgot the URL that you need to enter to get to these forms, look in your $ORACLE_HOME/forms/server/formsweb.cfg and find the entry that contains

form=gsasecr.fmx

http://[serverName]:[port]/forms/frmservlet?config=[entryName]

Once you can are in GSASECR, click on the Objects tab. Insert a record; enter COURSEROSTER in the "Object" field, and depending on whether or not you're on Banner 7 or 8, put 7.0 or 8.0 as your current version. It doesn't really matter what System Code you choose, but "S" seems appropriate since we're looking at student registration history. Choose BAN_DEFAULT_Q. Save the record.

Now let's define what roles grant access to this object. Click on the Classes tab. Select one of the "Class Codes" there on the left side of the form (BAN_STUDENT_C, for example) then click on the Objects button at the bottom of the form. Insert a record for COURSEROSTER, enter BAN_DEFAULT_Q as the "Role Name", and save it.

Click Close on the bottom right, then click on Synchronize at the bottom. At this point log out of Banner Security and log back in as a user that has the privileges to access the GJAJOBS and GJAPDEF forms. Once you get into GJAJOBS (for some reason, on our installation you have to execute a query before you can insert a record) enter a record for COURSEROSTER, making sure to select S for "System". And even though it doesn't really matter, select "Procedure" as the type.

Save your entry and now visit GJAPDEF. Enter COURSEROSTER as the "Process" and go the next block. Your cursor should be to the right of the field "Parameter". Enter 01. The cursor should move to the right once more where you should enter "Student G#". Enter a "Length" of 9 and select "Required". Save this record, then insert another record whose "Parameter" is 02, whose description is "Term", whose "Length" is 6, and which is "Required". Save this record as well.

To run courseroster.sql you would visit "http://[AppServerName]:[port]/registrar/mainmenu.htm".

Running Awk Scripts via the Web

The folowing awk script adddashes.awk adds dashes to the third field of an input file so that if the third field were a phone number like "6265845237" it would convert it to "626-584-5237":

BEGIN { FS = "," }

{ print substr($3,1,3)"-"substr($3,4,2)"-"substr($3,6,4) }

We'll put that in /home/oracle/awk_scripts/. You don't have to understand the syntax, but in case curious I'd recommed reading the book "Sed & Awk" by Dale Dougherty and published by O'Reilly and Associates, Inc. Because of the issues with respect to parameter passing and the Runtime class's exec() method, as with SQL Plus scripts, we can't call adddashes.awk directly. We must call it indirectly via run_awk.sh, which we'll also put in /home/oracle/awk_scripts/:

#!/bin/bash

/bin/awk -f $1 $2 > $3

Finally, here is the code for the Java stored procedures (see if you can spot the similarities and differences between it and the code for running SQL Plus scripts):

  public static void startAwkScriptThread( String script, String inputPath, String outputPath, String[] error, oracle.sql.CLOB[] output ) {
    Thread t = new Thread( new AwkScriptEngine( script, inputPath, outputPath, error, output ) );
    t.start();
    try {
      t.join(10000); // Wait ten seconds
      if (t.isAlive()) {
	t.interrupt();
        t.join();
      }
      return;
    } catch (Exception e) {
      return;
    }
  }

  public static class AwkScriptThread implements Runnable {
    private String script;
    private String inputPath;
    private String outputPath;
    private String[] error;
    private oracle.sql.CLOB[] output;
    public AwkScriptEngine( String script, String inputPath, String outputPath, String[] error, oracle.sql.CLOB[] output ) {
      this.script = script;
      this.inputPath = inputPath;
      this.outputPath = outputPath;
      this.error = error;
      this.output = output;
    }
    public void run() {
      String line = new String();
      String[] execArray = { "/home/oracle/awk_scripts/run_awk.sh", script, inputPath, outputPath };
      Writer inputClobWriter;
      try {
        Process p = Runtime.getRuntime().exec( execArray );
        p.waitFor();
	  
        InputStreamReader isr = new InputStreamReader( p.getInputStream() );
        InputStreamReader esr = new InputStreamReader( p.getErrorStream() );

        BufferedReader br = new BufferedReader( isr );
        BufferedReader er = new BufferedReader( esr );

        output[0].getEmptyCLOB();
        output[0].open( CLOB.MODE_READWRITE );
        inputClobWriter = output[0].setCharacterStream( 0L );

        while ((line = br.readLine()) != null)
        {
          inputClobWriter.write( line + "\n" );
        }
        while ((line = er.readLine()) != null)
        {
          inputClobWriter.write( line + "\n" );
        }
        inputClobWriter.flush();
        inputClobWriter.close();
        output[0].close();
      }
      catch (InterruptedException ie) {
        error[0] = "Your job timed out after 10 seconds";
        return;
      }
      catch (Exception e) {
	error[0] = e.getMessage();
      }
      return;
    }
  }

The following procedure gets the input files and output directory from the user:

PROCEDURE LOAD_INPUT_FILE( p_job VARCHAR2, p_home VARCHAR2 ) IS
BEGIN

htp.print( '<html>' );
htp.print( '<body>' );
htp.print( '<p>This will grab a file, put it into your awk directory, and '||
  'by applying an awk script, modify the text.</p>' );
htp.print( '<hr>' );
htp.print( '<form enctype="multipart/form-data" action="twgztest.PARSE_AWK_FILE" method="post">' );
htp.print( '  <p>Input File: <input type="file" ' ||
  'name="p_input_file"></p>' );
htp.print( '  <p>Output Directory: ' );
htp.print( '  <select name="p_output_dir">' );
htp.print( '    <option value="/home/bautista/awk/">/home/bautista/awk/</option>' );
htp.print( '    <option value="/home/sfs/">/home/sfs/</option>' );
htp.print( '  </select></p>' );
htp.print( '  <input type="hidden" name="p_job" value="'|| p_job ||'">' );
htp.print( '  <input type="hidden" name="p_home" value="'|| p_home ||'">' );
htp.print( '  <p style="margin-top:25px"><input type="submit" value="Modify & Spit"></p>' );
htp.print( '  </form>' );
htp.print( '  </body>' );
htp.print( '</html>' );

EXCEPTION WHEN OTHERS THEN
  htp.print( SQLERRM );
END;

The output directories are hard-coded for didactic purposes. It assumes that the only user who runs this is "bautista". You would never make such an assumption, of course, but the point is that he is given the option of writing the output of the Awk script to a directory named "awk" just beneath his home directory or to his departmental directory "/home/sfs". In a production environment, you could create a table that mapped users to the directories that they should be able to write to, then at run-time, find out who the user is using the twbkwbis.F_ValidUser function (as in GET_SQL_PARMS above) and iunclude those as <option>s for the select control.

Finally, we have the PL/SQL procedure that will process the values of the above web form:

b_AWK_DIR CONSTANT VARCHAR2(11) := 'AWK_SCRIPTS';

PROCEDURE RUN_AWK( p_input_file VARCHAR2, p_output_dir VARCHAR2 ) IS
v_fileName    VARCHAR2(50);
v_linesOfText t_VARCHAR2_32767;
v_serverInput utl_file.file_type;
v_output      CLOB := CHR(0);
v_awkDir      VARCHAR2(50);     
BEGIN

v_linesOfText := blobToTableOfVc( p_input_file );

DELETE FROM wpg_document WHERE NAME=p_input_file;
COMMIT;

-- Write the file onto the server
v_fileName := substr( p_input_file, instr( p_input_file, '/' ) + 1 );
v_serverInput := utl_file.fopen( b_AWK_DIR, v_fileName, 'W' );
FOR i IN 1..v_linesOfText.COUNT LOOP
  utl_file.put_line( v_serverInput, v_linesOfText(i) );
END LOOP;
utl_file.fclose( v_serverInput );

SELECT DIRECTORY_PATH 
  INTO v_awkDir
  FROM DBA_DIRECTORIES
 WHERE directory_name = 'AWK_SCRIPTS';

run_awk_web( v_awkDir ||'/'|| lower( p_job ) ||'.awk', v_awkDir ||'/'|| v_fileName, p_output_dir || v_fileName, v_output );

htmlOpen;
WriteCLOBtoWeb( v_output );
IF v_errString = '' THEN
  htp.print( v_filename ||' was successfully formatted.' );
ELSE
  WriteCLOBtoWeb( v_errString );
END IF;
htp.print( '<p style="text-align:center"><input type="button" onClick="location.href=''/'|| p_home ||'''" value="Return to Menu"</p>' );
htmlClose;

END;