I got it to work. Hurray !!! Real stupid oversight on my part having to do with what instance & schema I was using vs what I was setting current_schema to. Probably wouldn't have discovered the problem without all of your help though.
Thanks again -dave -----Original Message----- From: Andy Hassall [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 2:36 PM To: Gauthier, Dave; [EMAIL PROTECTED] Subject: Re: Using stored PL/SQL function Gauthier, Dave wrote: > 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; You're calling the function myplsqlfunc with two parameters, both of which are identifiers, as they're unquoted. You're selecting from DUAL, in this case. DUAL has one column, DUMMY. It does not have columns param1 or param2. Also this cannot be the code you're actually using since there's a quote missing before the semicolon. > $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. Therefore, you get invalid column name. Do you really mean to send the literal strings 'param1' and 'param2' to the function instead? $qrystr = "select myplsqlfunc('param1', 'param2') from dual"; Or set up bind variables, i.e. ?, or :param1 and :param2 $qrystr = "select myplsqlfunc(?, ?) from dual"; $qrystr = "select myplsqlfunc(:param1, :param2) from dual"; You'll have to bind values to them either with $sth->bind_param or in the $sth->execute call. > 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). Can you copy and paste an example of it working from sqlplus? [EMAIL PROTECTED]:~$ sqlplus test/[EMAIL PROTECTED] SQL*Plus: Release 9.2.0.4.0 - Production on Wed Aug 27 19:25:50 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> create or replace function mysqlfunc(param1 in number, param2 in number) return number as 2 begin 3 return 42; 4 end; 5 / Function created. SQL> select mysqlfunc(param1, param2) from dual; select mysqlfunc(param1, param2) from dual * ERROR at line 1: ORA-00904: "PARAM2": invalid identifier SQL> select mysqlfunc(1, 2) from dual; MYSQLFUNC(1,2) -------------- 42 [EMAIL PROTECTED]:~$ cat test.pl #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect('dbi:Oracle:dev92lnx', 'test', 'test', { RaiseError => 1, AutoCommit => 0, PrintError => 1 }) or die DBI::errstr; my $sth = $dbh->prepare('select mysqlfunc(param1, param2) from dual'); $sth->execute; DBI::dump_results($sth); $dbh->disconnect; [EMAIL PROTECTED]:~$ ./test.pl DBD::Oracle::db prepare failed: ORA-00904: "PARAM2": invalid identifier (DBD ERROR: OCIStmtExecute/Describe) [for statement ``select mysqlfunc(param1, param2) from dual'']) at ./test.pl line 11. DBD::Oracle::db prepare failed: ORA-00904: "PARAM2": invalid identifier (DBD ERROR: OCIStmtExecute/Describe) [for statement ``select mysqlfunc(param1, param2) from dual'']) at ./test.pl line 11. Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). This is on 9.2, so that accounts for it being 'invalid identifier' rather than 'invalid column name', as you're on 8i. It's still ORA-00904, they've just made the description more general. Change the prepare line to: my $sth = $dbh->prepare('select mysqlfunc(1,2) from dual'); And it executes and outputs: [EMAIL PROTECTED]:~$ ./test.pl '42' 1 rows > Has anyone been able to get this sirt of thing to work? Calling functions? Yes. There's also a load of examples in the DBD::Oracle docs: http://theoryx5.uwinnipeg.ca/CPAN/data/DBD-Oracle/Oracle.html#PL%2fSQL_Examples -- Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk) Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
