#!/usr/bin/perl
use strict;
use Test::More tests => 9;
use DBD::Oracle qw(:ora_types);
use DBI;
unshift @INC ,'t';
require 'nchar_test_lib.pl';
my $dbh;
$| = 1;
SKIP: {
$dbh = db_handle();
plan skip_all => "Not connected to oracle" if not $dbh;
my $table = table();
drop_table($dbh);
$dbh->do(qq{
CREATE TABLE $table (
id INTEGER NOT NULL,
data BLOB
)
});
my ($stmt, $sth, $id, $loc);
## test with insert empty blob and select locator.
$stmt = "INSERT INTO $table (id,data) VALUES (1, EMPTY_BLOB())";
$dbh->do($stmt);
$stmt = "SELECT data FROM $table WHERE id = ?";
$sth = $dbh->prepare($stmt, {ora_auto_lob => 0});
$id = 1;
$sth->bind_param(1, $id);
$sth->execute;
($loc) = $sth->fetchrow;
is (ref $loc, "OCILobLocatorPtr", "returned valid locator");
## test with insert empty blob returning blob to a var.
($id, $loc) = (2, undef);
$stmt = "INSERT INTO $table (id,data) VALUES (?, EMPTY_BLOB()) RETURNING data INTO ?";
$sth = $dbh->prepare($stmt, {ora_auto_lob => 0});
$sth->bind_param(1, $id);
$sth->bind_param_inout(2, \$loc, 0, {ora_type => ORA_BLOB});
$sth->execute;
is (ref $loc, "OCILobLocatorPtr", "returned valid locator");
sub temp_lob_count {
my $dbh = shift;
my $stmt = "
SELECT cache_lobs + nocache_lobs AS temp_lob_count
FROM v\$temporary_lobs templob,
v\$session sess
WHERE sess.sid = templob.sid
AND sess.audsid = userenv('sessionid') ";
my ($count) = $dbh->selectrow_array($stmt);
return $count;
}
sub have_v_session {
$dbh->do('select * from v$session where 0=1');
if ($dbh->err){
return if ($dbh->err == 942);
}
return 1;
}
## test writing / reading large data
{
# LOB locators cannot span transactions - turn off AutoCommit
local $dbh->{AutoCommit} = 0;
my ( $large_value, $len );
# get a new locator
$stmt = "INSERT INTO $table (id,data) VALUES (3, EMPTY_BLOB())";
$dbh->do($stmt);
$stmt = "SELECT data FROM $table WHERE id = ?";
$sth = $dbh->prepare( $stmt, { ora_auto_lob => 0 } );
$id = 3;
$sth->bind_param( 1, $id );
$sth->execute;
($loc) = $sth->fetchrow;
is( ref $loc, "OCILobLocatorPtr", "returned valid locator" );
# write string > 32k
$large_value = 'ABCD' x 10_000;
$dbh->ora_lob_write( $loc, 1, $large_value );
is( $dbh->ora_lob_length($loc), length($large_value), "returned length" );
is( $dbh->ora_lob_read( $loc, 1, length($large_value) ),
$large_value, "returned written value" );
## PL/SQL TESTS
SKIP: {
## test calling PL/SQL with LOB placeholder
my $plsql_testcount = 4;
$stmt = "BEGIN ? := DBMS_LOB.GETLENGTH( ? ); END;";
$sth = $dbh->prepare( $stmt, { ora_auto_lob => 0 } );
$sth->bind_param_inout( 1, \$len, 16 );
$sth->bind_param( 2, $loc, { ora_type => ORA_BLOB } );
$sth->execute;
# ORA-00600: internal error code
# ORA-00900: invalid SQL statement
# ORA-06550: PLS-00201: identifier 'DBMS_LOB.GETLENGTH' must be declared
# ORA-06553: PLS-00213: package STANDARD not accessible
if ( $dbh->err && grep { $dbh->err == $_ } ( 600, 900, 6550, 6553 ) ) {
skip "Your Oracle server doesn't support PL/SQL", $plsql_testcount
if $dbh->err == 900;
skip
"Your Oracle PL/SQL package DBMS_LOB is not properly installed", $plsql_testcount
if $dbh->err == 6550;
skip "Your Oracle PL/SQL is not properly installed", $plsql_testcount
if $dbh->err == 6553 || $dbh->err == 600;
}
is( $len, length($large_value), "returned length via PL/SQL" );
$stmt = "
DECLARE
-- testing IN, OUT, and IN OUT:
-- p_out will be set to LOWER(p_in)
-- p_inout will be set to p_inout || p_in
PROCEDURE lower_lob(p_in BLOB, p_out OUT BLOB, p_inout IN OUT BLOB) IS
pos INT;
buffer RAW(1024);
BEGIN
DBMS_LOB.CREATETEMPORARY(p_out, TRUE);
pos := 1;
WHILE pos <= DBMS_LOB.GETLENGTH(p_in)
LOOP
buffer := DBMS_LOB.SUBSTR(p_in, 1024, pos);
DBMS_LOB.WRITEAPPEND(p_out, UTL_RAW.LENGTH(buffer),
UTL_RAW.CAST_TO_RAW(LOWER(UTL_RAW.CAST_TO_VARCHAR2(buffer))));
DBMS_LOB.WRITEAPPEND(p_inout, UTL_RAW.LENGTH(buffer), buffer);
pos := pos + 1024;
END LOOP;
END;
BEGIN
lower_lob(:in, :out, :inout);
END; ";
my $out;
my $inout = lc $large_value;
local $dbh->{LongReadLen} = length($large_value) * 2;
$sth = $dbh->prepare( $stmt, { ora_auto_lob => 1 } );
$sth->bind_param( ':in', $large_value, { ora_type => ORA_BLOB });
$sth->bind_param_inout( ':out', \$out, 100, { ora_type => ORA_BLOB } );
$sth->bind_param_inout( ':inout', \$inout, 100, { ora_type => ORA_BLOB } );
$sth->execute;
skip "Your Oracle PL/SQL installation does not implement temporary LOBS", 3
if $dbh->err && $dbh->err == 6550;
is($out, lc($large_value), "returned LOB as string");
is($inout, lc($large_value).$large_value, "returned IN/OUT LOB as string");
undef $sth;
# lobs are freed with statement handle
skip q{can't check num of temp lobs, no access to v$session}, 1, unless have_v_session();
is(temp_lob_count($dbh), 0, "no temp lobs left");
}
}
$dbh->do("DROP TABLE $table");
$dbh->disconnect;
}
1;