I have changed the code and
it works, now !
Thanks a lot to all of you there.
If you see some improvement
That I can do on the following code,
Fill free to advice me :).
Performance ?
I have 8000 rows to insert and the script took about 7 min.
The function is a "simple" insert statement.
Is this performance out of value you would expect ?
Thanks again for your support ...
Jos�.
= PERL CODE
sub insert_into_buffer{
my($data,$action,$meta_type)=@_;
my $this_sub=(caller(0))[3];
print localtime()." -- running $this_sub\n";
my($out_val,$id,$name,);
my $sql=qq[
BEGIN
:out_val
:=CNI.F_SET_MAXIMO_BT_EQP_HRY_NE(:in_id,:in_action,:in_name,:in_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);
$sth->bind_param_inout(":out_val",\$out_val,10);
$sth->bind_param(":in_action",$action);
$sth->bind_param(":in_meta_type",$meta_type);
while(($id,$name)=each %$data){
$sth->bind_param(":in_id",$id);
$sth->bind_param(":in_name",$name);
$sth->execute();
}
$dbh->commit();
$dbh->disconnect();
}
= ORACLE FUNCTION
[snip]
if as_metatype='CNI_FUNCTION' then
insert into cni.maximo_bt_eqp_hry
(select
as_action,
null,
'INPRG',
as_name,
null,
null,
NOP_SUB_AREA,
f_Get_network_system(class_id),
F_GETCLASSNAME(class_id),
F_GETNESITECOWCODE(ne_id),
vendor,
release,
f_getNeName(ne_id),
f_get_cell_info(id,'DIAMOND'),
f_get_cell_info(id,'CI'),
null,
f_get_role_user(id,'CNI_FUNCTION','OWNER'),
null,
null,
null,
null,
null,
user,
sysdate(),
null,
f_get_next_key()
from cni_function
where id=al_id
);
else
[snip]
> -----Original Message-----
> From: Michael A Chase [mailto:[EMAIL PROTECTED]]
> Sent: Monday, November 18, 2002 4:43 PM
> To: [EMAIL PROTECTED]; NYIMI Jose (BMB)
> Subject: Re: Execute an Oracle Function (not a Procedure)
>
>
> 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.
**** 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.
