Re: external procedure and gcc (newbie)

From: Matt <matthew.ramadanovic_at_yale.edu>
Date: 28 Mar 2005 22:23:03 -0800
Message-ID: <1112077383.243404.209270@l41g2000cwc.googlegroups.com>

For those who need help and get (as I did) nothing but silence. I got it to work using Forte C Workshop 6. I couldn't get gcc to work with this at all.

First I defined the relevent variables :

LD_LIBRARY_PATH=/export/home/oracle/product/9.2.0/lib:/export/home/oracle/product/9.2.0/lib32:/export/home/oracle/product/9.2.0/ctx/lib export LD_LIBRARY_PATH
ORACLE_BASE=/export/home/oracle
export ORACLE_BASE
ORACLE_HOME=/export/home/oracle/product/9.2.0 export ORACLE_HOME
ORACLE_SID=YOURSID
export ORACLE_SID

I found that it was really picky about the version of ld I was using.

Next I wrote my external procedure (just executes a command) :

shell_cmd.c :

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

int main ()

{

      shell_cmd("/pathtoyourcommand");
      return 1;

}
shell_cmd( char* cmd)
{

system(cmd);
return 1;
}

Then I compiled it (assuming your compiler likes the demo_rdbms.mk file). Something was weird about compiling this thing and I would get errors unless I did it in this exact order.

   cc shell_cmd.c
   cc -c shell_cmd.c
   cc shell_cmd.c -o shell_cmd
   make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_callback SHARED_LIBNAME=shell_cmdlib.so OBJS=shell_cmd.o

Now I stuck the .so file in /export/home/oracle/product/9.2.0/lib :

cp shell_cmd* /export/home/oracle/product/9.2.0/lib

Next I used Toad, SQLEditor or SQL*Minus to register my procedure with the data base (I pasted the lines in one at a time rather than trying to execute them all at once or your probably get errors) :

DROP LIBRARY SHELL_CMDLIB;
DROP PROCEDURE shell_cmd;
DROP PUBLIC synonym shell_cmd;

CREATE OR REPLACE LIBRARY SHELL_CMDLIB IS '/export/home/oracle/product/9.2.0/lib/shell_cmdlib.so'; CREATE PROCEDURE shell_cmd (

           p_cmd IN VARCHAR2)
           AS EXTERNAL
           LIBRARY shell_cmdlib
           NAME "shell_cmd"
           PARAMETERS (p_cmd STRING);

create OR REPLACE public synonym shell_cmd for system.shell_cmd; grant execute on system.shell_cmd to public;

I was at long last able to execute the new external procedure with some PL/SQL like so :

DECLARE
cmd varchar2(200);
BEGIN

       cmd := '/pathtowhatever.pl';
       execute_cmd(cmd);

END; Next I went back to programming in a sane language like PERL. Note, if you are calling this code from PERL using DBI, drop the last ";" before your $dbh->prepare( or it will fail. Other than that, most of the PL/SQL I've tried calling via DBI work great.

Hope this helps somebody,

-Matt

Matt wrote:
> Folks,
> Has anyone ever sucessfully compiled an external procedure for
Oracle
> 9i on Solaris using gcc? I attempted to follow the documentation but
> have not been sucessful. My hunch is that it is the compiler but I'd
> like to find out for sure if I really need a $3000 compiler to
compile
> a (4 line) stored procedure. My main support for this rather lame
> conclusion comes from the fact that neither my program nor the demos
> will compile properly using the demo_rdbms.mk makefile. Please, say
it
> ain't so!

> -Matt

> If you are interested, here is what I am doing and the errors I am
> getting. I upgraded gcc from 2.9x to 3.4.3 but have the same errors.

> The stored procedure in question compiles when I do this :

>


> 	gcc execute_cmd.c -o execute_cmd.o

>

> Once compiled "execute_cmd.o xxx" outputs "Your argument was xxx"

> If I compile it the way they mention in this tutorial (and the Oracle
> documentation) :

>


> 	http://home.clara.net/dwotton/dba/oracle_extn_rtn3.htm

>

> The executable no longer runs from the commandline :

>


> 	gcc -c execute_cmd.c

>         ./execute_cmd.o

> 	chmod 777 ./execute_cmd.o

> 	Cannot execute binary file

>

> Here is execute_cmd.c

>


> 	#include <stdio.h>

> 	#include <string.h>

> 	int main (int argc, char *argv[])

> 	{

> 	     if (argc == 2) {

> 	       printf("Your argument was %s\n", argv[1]);

> 	     }

> 	}

> 	void execute_cmd( char* cmd )

> 	{

> 	       system(cmd);

> 	}

>

> Ignoring this bad omen I attempte to keep going. According to the
doco,
> you have to use the demo_rdbms.mk makefile to compile the external
> procedure. I set LD_LIBRARY_PATH explicitly to avoid "wrong ELF
class:
> ELFCLASS64" errors and then compile.

>


> 	LD_LIBRARY_PATH=/export/home/oracle/lib32:$LD_LIBRARY_PATH

> 	export LD_LIBRARY_PATH

> 	gcc -c execute_cmd.c

> 	make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_callback

> SHARED_LIBNAME=execute_cmdlib.so OBJS=execute_cmd.o

>
>

> I get this error from the make :

>


> 	/usr/ccs/bin/ld -G -z text -L/export/home/oracle/product/9.2.0/lib

> -R/export/home/oracle/product/9.2.0/lib -o execute_cmdlib.so
> execute_cmd.o /export/home/oracle/product/9.2.0/lib/libpls9.a
-lclntsh
> `cat /export/home/oracle/product/9.2.0/lib/sysliblist`
> -R/export/home/oracle/product/9.2.0/lib -laio -lposix4 -lkstat -lm
> -lthread
> Text relocation remains referenced
> against symbol offset in file
> <unknown> 0x28 execute_cmd.o
> <unknown> 0x2c execute_cmd.o
> printf 0x34 execute_cmd.o
> system 0x54 execute_cmd.o
> ld: fatal: relocations remain against allocatable but non-writable
> sections
> *** Error code 1
> make: Fatal error: Command failed for target `extproc_callback'

>
>

> This error goes away if I compile with this commandline :

>


> 	gcc -c -fPIC execute_cmd.c

>

> If I create the relevant stored procedures grants and stuff in oracle
> it doesn't (not surprisingly) execute. Instead attempts
> to use the external procedure like so

>


> 	    DECLARE

> 	       cmd varchar2(200);

> 	    BEGIN

> 	       cmd := '/bin/touch /tmp/yyy';

> 	       execute_cmd(cmd);

> 	    END;

>

> Yeilds oracle erros :

>


> 	ORA-28595: Extproc agent : Invalid DLL Path

> 	ORA-06512: at "SYSTEM.EXECUTE_CMD", line 0

> 	ORA-06512: at line 9

Received on Tue Mar 29 2005 - 00:23:03 CST

Re: external procedure and gcc (newbie) - Self Solved