$ENV{ORA_NCHAR_LITERAL_REPLACE} = 'TRUE';
$ENV{NLS_DATE_FORMAT} = 'DDMMYYYY';
$ENV{NLS_LANG} = "AMERICAN_AMERICA.TR8MSWIN1254";

open OUTFILE, "<:encoding(cp1254)", $in_file || die "Error open file ${file_name}: $!";

$l_temp_3 = $col_09;
$insert1 = "INSERT INTO xxfin.xxfin_test_load_file_2
                   (text_tr,
                    text_en,
                    creation_date)
                 VALUES
                   (?, 
                    ?,
                    sysdate)";
     $i1 = $dbh_ora->prepare($insert1);
     if (!$i1) 
       {
        print STDERR "Error:" . $dbh_ora->errstr() . "\n";
        exit 1;
       }
              
     Encode::from_to($l_temp_3, 'cp1254', 'UTF-16BE');
     
     $i1->bind_param(1, $l_temp_3);
     $i1->bind_param(2, $col_09);
     if (!$i1->execute())
       {
        print STDERR "Error:" . $i1->errstr() . "\n";
        exit 1;
       }
     $i1->finish();

Encode::_utf8_off($l_temp_3);
Encode::from_to($l_temp_3, 'cp1254', 'UTF-16BE');

use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);

use DBI qw(:sql_types);

#!/usr/local/bin/perl                                     
#
#

  use DBI qw(:sql_types);
#  use DBD::Oracle qw(:ora_types);
  use Encode qw(encode decode from_to);
  use File::Copy;
  use strict;
  use utf8;
  use open qw(:std :utf8); 
  use Data::Dumper;
#  use Text::Iconv;
#------ my parameters --------------
  my $org_id;
  my $file_name;
  my $user;
  my $password;
  my $dsn_ora;
  my $dbh_ora;
  my $sys_arg;
  my $my_arg;
  my $all_arg;
  my $pone;
  my @p_arg;
  my $j;
  my $in_file;
  my $str;
#-----------------------------------
  my $col_01;
  my $col_02;  
  my $col_03;
  my $col_04;
  my $col_05;
  my $col_06;
  my $col_07;
  my $col_08;
  my $col_09;
  my $col_10;
  my $col_11;
  my $col_12;
#-----------------------------------
  my $insert1;
  my $i1;
#-----------------------------------
  my $FCP_LOGIN;
  my $l_temp_3;
  
  binmode (STDERR, ':utf8');

  my $OA_HTML = $ENV{OA_HTML};
  my $APPL_OUT = $ENV{APPLCSF}.'/'.$ENV{APPLOUT};
  my $APPL_TOP = $ENV{APPL_TOP};

  if (@ARGV[0] eq '')
    {
     print "Error! No enter parameters. \n";
     exit(1);
    }  

