Execute an Oracle Function (not a Procedure)

[Ken Gaul]

Generally a function returns a value which you must assign to something. So
you could do
my $sql = qq[select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual];

instead of trying to execute an anonymous PLSQL block, which is probably
more efficient anyway.

If you need to do it in PLSQL because of pragma restrictions etc then you
would have to

my $sql = qq[BEGIN
:outval := OWNER.MY_FUNCTION(:in_id,$action,:in_name,$meta_type);
END;]

then also do bind_param_inout(":outval", \$outval.....)
and bin_param_in(":in_id".... etc for the other parameters.

I believe it is all in the DBD::Oracle perldoc.

Ken.

-----Original Message-----
From: NYIMI Jose (BMB) [mailto:Jose....@proximus.net]
Sent: 18 November 2002 14:33
To: dbi-...@perl.org
Subject: Execute an Oracle Function (not a Procedure)

Hello,

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);
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.

Thanks in advance for your help.

José.

**** DISCLAIMER ****

"This e-mail and any attachment thereto may contain information which is
confidential and/or protected by intellectual property rights and are
intended for the sole use of the recipient(s) named above.
Any use of the information contained herein (including, but not limited to,
total or partial reproduction, communication or distribution in any form) by
other persons than the designated recipient(s) is prohibited.
If you have received this e-mail in error, please notify the sender either
by telephone or by e-mail and delete the material from any computer".

Thank you for your cooperation.

For further information about Proximus mobile phone services please see our
website at http://www.proximus.be or refer to any Proximus agent.

[Michael A Chase]

On Mon, 18 Nov 2002 15:32:52 +0100 "NYIMI Jose (BMB)" <Jose....@proximus.net> 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.

[Jeff Seger]

Maybe I am missing something herre (It is Monday after all) but
shouldn't a function be called in a select statement:

"select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual"

or something similar?

[Michael A Chase]

On 18 Nov 2002 11:31:56 -0500 Jeff Seger <jeffre...@fairchildsemi.com> wrote:

> Maybe I am missing something herre (It is Monday after all) but
> shouldn't a function be called in a select statement:
>
> "select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual"
>
> or something similar?
>
> On Mon, 2002-11-18 at 10:43, Michael A Chase wrote:

>> 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 .

Look at the function examples in the URIs I cited. PL/SQL functions can
often be used in a SELECT, but not always. Unless you are getting
arguments for the function from a table, there is no advantage to using a
SELECT instead of an anonymous block and there may be some additional
overhead.

[Steve Baldwin]

Actually, because José mentioned he was doing an insert in his function,
it cannot be called from SQL, but only from an anonymous PL/SQL block.
In any instance, it is *always* faster to execute a PL/SQL function from
an anonymous PL/SQL block than from a SELECT.

In terms of the throughput you are seeing (8000 rows in 7 minutes), that
does seem slow, but it could be one of many things. Are there any
triggers on the table(s) you are inserting into ? Have you tried
tracing the session, and running the trace file through tkprof ? If
you're not sure how to do this, in your Perl code, you need to execute
'alter session set sql_trace = true'. You then run your script, and ask
your DBA where trace files end up. It will be wherever the Oracle
parameter USER_DUMP_DEST is pointing to. Note that this is on the
database server machine which may not be the same as your client
machine. Once you have located your trace file, you use tkprof to
format it into something useful - eg 'tkprof tracefile outfile'. If you
just type 'tkprof<return>' on the command line, you will see all the
options.

Hope this helps,

Steve