java 调用 oracle sqlloader,用pl/sql调用sql*loader

DBMS_PIPE

Unfortunately, PL/SQL doesn't let you perform host operating-system commands and doesn't provide a facility for\ performing terminal I/O. In this article, Ron shows you how he used Oracle pipes to work around these limitations.

Although PL/SQL provides all of the features of control structure, variable declarations, and exception handling, it lacks two facilities that are available in most imperative programming languages today. First, PL/SQL does not provide a facility for performing host operating-system commands, which would be very helpful in executing a variety of tasks - such as issuing print commands or sending email - from within PL/SQL blocks. Second, it lacks a facility for performing terminal I/O. Although the Oracle-supplied DBMS_OUTPUT package addresses some of the problems of screen output, you still must resort to Pro*C, Oracle Forms, or third-party programming tools to perform a rudimentary function such as accepting user input while processing Oracle data.

Although the need to utilize the features of I/O and host commands within PL/SQL may be infrequent, there are specific times when this ability would be invaluable. I've discovered a way to

use Oracle pipes to develop database procedures that effectively extend the capabilities of PL/SQL to include both of these functions. In this article, I show you some examples of how to perform simple terminal I/O and issue Unix operating-system commands from within a PL/SQL block. The implementation of the procedures for terminal I/O is suited for use with anonymous PL/SQL blocks executed from SQL*Plus; the procedure for Unix commands can be issued from within database triggers or stored procedures in much the same way as the HOST command is issued within Oracle Forms.

Oracle Pipes

When two processes wish to communicate with one another, they must do so through some sort of communication link. This link can be established in a variety of ways, depending on the type and location of the processes. Regardless of the physical implementation of the link, most operating systems provide some mechanism that lets the processes establish a link and then perform read and write operations on it. The Oracle DBMS_PIPE package provides a mechanism that can be used for indirect communication between two or more cooperating Oracle sessions.

The Oracle pipes facility provided by the DBMS_PIPE package is similar to some of the IPC (interprocess communication) facilities provided by the Unix operating system. Although the Oracle facility is referred to as a pipe, it is actually much more analogous to the indirect form of communication known as the Unix message queue. A Unix pipe is associated with two communicating processes that have a parent/child relationship - that is, where one has been spawned by the other. With Oracle pipes, as with the Unix message queue, two otherwise unrelated processes can communicate with one another. These processes must be Oracle sessions within the same database instance.

The use of Oracle pipes is actually quite simple. Messages are built using the PACK_MESSAGE procedure, which puts message text into a local message buffer. The message is subsequently sent on a named pipe by calling the SEND_MESSAGE function. A part of the SGA (System Global Area) is reserved for this pipe when it is first referenced with the SEND_MESSAGE function. Any other Oracle session can then receive the packaged message with the RECEIVE_MESSAGE function. The named pipe is accessible by any number of readers and writers. After calling the RECEIVE_MESSAGE function, the message can be accessed by invoking the UNPACK_MESSAGE procedure. For more information concerning Oracle pipes, see the Oracle7 Server Application Developer's Guide.

Command Processor

The ability to have one program executing another is powerful and quite useful in the Unix environment. It lets you incorporate operating-system commands into the programming language you are using. In C, for instance, you can either use the system library routine or make a system call to exec to perform this function. In PL/SQL you are trapped; there is no feature for escaping to a Unix shell.

To get around this limitation, I developed a stored procedure called unixcmd (see Listing 1) that makes host operating-system command requests to a host command processor written in Pro*C. A similar implementation can now be found in the Oracle7 Server Application Developer's Guide. The implementation presented here, however, is multithreaded, with the capability of passing back a standard error generated by a failed command.

The following is an example of the capability of the host command processor. Suppose you wish to send email to a specific individual each time an employee is deleted from the database. The following database trigger demonstrates how this can be accomplished (any valid Unix operating-system command could be executed here):

CREATE TRIGGER example

AFTER DELETE ON emp

FOR EACH ROW

BEGIN

