use strict;
use DBI;
use DBD::Oracle;
use DBD::Oracle qw(:ora_types);
use Data::Dumper;

my $oradbh;  # Oracle connection handle
my $orasth;  # SQL statement handle
my @row;     # row returned by a SQL
my $sql;     # SQL text
my $xml;     # XML value

$oradbh = DBI->connect ('dbi:Oracle:MIKB2','michel','michel',
                        { PrintError => 0,
                          PrintWarn => 0,
                          RaiseError => 0,
                          AutoCommit => 0,
                          LongReadLen => 1024*1024, # 1MB LONG read length
                          LongTruncOk => 0,         # Allow LONG truncation
                          Warn => 1 }
                       );
do { 
  print "*** Unable to connect to database: " .
        (substr $DBI::errstr, 0, index($DBI::errstr,'(')) .
        "\n"; 
  exit 1;
} unless $oradbh;

#$xml = $oradbh->selectrow_array(<<'SQL');
#select  WAREHOUSE_SPEC 
#from OE.WAREHOUSES 
#where WAREHOUSE_SPEC is not null and rownum = 1
#SQL

# The query
$sql = 'select  WAREHOUSE_SPEC 
from OE.WAREHOUSES 
where WAREHOUSE_SPEC is not null and rownum = 1';

# Prepare SQL
$orasth = $oradbh->prepare($sql);
do {
  print "*** Unable to prepare sql: " .
        (substr $DBI::errstr, 0, index($DBI::errstr,'(')) .
        "\n"; 
} unless $orasth;

# Execute SQL 
do {
  print "*** Unable to execute sql: " .
        (substr $DBI::errstr, 0, index($DBI::errstr,'(')) .
        "\n"; 
} unless $orasth->execute();

# Bind variable
$orasth->bind_col(1, \$xml, {ora_type => ORA_CLOB});

# Fetch SQL 
do {
  print "*** Unable to fetch sql: " .
        (substr $DBI::errstr, 0, index($DBI::errstr,'(')) .
        "\n"; 
} unless $orasth->fetchrow_array() ;

# Print value
print "XML: $xml\n";
print "Dump: ",Dumper($xml),"\n";

$oradbh->{Warn}=0;
$oradbh->disconnect;