On Mon, 18 Nov 2002 15:32:52 +0100 "NYIMI Jose (BMB)" <[EMAIL PROTECTED]> wrote:
> I have a function in our Oracle database (a function, not a procedure). > I would like to execute this function from my perl script using DBI. > > I wrote something like this : > > my $sql=qq[ > BEGIN > OWNER.MY_FUNCTION(?,$action,?,$meta_type); Unless you've quote()ed $action and $meta_type, you are pasting unquoted text into the SQL. You would be better off using placeholders for them along with the ones you already have. You are not providing anything to receive the value returned by the function. You will need either a PL/SQL variable that will be discarded or a bind variable as shown in the examples in http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm and http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc.pl . This will require you to use bind_param() and bind_param_inout() since there is no provision for inout parameters in execute(). An up side to that is that you can bind $action and $meta_type outside the loop. > END; > ]; > > my $dbh=DBI->connect("dbi:Oracle:$db_name",$db_user,$db_passwd); > $dbh->{AutoCommit}=0; > $dbh->{RaiseError}=1; > > my $sth=$dbh->prepare($sql); > > while(my($id,$name)=each %$data){ > $sth->execute($id,$name); > } > > $dbh->commit(); > $dbh->disconnect(); > > > But I'm getting the following error message: > > DBD::Oracle::st execute failed: ORA-06550: line 2, column 48: > PLS-00201: identifier 'MAIN' must be declared > ORA-06550: line 2, column 13: > > How can I fix it ? Any idea is welcome. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
