in reply to Re^2: DBI Out parameters
in thread DBI Out parameters

Just to clarify what I meant. Functions in pl/sql only allow input arguments. You cannot write:

FUNCTION fred (arg1 OUT INTEGER) RETURN integer;

[download]

but you can write

FUNCTION fred (arg1 IN INTEGER) RETURN integer;

[download]

and the IN is implied anyway.

Also, I was not saying bind_param_inout would not work on an input only parameter but it is pointless using it and may require an unnecessary overhead on input only arguments.

Replies are listed 'Best First'.
Re^4: DBI Out parameters
by almut (Canon) on Nov 19, 2008 at 15:05 UTC
    You cannot write:
    FUNCTION fred (arg1 OUT INTEGER) RETURN integer;

    Yes you can... (and if you do use such output parameters, bind_param_inout is the way to bind/access them).

[reply]
[d/l]
[select]

      You are right you can do that - I didn't know that. I use functions mostly in SQL and procedures if there is more than one parameter to return but as you say the following does work:

      use DBI;
use strict;
my $h = DBI->connect('dbi:Oracle:xxx','xxx','xxx');
eval {$h->do(q{drop function fred});};
my $sql = <<'EOT';
create function fred(a OUT integer) return integer as
begin
  a := 1;
  return (a + 1);
end;
EOT

$h->do($sql);
my $s = $h->prepare(q{begin ? := fred(?); end;});
my $in = 1;
$s->bind_param_inout(2, \$in, 100);
my $res;
$s->bind_param_inout(1, \$res, 100);
$s->execute;
print "fred=$res, a=$in\n";

      [download]

      outputs fred=2, a=1

      However, I still stand by the my comment that if the parameter is an input parameter it is probably better to bind it with bind_param and not bind_param_inout.

[reply]
[d/l]

In Section Seekers of Perl Wisdom