Re: perl script and Oracle

From: <holgdj_at_my-dejanews.com>
Date: Wed, 05 May 1999 22:14:37 GMT
Message-ID: <7gqfs9$cpg$1@nnrp1.deja.com>

In article <7g6tmm$g6n$1_at_sloth.swcp.com>,   elufker_at_swcp.com (Ed Lufker) wrote:
> Hi all:
>
> Can perl script run or call an oracle procedure and also pass
> parameters to the procedure. Any examples of code would be great.
>
> thanks for any help here
> eddie lufker
>
>

how about this???

since perl executes OS commands via ` ... `,

given:

cat p2o0.sql - perl 2 oracle

spool p2o0
select username from dba_users where username like '&1%'; spool off
exit

cat p2o0.pl

#!/usr/local/bin/perl -w
#

$x=`/bin/ksh export ORACLE_BASE=/oracle`;
$x=`/bin/ksh export ORACLE_HOME=/oracle/product/7.3.3`;
$x=`/bin/ksh export ORACLE_SID=dojv`;
$x=`/bin/ksh PATH=\$PATH:\$ORACLE_HOME/bin`;
$x=`sqlplus u001/pass \@p2o0 $ARGV[0]`;

open (OLD,'p2o0.lst');
while (<OLD>) {

   print;
}
close(OLD);

now from unix run:

unix >p2o0.pl M

results in:

/usr/accts/oracle >p2o0.pl M
old 1: select username from dba_users where username like '&1%' new 1: select username from dba_users where username like 'M%'

USERNAME

---

MANDADIM
MARGARETB
MARIAA
MARIAP
MARTYLA
MARYB
MCDANIEL
MEGAN_USR
MOORE
MRBACKUP
MRDISCOVER 11 rows selected.

/usr/accts/oracle >

this is executing a script, but it could just as easily execute a procedure. Hope this helps!

PS: see http://www.holg.com/oracle/monitor for an original use of Perl and Oracle.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed May 05 1999 - 17:14:37 CDT