#!perl

use strict;
use warnings;

use lib 't/lib';
use DBDOracleTestLib qw/ oracle_test_dsn table drop_table db_handle force_drop_table /;

use Test::More;
use DBD::Oracle qw(:ora_types ORA_OCI );
use DBI;

my $dbh = db_handle( { PrintError => 0 } );

plan $dbh
  ? ( tests => 12 )
  : ( skip_all => 'Unable to connect to Oracle' );

my $table = table();
force_drop_table($dbh);

$dbh->do("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' );
$sth->finish;

## test inserting a large value

$stmt = "INSERT INTO $table (id,data) VALUES (666, ?)";
$sth  = $dbh->prepare($stmt);
my $content = join( q{}, map { chr } ( 32 .. 64 ) ) x 16384;
$sth->bind_param( 1, $content, { ora_type => ORA_BLOB, ora_field => 'data' } );
eval { $sth->execute($content) };
is $@, '', 'inserted into BLOB successfully';
{
    local $dbh->{LongReadLen} = 1_000_000;
    my ($fetched) =
      $dbh->selectrow_array("select data from $table where id = 666");
    is $fetched, $content, 'got back what we put in';
}

## 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;
    return $dbh->selectrow_array(<<'END_SQL');
        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')
END_SQL
}

sub have_v_session {
    $dbh->do('select * from v$session where 0=1');
    return defined( $dbh->err ) ? $dbh->err != 942 : 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' );

    is( $dbh->ora_lob_is_init($loc), 1, 'returned initialized locator' );

    # write string > 32k
    $large_value = 'ABCD' x 10_000;

    $dbh->ora_lob_write( $loc, 1, $large_value );
    eval { $len = $dbh->ora_lob_length($loc); };
    if ($@) {
        note(
'It appears your Oracle or Oracle client has problems with ora_lob_length(lob_locator). We have seen this before - see RT 69350. The test is not going to fail because of this because we have seen it before but if you are using lob locators you might want to consider upgrading your Oracle client to 11.2 where we know this test works'
        );
        done_testing();
    }
    else {
        is( $len, 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;

        my $sth = $dbh->prepare( 'BEGIN ? := DBMS_LOB.GETLENGTH( ? ); END;',
            { 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 q|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;
        }

      TODO: {
            local $TODO =
              'problem reported w/ lobs and Oracle 11.2.*, see RT#69350'
              if ORA_OCI() =~ m/^11\.2\./;

            is( $len, length($large_value), 'returned length via PL/SQL' );
        }

        $dbh->{LongReadLen} = length($large_value) * 2;

        my $out;
        my $inout = lc $large_value;

        eval {
            $sth = $dbh->prepare( <<'END_SQL', { ora_auto_lob => 1 } );
  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;
END_SQL

            $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;

        };

        local $TODO = 'problem reported w/ lobs and Oracle 11.2.*, see RT#69350'
          if ORA_OCI() =~ m/^11\.2\./;

        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' );
    }
}

undef $sth;

END { eval { drop_table($dbh); } }

1;