I think you've pinpointed it. I was NOT using the same user/pw in the perl script. I went into SQLPlus and tried it through the same user I was using in the perl script and it failed for the same reason. However, I granted EXECUTE on the stored function to that user and it still fails (same error message). Something beyond execute seems to be necessary.
Hmmmmm.... -----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 !
