DBMS_PIPE Demo from "Dan's Oracle7 Guide"

From: Daniel B. Bikle <dbikle_at_cco.caltech.edu>
Date: 28 Aug 1993 19:11:39 GMT
Message-ID: <25oalbINNah_at_gap.caltech.edu>

The following discussion is an excerpt from "Dan's Oracle7 Guide".

While the guide could not be described as a great literary work, it might prove useful to those DBA's and Application Developers who work with Oracle7.

The guide will be finished sometime in October. If you want a copy, I'll put you on my mailing list.

All of the trademarks mentioned in this excerpt are owned by their respective owners.

-Dan

---

Daniel B. Bikle
dbikle_at_alumni.caltech.edu
415/854-9542

---

Now we switch mental gears and think about how the database can interact with the OS on behalf of the user. For example, SQL*Forms allows you to put a syntax construct known as #HOST in a forms trigger to run shell script. So the question is simple, "Can we write a procedure which executes shell commands on the database server?" The answer is, "No."

We are not, however, totally helpless. Oracle has provided a package named DBMS_PIPE which allows us to send text messages between Oracle user sessions. So we can set up a situation where one user session is a sql script running on the server. Another user session could be a sql script running on a client machine. The client script could execute a set of DBMS_PIPE procedures which send the following text string to the server script, "echo Warheads Launched | wall".

The server script could receive the string and supply it as an argument to a sqlplus HOST command which would then send the string to a UNIX shell for execution.

 sqlplus usr/pwd _at_pipe.sql # perhaps the world's simplist dbms_pipe demo

 sqlplus usr/pwd _at_pipesnd.sql # this sends a shell command to a pipe

 sqlplus usr/pwd _at_pipercv.sql	# this receives a shell command from the


				# pipe and forwards it to a shell for


				# execution



 sqlplus usr/pwd _at_pipe2.sh	# this demonstrates that pipesnd.sql and


				# pipercv.sql working together can execute


				# a shell command on the server

---

set echo on
spool pipe

rem pipe.sql

rem very simple demo of using the dbms_pipe package

set serveroutput on
DECLARE

 apipe	char(222);
 s	char(222);	/* msg to be sent*/
 t	char(222);	/* msg to be received*/
 rv	integer;	/* return value of send_message() fnc*/

BEGIN
 dbms_output.enable (9000);
 dbms_output.put_line('Output enabled');  s := 'hi dan';
 apipe := 'p1';
 dbms_pipe.pack_message(s);
 rv := dbms_pipe.send_message (apipe);
 rv := dbms_pipe.receive_message (apipe);  dbms_pipe.unpack_message(t);
 dbms_output.put_line(t);
END;
/

exit

---

set echo on
spool pipesnd

rem pipesnd.sql

set serveroutput on
DECLARE

 apipe		char(222);
 shllcmd	char(222);	/* msg to be sent*/
 rv		integer;	/* return value of send_message() fnc*/

BEGIN
 dbms_output.enable (9000);
 shllcmd := 'echo msg received on `date` > /tmp/msg.$$';  dbms_pipe.pack_message(shllcmd);
 apipe := 'p1';
 rv := dbms_pipe.send_message (apipe);
END;
/

rem end of pipesnd.sql
exit

---

set echo on

rem pipercv.sql

set serveroutput on

set echo off

rem this file will receive shell command spool /tmp/pipercv.lst

rem use the pl/sql block below to output the shell command rem sent via dbms_pipe into the file spooled above

DECLARE

 apipe	char(222);
 msg	char(222);	/* msg to be received*/
 rv	integer;	/* return value of rcv_message() fnc*/

BEGIN
 dbms_output.enable (9000);
 apipe := 'p1';
 rv := dbms_pipe.receive_message (apipe);  dbms_pipe.unpack_message(msg);
 dbms_output.put_line('host '||msg); /* put 'host ' at beginning of msg*/ END;
/

spool off

set echo on

rem transform spooled output into sql script rem with a host command in it

host grep host /tmp/pipercv.lst > /tmp/host.sh

rem run the script now as a sql script with a host command in it spool /tmp/host.lst

_at_/tmp/host.sh

rem delete files not needed anymore
host rm /tmp/pipercv.lst /tmp/host.sh

rem end of pipercv.sql

spool off

exit

---

#! /bin/sh

# pipe2.sh

# This is a simple demo of using dbms_pipe to communicate with UNIX
# Run this demo on the server machine
# This script assumes that pipesnd.sql and pipercv.sql reside
# in the home dir of the person running this demo
# If they don't, add appropriate cd commands
# Also this script assumes the client machine is named ava2

# if you want a demo of both sql scripts running on the server, # un comment the next line and comment the rsh line

## sqlplus / _at_pipesnd.sql

# create shell command over at client machine
# assume default shell is c shell
# be aware that rsh uses the login env variables setup by .cshrc
# NOT the env variables of this shell !

rsh ava2 'setenv TWO_TASK ava2_x7; sqlplus / _at_pipesnd.sql'

# receive shell command here at server machine and run it sqlplus / _at_pipercv.sql

# to find out if it worked, look at the shell command specified
# in pipesnd.sql and verify it worked by looking for a file
# named /tmp/msg.$$ with a message in it.

# that's about it
exit

---

Received on Sat Aug 28 1993 - 21:11:39 CEST