DBI Out parameters

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have the following Perl script which basically should take a string, run a plsql function using that string and return an ID number. I would like to pass the string as a bind variable into the plsql and retrieve the ID number as another out bind variable.

use DBI;
my $dbh;
my $sth;
my $plsql;
my $id=0;
my $l_chime = qw(HZgrDAwAPB5QvcatrY);

connect_db();


eval{
$plsql = $dbh->prepare(q{
            BEGIN 
                :id := Pkg.search_string(:l_chime); 
            END;
            }); 


$plsql->bind_param_inout(":id",\$id,16);
$plsql->bind_param_inout(":l_chime",\$l_chime,4000);
$plsql->execute;
$plsql->finish;
};
print "returned: id: $id \n";
[download]

$id always returns null and i get a 'use of unitialised variable in string or concatenation (.)' error. I followed the steps in this perlmonks post, but still no luck.

You will see above that i have used bind_param_inout for id but i have tried bind_param, and it does the same thing.

Any ideas would be appeciated.

Thanks.

Joe.

Comment on DBI Out parameters Download Code

Replies are listed 'Best First'.
Re: DBI Out parameters by mje (Curate) on Nov 19, 2008 at 13:46 UTC
Functions in pl/sql only take input arguments so your second bound parameter should be a simple bind_param call and if the function argument is something special (e.g., a cursor which it does no look like here) you may need an extra arg to bind_param to specify the type - see the pod. What does your function return? A value of undef in '$id' is perfectly possible if you function returns NULL.	[reply]
Re^2: DBI Out parameters by almut (Canon) on Nov 19, 2008 at 14:20 UTC
Functions in pl/sql only take input arguments Don't think that's true. At least, I've been using `bind_param_inout()` with pl/sql function arguments (like the `:l_chime` in the OP) in quite a number of cases without any problems...	[reply] [d/l] [select]
Re^3: DBI Out parameters by mje (Curate) on Nov 19, 2008 at 14:43 UTC
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.	[reply] [d/l] [select]
Re^4: DBI Out parameters by almut (Canon) on Nov 19, 2008 at 15:05 UTC
Re^5: DBI Out parameters by mje (Curate) on Nov 19, 2008 at 16:12 UTC
Re^2: DBI Out parameters by Anonymous Monk on Nov 19, 2008 at 14:29 UTC
Hi, i have tried using bind_param for my second parameter. Still no luck. I have come to the conclusion that maybe one cant get at the variable on the left side of :=? For example, if i got my designer to write the function as `create procedure search(chime in varchar, id out number)` [download] then i could use bind_param_inout to get at the id? For example: `$id = $plsql->bind_param_inout(":id",\$id,16);<br> print "$id";<p>` [download] Am i correct in thinking that? Thanks	[reply] [d/l] [select]
Re^3: DBI Out parameters by almut (Canon) on Nov 19, 2008 at 14:37 UTC
maybe one cant get at the variable on the left side of :=? It should work (I've done it many times myself...), so I think the problem lies elsewhere.	[reply]
Re^3: DBI Out parameters by mje (Curate) on Nov 19, 2008 at 14:52 UTC
You certainly can get function return values back in perl. You can also get output arguments back in procedures. I do this all the time with DBD::Oracle. `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 integer) return integer as begin return (a + 1); end; EOT $h->do($sql); my $s = $h->prepare(q{begin ? := fred(?); end;}); $s->bind_param(2, 1); my $res; $s->bind_param_inout(1, \$res, 100); $s->execute; print "fred=$res\n";` [download] outputs fred=2	[reply] [d/l]
Re: DBI Out parameters by almut (Canon) on Nov 19, 2008 at 12:27 UTC
Are you sure the database is in fact returning an ID? In case of it being NULL, the DBI would set `$id` to `undef`, which would explain the "use of unitialised variable" message...	[reply] [d/l] [select]
Re^2: DBI Out parameters by Anonymous Monk on Nov 19, 2008 at 13:54 UTC
Yeah, the query does return a value. It is as if dbi is not picking up the variable and returning it. i can attach the dbi trace if that is of any use? thanks.	[reply]
Re^3: DBI Out parameters by almut (Canon) on Nov 19, 2008 at 14:17 UTC
i can attach the dbi trace if that is of any use It might help... (no guarantees whatsoever, though). In case it's long, please wrap it in `<readmore>...</readmore>` tags, to not annoy those who don't want to look at it...	[reply] [d/l]
Re: DBI Out parameters by Krambambuli (Curate) on Nov 20, 2008 at 08:04 UTC
Just an idea, as I'm not really at home with PL/SQL: shouldn't your SQL procedure do something like RETURN :id somehwere ? Krambambuli ---	[reply]