Re: Running perl DBI in PLSQL on UNIX

katiesoh_at_gmail.com wrote:
> I'm having trouble executing my perl DBI program in PLSQL. The perl
> program connects to an Oracle 9203 database, does some DML and DDL and
> then writes the output to STDOUT and a file.
>
> I have the following java method that I call from my PLSQL.
>
> public class execperl{
> public static execperl(String paramfile){
> Process p = Runtime.getRuntime().exec("/tmp/myperlpgm " +
> paramfile);
> }
> }
>
> I did some troubleshooting by placing print statements at various lines
> in my code and the program always stop before the OPEN statement. If I
> comment the OPEN, it exits when I do a subroutine call.
>
> The java stored procedure works fine in PLSQL if I run simple command
> like /bin/ls or a simple perl script that does some printing to STDOUT.
>

[...]

> 
> Now, back to my original question, are there any restrictions or
> limitations with what I'm doing to do here? As I stated earlier, the
> java stored procedure works fine in PLSQL if I run simple command like
> /bin/ls or a simple perl script that does some printing to STDOUT but
> it always terminates at OPEN statements and subroutine calls. There is
> something I don't understand about how Perl DBI works when it is
> invoked via PLSQL/Java. Can someone shed some light? Has anyone done
> something similar?
> 

PL/SQL, Java, and Perl are all mature, allegedly full-featured languages, and it should be possible to cajole them into playing nicely together.

However, you haven't provided nearly enough information to reproduce or test your problem.

1. what OS runs on the server? (hopefully some flavor of Unix)
2. How are you handling the input and output streams associated with the Process class?
3. How are you handling output from PL/SQL? (normally requires DBMS_OUTPUT or UTL_FILE).
4. You mention DBI, but from your description it sounds like nothing to do with DBI, rather standard IO handles versus filehandles you create. What are you using in Perl to diagnose the error (if any) returned from the open()?

-Mark Bole Received on Sat Apr 02 2005 - 18:18:14 CST