Actually, I do a "alter session set current_schema = ..." immediately after I do the DBI-> connect. Your suggestion fixed my problem of not being able to get at the function via SQLPLUS, but, because I was already changing the current_schema in the Perl script, the problem remains there (even if I prefix the function name with it's schema owner).
As an experiment, I tried a query which uses SUBSTR (some sort of system function I suspect) and it worked OK in the perl script. Hmmmm.... -----Original Message----- From: Steve Baldwin [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 2:13 PM To: Gauthier, Dave; [EMAIL PROTECTED] Subject: RE: Using stored PL/SQL function Try either creating a synonym to the function (either public or private), or qualifying the select to be something like this ... select schema_owning_function.myplsqlfunc(param1, param2) from dual EXECUTE *is* the only privilege you need to execute the function, but unless you qualify it with a schema name, Oracle will attempt to find the object (in this case the function) in the current schema. Steve -----Original Message----- From: Gauthier, Dave [mailto:[EMAIL PROTECTED] Sent: Wednesday, 27 August 2003 12:59 PM To: Steve Baldwin; [EMAIL PROTECTED] Subject: RE: Using stored PL/SQL function 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 !
