Perl DBI connect oracle example

Perl DBI connect oracle example.

Select statement

#!/export/home/oracle/bin/jade -w
use strict;
use DBI qw(:sql_types);

$ENV{'ORACLE_HOME'} = "/oracle_base/10204″;

my $dbh = DBI->connect('dbi:Oracle:host=alexzeng.wordpress.com;sid=WENJIN;port=1521′,'alex','alex') || die "Database connection not made";
$dbh->{RowCacheSize} = 100;

my $sql = qq{ select /*+ use_nl(t1,t2) index(t1),index(t2)*/t1.b,t2.b from t1,t2 where t1.a between 10 and 200000 and t1.b=t2.a};
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $a,$b );
$sth->bind_columns( undef,\$a,\$b );
while( $sth->fetch() ) {
  print "$a,$b\n";
}
$sth->finish();
$dbh->disconnect();

Insert statement

#!/export/home/oracle/bin/jade -w
use strict;
use DBI qw(:sql_types);
$ENV{'ORACLE_HOME'} = "/export/home/oracle/products/10204″;
my $dbh = DBI->connect('dbi:Oracle:host=alexzeng.wordpress.com;sid=WENJIN;port=1521′,'alex','alex') || die "Database connection not made";
my $insert_handle = $dbh->prepare_cached('INSERT INTO test_flist VALUES (?)');
my $single_raw='1′;
my $one_k_raw;
my $eight_k_raw;
my $cnt;
for ($cnt=0;$cnt<10;$cnt++){
  $single_raw=$single_raw.$single_raw;
}
$one_k_raw=$single_raw;
for ($cnt=0;$cnt<80;$cnt++){
  $eight_k_raw=$eight_k_raw.$one_k_raw
}
print length($eight_k_raw)."\n";

$insert_handle->bind_param(1, $eight_k_raw, SQL_LONGVARBINARY);
for ($cnt=0;$cnt<100;$cnt++){
  $insert_handle->execute() or return 0;
}
print "complete\n";

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

7 Responses to Perl DBI connect oracle example

  1. VK says:

    Hi,
    I am getting this error
    Can’t locate DBI.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.8/sun4-solaris.. Can you pls help?

    Thanks,
    VK

  2. rahul says:

    I am getting the below error

    DBI connect(‘host=xyz’,’user1′,…) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. at sql.pl line 5
    ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc.

    code:

    use DBI;
    use DBD::Oracle;
    $ENV{‘ORACLE_HOME’} = “/oracle/product/11.2.0.3_std/”;
    my $db = DBI->connect( “dbi:Oracle:host=xyz”, “user1”, “user1” )

    || die( $DBI::errstr . “\n” );

    Thanks,
    Rahul

    • Alex Zeng says:

      Probably you can remove the “/” at the end and try again.

      From
      $ENV{‘ORACLE_HOME’} = “/oracle/product/11.2.0.3_std/”;
      To
      $ENV{‘ORACLE_HOME’} = “/oracle/product/11.2.0.3_std”;

      I have experiences that an additional “/” causes issues.

      Regards,
      Alex

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: