That answer is fine if you are planning to do other things in the PL/SQL
block beyond the select.
# in a block: (note that '?' type placeholders also work)
# The variable $vbb_ver_nr won't be interpolated inside q{}.
# I replaced it with a placeholder.
my $sth = $dbh->prepare(q{
BEGIN
select vtr_id into ? from t_vertraege
where vtr_nr = ?;
END;
}) or die "Prepare failed, $DBI::errstr";
$sth -> bind_param_inout( 1, \$var, );
$sth -> bind_param( 2, $vbb_ver_nr );
$sth -> execute or die "Execute failed, $DBI::errstr";
=============
If all you intend to do is fetch values from the SELECT, you can receive the
values using $sth->bind_columns() and $sth->fetch().
$dbh -> {RaiseError} = 1; # Implicit error checking
# I removed 'INTO' and ';' from the SELECT statement
my $sth = $dbh -> prepare( q{
select vtr_id from t_vertraege
where vtr_nr = ?
} ) or die "Prepare failed, $DBI::errstr";
$sth -> execute( $vbb_ver_nr ) or die "Execute failed, $DBI::errstr";
$sth -> bind_columns( \( $vbb_ver_nr ) );
while ( $sth -> fetch ) {
# use $vbb_ver_nr here
}
=============
There is lots of discussion and some examples in the fine manual. Run
'perldoc DBI' and perldoc 'DBDL::Oracle' to see them. There are also
examples in the DBD::Oracle distribution in DBD-Oracle-1.06/Oracle.ex/ .
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 23, 2001 07:26
Subject: AW: AW: (not) Too stupid to bind a variable... :-)
What Steve says is exactly what I want, and the code
my $sth = $dbh->prepare(q{
BEGIN select vtr_id into :1 from t_vertraege
where vtr_nr='$vbb_ver_nr';
END;
});
works very well...
-----Urspr�ngliche Nachricht-----
Von: Steve Sapovits [mailto:[EMAIL PROTECTED]]
Gesendet am: Mittwoch, 23. Mai 2001 16:17
An: Bart Lateur; [EMAIL PROTECTED]
Betreff: RE: AW: (not) Too stupid to bind a variable... :-)
I think he's trying to use it as a PL/SQL variable -- not
a table name. As in this block of PL/SQL code in the
DBD::Oracle docs:
FUNCTION func_np
RETURN VARCHAR2
IS
ret_val VARCHAR2(20);
BEGIN
SELECT USER INTO ret_val FROM DUAL;
RETURN ret_val;
END;
In this case the PL/SQL code is selecting from a table into
a variable.
The stuff below looks like an attempt to create on the fly
PL/SQL. I've never seen that done.
> -----Original Message-----
> From: Bart Lateur [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 23, 2001 10:13 AM
> To: [EMAIL PROTECTED]
> Subject: Re: AW: (not) Too stupid to bind a variable... :-)
>
>
> On Wed, 23 May 2001 16:02:00 +0200, [EMAIL PROTECTED] wrote:
>
> >What I wanted is:
> >
> > my $sth = $dbh->prepare(q{
> > BEGIN select vtr_id into :1 from t_vertraege
> > where vtr_nr='$vbb_ver_nr';
> > END;
> > });
>
> You may turn this around as much as you like, but you're
> still trying to
> do something for which placeholders were never intended. You
> want to use
> a placeholder for a table name. Placeholders were only intended to
> reference field (=column) values. Don't be surprised if anything
else
> doesn't work: no field names, no table names. That's not what
> it's for.
> Only field values.
