execution of host 3gl programs using dbms_pipe example!!

In response to many people requesting how I run HOST programs of my choice with a pl/sql command. I commented the source that I was using and posted it for any one that it interested in running host programs from pl/sql. It runs and compiles fine on my Solaris 2.4 with Oracle 7.3. Run pipels.pc in the background once you have modified it to your taste. Hope this helps...

thanks,
marcus,
marcus_at_ao.net

---------------------------cut here -------------------

/* This program was created by Marcus Williford <marcus_at_ao.net>

   One of the functions written by Oracle Corp.    (i.e. sql_error())

   This is example code from me to you, I have tested it on a X86 Solaris    machine running Oracle 7.3. It works fine for me, let me know if you    get it to work on your platform. Use at your own risk, thanks.

   It should complile using proc makefiles in oracle.    It is meant to show how to receive an oracle dbms_pipe message    in a proc program. What you do with it after you have received    the message is up to you. This example reads from a pipe that is    named 'exe', and then executes it.

   Make sure to use SQLCHECK=SEMATICS and userid=userid/passwd in proc    flags.

   also: here is an example pl/sql procedure to call this test

   create or replace procedure SEND_MESSAGE( v_msg IN VARCHAR2) IS    pipe_status INTEGER;
   BEGIN DBMS_PIPE.pack_message (v_msg);    pipe_status := DBMS_PIPE.SEND_MESSAGE ('exe');    END;    Then just type
   exec send_message('ls -lat');
   and your C host program will execute that command...

   Please think about security when using host pipes, especially if you are    listining on a public pipe. Under pl/sql release 2.2, you can explicitly    request a pipe with different settings.    like
   DBSMS_PIPE.CREATE_PIPE('exe')
   this would create a private pipe, not public.    also, running pipels as root or oracle would not be a good idea, the    system call in pipels will pass sh the mesg.arr string, executing    whatever you pass it!
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
#include <unistd.h>
#include <string.h>

VARCHAR     username[20]; 
VARCHAR     password[40];    
VARCHAR     mesg[100]; 

void sql_error();

main()
{

int pipe_status;
int i;

/* copy the username and passwd varchars */ strncpy((char *) username.arr, "userame", 20);

username.len = strlen((char *) username.arr);
strncpy((char *) password.arr, "passwd", 40);
password.len = strlen((char *) password.arr);

/* Connect with error notification */

    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");     EXEC SQL CONNECT :username IDENTIFIED BY :password;     printf("\nConnected to ORACLE as user: %s\n", username.arr);

pipe_status=0;

    do {

	EXEC SQL EXECUTE
	BEGIN
	/* receive the pipe message, this will wait until it receives one */
 	:pipe_status := DBMS_PIPE.RECEIVE_MESSAGE('exe');
	/* unpack gets it off of the mesg queue and puts it into a VARCHAR */
	DBMS_PIPE.UNPACK_MESSAGE(:mesg);
	END;
	END-EXEC;

        /* properly terminate characters */
        mesg.arr[mesg.len] = '\0';

	/* take these printf's out when you get it working right 
           they may help debug it
	*/
        printf("pipe_status = %d\n",pipe_status);
        printf("mesg = '%s",mesg.arr);
        printf("'\n");
        if(system(mesg.arr)<0) {
	printf("error executing %s",mesg.arr);
	}

/* Disconnect from ORACLE. */

    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

/* oracle sql_error code, from the sample files */

void
sql_error(msg)
char *msg;
{

    char err_msg[128];
    int buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\n%s\n", msg);
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);     printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}

/* have fun whatever you do */ Received on Sat Jun 15 1996 - 00:00:00 CEST