How to use a user created function in n perl DBD::Oracle

Question

I have created a function in oracle 11g r2, I would like to call that function while inserting a record.

Following is my code block.

my $SQL="insert into sample values ( :sno, :amount , :func )";
my $sth =$dbh-> prepare($SQL);
$sth->bind_param(":amount" , $amount );
$sth->bind_param(":sno" , $i);

$sth->bind_param(":func" , my_func($amount)); 
$sth-> execute();
# I want the third parameter to be output of the function my_func.

Are you just missing the dollar sign before sth? `sth->bind_param(":func" , my_func($amount));` — Miller, Apr 25 '14 at 05:41
No way .. That line is incomplete am asking for suggestions. Anyways thanks — Nishanth Lawrence Reginold, Apr 25 '14 at 05:43

score 3 · Accepted Answer · answered Apr 25 '14 at 07:15

3

my $SQL = "insert into sample values (?, ?, my_func(?))";
my $sth = $dbh->prepare($SQL);

$sth->execute($i, $amount, $amount);

answered Apr 25 '14 at 07:15

mpapec

50,217
8
67
127

I need to call the function my_func created by me in Oracle. – Nishanth Lawrence Reginold Apr 25 '14 at 07:40
Could you please explain. That function is not a perl sub routine but a function created by me in oracle. – Nishanth Lawrence Reginold Apr 25 '14 at 08:32
1

@NishanthLawrence `my_func` in `"insert into sample values (?, ?, my_func(?))"` is your plsql stored function. – mpapec Apr 25 '14 at 08:40
I am using bind for one xmltype field so I cant mix the placeholder types here. – Nishanth Lawrence Reginold Apr 25 '14 at 09:07

How to use a user created function in n perl DBD::Oracle

1 Answers1