We will start with the 'server' script. This part of the application is responsible for creating the private pipe on which we will listen for requests

Страницы работы

5 страниц (Word-файл)

Фрагмент текста работы

DBMS_PIPE

As promised on page 1041 of the book, here is an example of a small pipe server, which answers the frequently asked question, 'How can I run a host command from PL/SQL?' With the addition of Java to the database and C external procedures, we could easily implement a host command function with either technology. However, what if we do not have access to a C compiler or we don't have the Java component of the database available – what then? The following shows how we could very simply set up a small pipe server that can do host commands using nothing more than SQL*PLUS and the csh scripting language. It is fairly simple, consisting of only a few lines of csh and even fewer of PL/SQL. It does show much of the power of database pipes though, and should give you some ideas for other interesting implementations.

We will start with the 'server' script. This part of the application is responsible for creating the private pipe on which we will listen for requests. We will usually use a private pipe here to prevent other malicious sessions from reading or writing on our pipe. This server stub will be responsible for reading the request from the pipe, processing the request, and writing the answer (the output of the host command) back to the client. The pseudo-code for this server is:

If passed a pipe name (every time except startup) then     write answer back to client on the pipe

end if

wait for request to be written on pipe

when request received, create a csh script to run the command,     always make the last line of this csh script rerun THIS     script, passing the name of the response pipe

exec the tmp script we just generated

The host.csh script (it must be named host.csh) for this will be:

#!/bin/csh -f

#

Chapter number

# If the first argument is supplied, then this is the

# name of the pipe on which to write the answer back to the client.

# The answer they are waiting for is in the file

# $$.dat - $$ will be the pid (process id) of our csh # script. We use EXEC to ensure the pid stays the same

# from call to call.

if ( "$1" != "" ) then

    #

    # If we get here, we will get the 'head' or beginning

    # of our result. We'll send just enough back so the client

    # can get the gist of our success or failure.

    #

set x="`head $$.dat | sed s/\'/\'\'/g`"

    #

    # We are going to use SQL*PLUS only to interact with

    # the database. Here, we will feed SQL*PLUS a script

    # inline. We want the $x and $1 to be 'replaced' with

    # the corresponding environment variables, so we redirect

    # in from EOF. Later, we don't want this replacement     # so we use 'EOF'.

    #

    # In this block of code, we simply write out the result

    # and send it on the pipe the client asked us to. The

    # pipe name is carried along as a parameter to this script.

    #

    # Note how to use CURSOR_SHARING=FORCE to make use of

    # bind variables. Since cursor sharing does not happen inside

    # of PL/SQL, we use a temporary table and insert our 'parameters'

    # into the temp table. PL/SQL reads those values out. This ensures

    # we do not overload our shared pool with all of the

    # character string literals we would otherwise. You need to have issued

    # create global temporary table host_tmp(msg varchar2(4000), pipe

    # varchar2(255) ); prior to executing this script.

    #

SQL*PLUS / <<EOF     alter session set cursor_sharing=force;

insert into host_tmp (msg,pipe) values ( '$x','$1' );     declare         status      number;

l_msg       varchar2(4000);         l_pipe      varchar2(255);     begin

select msg, pipe into l_msg, l_pipe from host_tmp;         dbms_pipe.pack_message( l_msg );         status := dbms_pipe.send_message( l_pipe);

if ( status <> 0 )         then           raise_application_error( -20001, 'Pipe error' );

end if;     end;

/

EOF

endif

2

DBMS_PIPE

#

# Now we go into 'server' mode. We will run and wait

# for something to be written on the pipe to us. We

# start by creating a private pipe. If one already exists,

# this does nothing. If one does not exist, the 'server' creates

# a private pipe for us.

#

# Then, we block indefinitely in received message, waiting for

# a request.

#

# Then we get the components of the message. Ours has 2 pieces:

# – the pipe to write back on

# - the command to execute

# We simply echo this out using dbms_output. If we receive

# any sort of timeout or error, we write out 'exit' which will

# cause us to exit.

#

# The command we run, SQL*PLUS / .... > tmp.csh is set up to do the

# following:

# SQL*PLUS /  -> run SQL*PLUS

# <<"EOF"     -> get the input inline, 'EOF' means don't do shell expansions

# | grep '^#' -> we'll keep only lines that begin with #

# | sed 's/^.//' -> get rid of that # in the real script

# > tmp.csh -> put that output from SQL*PLUS into tmp.csh

#

SQL*PLUS / <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh

set serveroutput on

whenever sqlerror exit

declare

status      number;

answer_pipe varchar2(255);         command     varchar2(255); begin

status := dbms_pipe.create_pipe( pipename => 'HOST_PIPE',                                          private  => TRUE );

status := dbms_pipe.receive_message( 'HOST_PIPE');         if ( status <> 0 ) then                 dbms_output.put_line( '#exit' );

else                 dbms_pipe.unpack_message( answer_pipe );                 dbms_pipe.unpack_message( command );

dbms_output.put_line( '##!/bin/csh -f' );                 dbms_output.put_line( '#' || command );                 dbms_output.put_line( '#exec host.csh ' || answer_pipe );

end if; end; /

spool off

"EOF"

#

# Lastly, we make the script we just created executable

Похожие материалы

Информация о работе