Try fully qualifying the function name. Granting Execute to public is not
the same as a public synonym.
$qrystr = "select owningschema.myplsqlfunc(param1, param2) from dual;"
____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________
"Gauthier, Dave" <[EMAIL PROTECTED]>
08/27/2003 11:51 AM
To: "Steve Baldwin" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
cc:
Subject: RE: Using stored PL/SQL function
Absolutely sure the DB connect is OI. In the example I gave, if I replace
"$qrystr = "select myplsqlfunc(param1, param2) from dual;" with "$qrystr =
"select dummy from dual;", it runs fine.
I did grant "execute" on the stored function to "public", tried it again,
still no luck.
-dave
-----Original Message-----
From: Steve Baldwin [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 27, 2003 10:43 AM
To: Gauthier, Dave; [EMAIL PROTECTED]
Subject: RE: Using stored PL/SQL function
Are you sure the Oracle schema/password you are using in your Perl
script is the same one you used to successfully execute the query. It
sounds like a permissions/privileges problem to me. What you are trying
to do is definitely possible in Perl/DBI - I do it in many places with
Oracle versions from 7.3.3 (!!!) to 9.2.0.3. It works just fine.
Steve
-----Original Message-----
From: Gauthier, Dave [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 27 August 2003 9:33 AM
To: [EMAIL PROTECTED]
Subject: Using stored PL/SQL function
Hi:
I'm trying (and failing) to use a stored PL/SQL function from a perl/DBI
script. Here's an excerpt....
$qrystr = "select myplsqlfunc(param1, param2) from dual;
$sth = $dbh->prepare($qrystr)
or die "Failed to prep statement--- $qrystr";
$sth->execute()
or die "Failed to execute statement... $qrystr";
The error I get is...
DBD::Oracle::db prepare failed: ORA-00904: invalid column name (DBD
ERROR: OCIStmtExecute/Describe) at try_stored.pl 10.
The statement works fine using the character cell SQL interface to the
Oracle DB. The statement in perl runs fine if I change the query to
access some other table/column in the DB (no function calls).
Has anyone been able to get this sirt of thing to work?
Oracle 8i release 8.1.7.0.0 served from a 64 bit alpha running Tru64.
Query from a Linux client, through Oracle OCI, using perl
($PERL_VERSION doesn't seem to be set, so I can't tell you about that)
Thanks !