## for 11i
##------------------------------------------------
  $all_arg = @ARGV[0];
    $pone=0;
    
     for ($j = 1; $j < scalar(@ARGV); ++$j){
       if((@ARGV[$j] =~ /^\"(\S+)\"/i)&&($pone==0)){
          $all_arg .= '  ' . @ARGV[$j];
	   $pone = 1;
	      }else{
	          $all_arg .= ' ' . @ARGV[$j];
		    }
		     }

### divide system and user parameters

  ($sys_arg, $my_arg ) = split(/  /, $all_arg);               

### take array @p_arg user parameters

  $my_arg = substr($my_arg, 1, length($my_arg)-2);

  @p_arg = split(/" "/, $my_arg);

  print STDERR "Start with parameters:\n";

  $org_id = @p_arg[0];
  $file_name = @p_arg[1];

  print STDERR "Org_id = $org_id \nFile_name = $file_name \n";

### Look system parameters
   if($sys_arg =~ /FCP_REQID=(\S+)\s+/i){
      print STDERR "FCP_REQID = $1\n";
      my $FCP_REQID = $1;
   }else{
      print STDERR "No parameter FCP_REQID\n";
      exit(1);
   }

   if($sys_arg =~ /FCP_LOGIN=\"(\S+)\"\s+/i){
      $FCP_LOGIN = $1;
   }else{
      print STDERR "No parameter FCP_LOGIN\n";
      exit(1);
   }

   if($sys_arg =~ /FCP_USERNAME=\"(\S+)\"\s+/i){
      my $FCP_USERNAME = substr($1,0,8);
   }else{
      print STDERR "No parameter FCP_USERNAME\n";
      exit(1);
   }

  $user = $FCP_LOGIN;
  $password = '';

  $ENV{ORA_NCHAR_LITERAL_REPLACE} = 'TRUE';
  $ENV{NLS_DATE_FORMAT} = 'DDMMYYYY';
  $ENV{NLS_LANG} = "TURKISH_TURKEY.TR8MSWIN1254";

##------ Connect --------------  
   $dsn_ora = 'dbi:Oracle:';
   $dbh_ora = DBI->connect($dsn_ora, $user, $password, {AutoCommit => 0});
   $dbh_ora->{pg_enable_utf8} = 1;
   if (!$dbh_ora) {
      print STDERR "\nConnection error\n";
      print STDERR "Error:" . $DBI::errstr . "\n";
      exit 1;
   }

   $dbh_ora->{LongReadLen} = 512 * 1024;

##---------- Open file -----------------
  $in_file = "${APPL_TOP}/xxupload/${file_name}";
  open OUTFILE, "<:encoding(cp1254)", $in_file || die "Error open file ${file_name}: $!";
#---------------------------------------
  while (<OUTFILE>)
    {
     $str = $_;
     next if (substr($str, 0, 1) eq '-');
     #------------------------------------------
     $col_01 = substr($str, 0, 5);     # B SUB
     $col_02 = substr($str, 6, 7);     # B HESAP
     $col_03 = substr($str, 14, 4);    # BNK
     $col_04 = substr($str, 19, 5);    # SUBE
     $col_05 = substr($str, 25, 19);   # ALACAK HESAP
     $col_06 = substr($str, 45, 18);   # TUTAR
     $col_07 = substr($str, 64, 3);    # DVZ
     $col_08 = substr($str, 68, 10);   # VADE TAR
     $col_09 = substr($str, 79, 30);   # ALACAKLI ADI
     $col_10 = substr($str, 110, 50);  # TGB HESAP ADI
     $col_11 = substr($str, 161, 40);  # ISLEM ACIKLAMASI
     $col_12 = substr($str, 202, 26);  # IBAN

     $insert1 = "INSERT INTO xxfin.xxfin_test_load_file_2
                   (text_tr,
                    text_en,
                    creation_date)
                 VALUES
                   (?, 
                    ?,
                    sysdate)";
     $i1 = $dbh_ora->prepare($insert1);
     if (!$i1) 
       {
        print STDERR "Error:" . $dbh_ora->errstr() . "\n";
        exit 1;
       }
     
     print STDERR "Dump col_09 = ", Dumper $col_09;
     print STDERR "1 col_09 = $col_09\n"; 
     print STDERR "\n";

     $l_temp_3 = $col_09;

     Encode::_utf8_off($l_temp_3);
     Encode::from_to($l_temp_3, 'cp1254', 'UTF-16BE');
#     Encode::_utf8_on($l_temp_3);
     print STDERR "Dump l_temp_3 = ", Dumper $l_temp_3;
     print STDERR "\n";
     print STDERR "1 l_temp_3 = $l_temp_3\n";
     printf STDERR ("2 l_temp_3 = %s\n", $l_temp_3);
     print STDERR "==============\n";

     $i1->bind_param(1, $l_temp_3);
     $i1->bind_param(2, $col_09, SQL_LONGVARCHAR);
     if (!$i1->execute())
       {
        print STDERR "Error:" . $i1->errstr() . "\n";
        exit 1;
       }
     $i1->finish();

    } #----------- while

  close STDERR;
  close OUTFILE;

  $dbh_ora->commit() or die "Error commit: $DBI::errstr\n";
  $dbh_ora->disconnect or warn "Error disconect: $DBI::errstr\n";

open OUTFILE, "<:encoding(iso-8859-5)", $in_file || die "Error open file ${file_name}: $!";

open OUTFILE, "<:encoding(iso-8859-5)", $in_file || die "Error open file ${file_name}: $!";