Oracle nightmare

[marcos rebelo]

Hi all

I have already lost one day around coding and documentation with this
problem, so I'm asking for help with DBD::Oracle.

I'm working around the unicode and BLOB problem. Can someone help me
setting the fooling code running and correctly undling the Unicode
part

use strict;
use warnings;
use v5.10;

use utf8;

use DBD::Oracle qw(ORA_BLOB ORA_CLOB SQLT_CHR SQLT_BIN);
use DBI;
use Data::Dumper;

local $ENV{'ORACLE_HOME'} = ( $ENV{'ORACLE_HOME'} ||
'/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/' );
local $ENV{'NLS_LANG'} = "AMERICAN_AMERICA.AL32UTF8";

my $dbh = DBI->connect(
    "DBI:Oracle:host=XXX.XXX.XXX.XXX;sid=ORACLE9;port=1522",
    'test_01',
    'test_01',
    {
        'RaiseError'              => 1,
        'ShowErrorStatement'      => 1,
        'AutoCommit'              => 1,
        'FetchHashKeyName'        => 'NAME_lc',
    }
);
$dbh->{'LongReadLen'} = 1024 * 1024;

eval { $dbh->do( 'DROP TABLE my_table' ) };

$dbh->do( 'CREATE TABLE my_table ( field_1 CLOB, field_2 BLOB )' );

my $nao_str = 'não';

{
    my $sth = $dbh->prepare( 'INSERT INTO my_table(field_1, field_2)
VALUES (?, ?)' );

    $sth->bind_param(1, "$nao_str",  { ora_type => SQLT_CHR });
    $sth->bind_param(2, "$nao_str", { ora_type => SQLT_BIN });

    $sth->execute( );
}

{
    my $sth = $dbh->prepare(
        'SELECT field_1, field_2 FROM my_table',
        { 'ora_pers_lob' => 1 },
    );

    $sth->execute();
    while ( my ( $field_1, $field_2 ) = $sth->fetchrow() ) {
        say "( $field_1, $field_2 )";

        say($nao_str eq $field_2 ? 'ok' : 'nok');
    }
}

{
    my $sth = $dbh->prepare( 'UPDATE my_table SET field_2 = ? WHERE
field_2 = ?' );

    $sth->bind_param(1, 'pippo',  { ora_type => SQLT_BIN });
    $sth->bind_param(2, $nao_str, { ora_type => SQLT_BIN });

    $sth->execute( );
}

{
    my $sth = $dbh->prepare(
        'SELECT field_1, field_2 FROM my_table',
        undef
    );

    $sth->execute();
    while ( my ( $field_1, $field_2 ) = $sth->fetchrow() ) {
        say "( $field_1, $field_2 )";
    }
}


Thanks
Marcos Rebelo

-- 
Marcos Rebelo
http://www.oleber.com/
Milan Perl Mongers leader https://sites.google.com/site/milanperlmongers/
Webmaster of http://perl5notebook.oleber.com

--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/