CODEWITHSUNDEEP
oracle-databaseperldbi
user10930455
user10930455

Reputation: 1

determining what ora_type values are supported

#!/usr/bin/perl
use diagnostics;
use DBI;
use DBI qw(:sql_types);

my $AIM_PSWD= $ENV{'AIM_PSWD'};
my $len=length($AIM_PSWD);
my $pwindex1=rindex($AIM_PSWD,'/');
my $pwindex2=rindex($AIM_PSWD,'@');

my $usr = substr($AIM_PSWD,0,$pwindex1);
my $pwd = substr($AIM_PSWD,$pwindex1+1,$pwindex2-($pwindex1+1));
my $db  = substr($AIM_PSWD,$pwindex2+1,$len-($pwindex2+1));
my $attr = {AutoCommit => 0};

my $dbh;
my $qryHandle;
my $row;
my $query = "SELECT /*+ index(T_CAPITATION_HIST I_CAPITATION_HIST) */ SAK_CAPITATION FROM AIM.T_CAPITATION_HIST WHERE SAK_CAPITATION = ?";

$dbh = DBI->connect("dbi:Oracle:$db", $usr, $pwd, $attr)
   or die "Unable to connect to $db\n";
$qryHandle = $dbh->prepare($query) or die "Unable to prepare query\n";
$qryHandle->bind_param(1, "765756556", {ora_type => SQL_VARCHAR});
$qryHandle->execute() or die "Unable to execute query\n";
if ($row = $qryHandle->fetchrow_hashref())
{
    print "query succeeded\n";
}
else
{
    print "query failed\n";
}
$qryHandle->finish();
$dbh->disconnect();
exit(0);

Running this produces the error message from the bind_param statement: Uncaught exception from user code: Can't bind :p1, ora_type 12 not supported by DBD::Oracle at ./test.pl line 26.

How do I determine what ora_type values are supported?

Upvotes: 0

Views: 491

Answers (1)

Grinnz
Grinnz

Reputation: 9231

The available ora_type values are listed at https://metacpan.org/pod/DBD::Oracle#:ora_types and described at https://metacpan.org/pod/DBD::Oracle#ora_type, and must be imported from DBD::Oracle like:

use DBD::Oracle ':ora_types';

If you want to pass a standard SQL type that you imported from DBI :sql_types, just pass it directly as the bind type:

$qryHandle->bind_param(1, "765756556", SQL_VARCHAR);

See https://metacpan.org/pod/DBD::Oracle#bind_param.

Upvotes: 3

Related Questions