unixcmd('echo "'||

ld.empno||'

was deleted from the

database."|mail ao713');

END;

/

You can invoke the unixcmd procedure from any PL/SQL block. If the command executes successfully, the execution continues with the next statement in the block. If an error occurs during processing of the command, an application error is raised. As you can see in the code for unixcmd, if no response from the command processor is received within 60 seconds, an application error will also be raised.

This facility is completely noninteractive. You should submit commands for execution without expecting any return of standard output. For instance, the "ls" command will execute successfully, but no data is displayed back to the terminal. Of course, you can redirect within the command the results of the "ls" command to any file in order to capture the results.

Unixcmd first makes a call to the DBMS_PIPE.UNIQUE_

SESSION_NAME function to obtain a unique name for the pipe on which it is to listen for a response after it issues the send. The return pipe name and the Unix command to be issued are packaged into the local message buffer and then sent on the HOST_COMMAND pipe. The response from the host command server that is received on the return pipe consists of either a SUCCESS message or a standard error from the Unix shell.

The DBMS_COMMAND_SERVER package contains two procedures that are used by the host command server. They are called from the Pro*C programs that comprise the server process to get the command from the HOST_COMMAND pipe and then return the result of the command (SUCCESS or a standard error). You can use the PL/SQL code shown in Listing 1 to create the unixcmd procedure and the DBMS_COMMAND_SERVER package. See the installation section later in this article to properly create the hcs schema and to grant access to other users before executing the code in Listing 1 and Listing 2.

Pro*C Code

The following two programs make up the host command server. Ensure that hcs (host command server) starts upon machine boot and runs in the background for each database instance from which unixcmd will be executed. Remember that hcs is acting as an execution agent on behalf of other Oracle sessions. Therefore, all environmental variables, file permissions, and execution paths are relative to the user hcs and not to the user issuing the command. Pay special attention to this relationship when setting up things such as access to secure commands. Execution of the program is allowed only by the user hcs or root. If hcs is started by root, the program makes a SETUID system call to run the process as hcs. I recommend using a startup script to start the server process that sets the Oracle system ID and all other environmental variables needed to execute various commands.

The hcs program connects to the database, then loops infinitely, calling GET_COMMAND from the DBMS_COMMAND_SERVER package. When a request for command execution is received, a child process is spawned that executes cem (command execution module). Cem establishes its own connection to the database and then executes the system library routine to execute the command. In this way, hcs is multithreaded - it can service multiple execution requests at the same time. A new child process is created for each request.

Within cem, standard error is read into a character string after executing the command. Any error message received from the shell is sent back on the return pipe; otherwise, a SUCCESS message is sent. At this point, the child process exits.

The implementation of the host command server assumes that there is a Unix user named hcs as well as an Oracle user named hcs. The Pro*C code presented in Listing 3 is the implementation of the host command server and the command execution module.

Installation

The following briefly outlines the steps for installing the command processor on your Oracle database server. These instructions assume that you have a working knowledge of both Unix and Oracle.

1. Create a Unix account for the user hcs.

2. Create an Oracle user named hcs.

3. Make the necessary grants and synonym as follows:

GRANT CREATE SESSION TO hcs;

GRANT CREATE PROCEDURE TO hcs;

CREATE PUBLIC SYNONYM UNIXCMD FOR hcs.unixcmd;复制代码

4. Connect to Oracle within SQL*Plus as hcs and run the scripts shown previously to create unixcmd and DBMS_COMMAND_SERVER.

5. Compile hcs.pc and cem.pc using the Pro*C make file on your system. The command: make -f proc.mk EXE=hcs OBJS=hcs.o worked just fine on DEC Unix and Oracle7 version 7.1.4 after I changed userid=/ and set sqlcheck=semantics in proc.mk.

6. Create a start_hcs script to suit your environment. Hcs can accept a username and password on the command line as: hcs hcs/. Be sure to put the process in the background in the script.

7. Run start_hcs.

8. Any Oracle user that has been granted EXECUTE privileges on unixcmd can now execute operating-system commands within any PL/SQL block using unixcmd('command') .

Adaptations

The host command processor I present here is a rather simple implementation demonstrating the use of Oracle pipes. You can make several adaptations and enhancements to better suit your specific environment. These enhancements include a modification to allow display of standard output, as well as a modification to reduce the number of Oracle connections by sending requests to multiple persistent copies of cem, which would improve performance.

Terminal I/O

I seem to be constantly writing little utility scripts to automate simple tasks. I have a myriad of shell scripts and SQL scripts that perform a variety of functions. In many of these scripts, I either display something to the screen or prompt the user for some kind of input. But during one of my script-writing binges, when I first seized upon the opportunity to employ the processing of PL/SQL, I was shocked to find that I could do neither. With Oracle7 I could take advantage of the DBMS_OUTPUT procedure to perform some screen output, but I was still unable to print a line of text during each iteration of a loop, for instance, or to prompt a user for input.

Listing 4 shows some terminal I/O utilities I created. In this example, each time an employee row is fetched, the employee number and current salary are displayed on the screen. The user of the script is then prompted to enter a new salary, and the salary of the employee is updated with the new entered value.

Once again, the three new commands (print, gets, and ioexit) shown in Listing 3 make use of Oracle pipes to perform the specified function. These commands are intended to be used primarily within anonymous PL/SQL blocks. The print command accepts character data to be displayed on the terminal. Although it performs tasks similar to the DBMS_OUTPUT package, it is much simpler to use. A "newline" character is printed whenever a line of text ends with "\n", as in the C programming language. The gets command (again, like C) takes terminal input up to the first newline character and returns the value in the supplied variable argument. Finally, the ioexit command terminates the terminal I/O process.

The trick to using these commands is to have SQL*Plus execute the PL/SQL in the background while running the termio process in the foreground. Requests for terminal I/O are then made from the PL/SQL program to the termio process in a client/server fashion through Oracle pipes using the above commands. The appearance is a single interactive process.

A Unix csh script named iplsql (for interactive PL/SQL) starts up the two processes as follows:

#! /bin/csh -f

if ($#argv != 2) then

echo "Usage: iplsql [username/password]

[SQL filename]"

exit 1

endif

sqlplus -s $argv[1] @$argv[2] &

termio

Assuming the name of the sample PL/SQL block to be update_sal.sql, the program could be executed as:

iplsql scott/tiger update_sal.

I offer one caveat here: The pipe on which the termio process listens is not dynamically named, and thus multiple sessions within the same instance will be reading and writing to the same pipe. This conflict could result in misdirected terminal I/O. The simple implementation offered here should be used by one user at a time. You could construct a more robust implementation of the processor by dynamically naming the pipe on which requests for I/O are made.

The PL/SQL code shown in Listing 5 is used to create the database procedures callable from PL/SQL and to create the termio package that the Pro*C termio process uses to accept requests for I/O and make responses. You can create the procedures and package in any schema that you wish. Any user wishing to execute the procedures will need to be granted execute privileges on each. Optionally, you could grant EXECUTE to PUBLIC on each procedure.

Pro*C Code

The Pro*C code in Listing 6 implements the terminal I/O foreground process, termio. This process receives the requests for I/O and performs either a screen output or keyboard input.

The Possibilities Are Endless

Communicating with Oracle pipes gives greater flexibility to PL/SQL. When you use Oracle pipes in conjunction with Pro*C, you can exploit 3GL capabilities. And although I have only shown two separate uses here, you can employ many other uses of pipes to provide various systems solutions.