All the Perl that's Practical to Extract and Report

Glue in Oracle

jdavidb on 2003-05-21T20:45:13

Okay, we all know you have to glue things together. I can't seem to get a straight answer at work, so I thought I'd ask expert gluers: what's the simplest and most intelligent way to "shell out" of PL/SQL code? Note that I'm talking server-side stored PL/SQL code here; shelling out of SQL*Plus is useful but irrelevant for what I'm doing.

I highly suspect I'm not getting a straight answer because there's not one. If anyone's interested, I'll post how they're having me do it.

I thought it was one of those DBMS_* libraries

runrig on 2003-05-21T23:28:21

...but I was wrong. But there's always google. Looks like there's no good direct way, but here's the best answer I could find (summary: Java, DBMS_PIPE w/a daemon, or compile something into a shared library).

used two of those..

tinman on 2003-05-25T07:58:54

Here's what I've found, so far (havent tried any of this on 9i, so no idea if things are different)

Java: nice.. if you like coding java, this is by far the easiest method to do things.. but of course, its err... java (does my bias show yet ?), and Oracle only allows you to use a weird JVM called Aurora (not the standard JVM by Sun), so you may find some class libraries arent there.. Compatibility is with 1.1.8, I think)

Shared library: compile the C equivalent of what you want done into a shared object (or DLL in the windows world), define an external procedure in PL/SQL and you're all set. Requires a lot more setting up than the Java stored procedure route, but if your databases are installed without the jvm (mine are, by default), this is the only route left available at times..

If its generating a file or something trivial like that, you can try UTL_FILE or something PL/SQL-ish..

I havent tried the DBMS_PIPE option, so no idea how that works..