jump to navigation

Turn off Oracle password expiration Tue - Jul 24, 2012

Posted by supriyatna in All, Howto, Oracle.
Tags: , ,
add a comment

Pada Oracle Database 11g (termasuk Oracle Database XE -Express Edition- 11g) secara default password user akan expired setelah 180 hari. Pesan error yang muncul adalah sebagai berikut:

ERROR:
ORA-28001: the password has expired

Setting 180 hari ini ditentukan pada Oracle profile. Kita bisa melihat nilai dari DEFAULT profile melalui select terhadap table DBA_PROFILES.

SQL> set linesize 300
SQL> select * from dba_profiles order by 1,2;

PROFILE   RESOURCE_NAME               RESOURCE LIMIT
--------- --------------------------- -------- ---------
DEFAULT   COMPOSITE_LIMIT             KERNEL   UNLIMITED
DEFAULT   SESSIONS_PER_USER           KERNEL   UNLIMITED
DEFAULT   CPU_PER_SESSION             KERNEL   UNLIMITED
DEFAULT   CPU_PER_CALL                KERNEL   UNLIMITED
DEFAULT   LOGICAL_READS_PER_SESSION   KERNEL   UNLIMITED
DEFAULT   LOGICAL_READS_PER_CALL      KERNEL   UNLIMITED
DEFAULT   IDLE_TIME                   KERNEL   UNLIMITED
DEFAULT   CONNECT_TIME                KERNEL   UNLIMITED
DEFAULT   PRIVATE_SGA                 KERNEL   UNLIMITED
DEFAULT   FAILED_LOGIN_ATTEMPTS       PASSWORD 10
DEFAULT   PASSWORD_LIFE_TIME          PASSWORD 180
DEFAULT   PASSWORD_REUSE_TIME         PASSWORD UNLIMITED
DEFAULT   PASSWORD_REUSE_MAX          PASSWORD UNLIMITED
DEFAULT   PASSWORD_VERIFY_FUNCTION    PASSWORD NULL
DEFAULT   PASSWORD_LOCK_TIME          PASSWORD 1
DEFAULT   PASSWORD_GRACE_TIME         PASSWORD 7

16 rows selected.

Untuk mematikan/mendisable password expiration cara paling mudah yaitu dengan merubah setting PASSWORD_LIFE_TIME dari 180 (hari) menjadi UNLIMITED.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Saya sendiri cenderung lebih memilih untuk membuat profile baru, dengan setting parameter yang telah kita sesuaikan. Untuk membuat/men-drop profile baru dengan cara berikut:

-- DROP PROFILE default2;
CREATE PROFILE default2 LIMIT
    COMPOSITE_LIMIT UNLIMITED
    CONNECT_TIME UNLIMITED
    CPU_PER_CALL UNLIMITED
    CPU_PER_SESSION UNLIMITED
    FAILED_LOGIN_ATTEMPTS 10
    IDLE_TIME UNLIMITED
    LOGICAL_READS_PER_CALL UNLIMITED
    LOGICAL_READS_PER_SESSION UNLIMITED
    PASSWORD_GRACE_TIME 7
    PASSWORD_LIFE_TIME UNLIMITED
    PASSWORD_LOCK_TIME 1
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_VERIFY_FUNCTION NULL
    PRIVATE_SGA UNLIMITED
    SESSIONS_PER_USER UNLIMITED
;

Lalu assign profile DEFAULT2 yang baru saja dibuat terhadap user yang kita inginkan.

ALTER USER dede PROFILE DEFAULT2;
Advertisements

Howto change font characters on OEM Console 10g to english characters Sun - Jul 1, 2012

Posted by supriyatna in All, Oracle, Windows.
Tags: , , ,
add a comment

Secara default Oracle Enterprise Manager Console (OEM Console) akan menggunakan font character sesuai dengan local setting, kebetulan hingga saat ini saya mengaktifkan “Chinese (PRC)” karakter pada setting “Regional Setting”.

Hal ini menyebabkan secara default OEM Console 10g menampilkan karakter cina, bahasa “dewa”! karena saya tidak mengerti tulisannya :D. Dan ini nampaknya tidak terpengaruh oleh setting NLS_LANG, karena walaupun registry dan Environment Variable-nya telah diganti menjadi SET NLS_LANG =AMERICAN_AMERICA.WE8ISO8859P1, OEM Console tetap ditampilkan dalam karakter cina.

OEM Console in Chinese charactersAwalnya saya  terpaksa tetap menggunakan OEM Consloe berbahasa “dewa” ini, dengan menebak-nebak dimana posisi menu yang saya inginkan, atau kalau sudah pusing sekali saya akan membuka OEM Console di komputer lain yang masih menggunakan setting default English. Bahkan pernah pula “terpaksa” mengubah kembali setting “Language for non-Unicode program” (yang ada di Control Panel | Regional Setting) ke “English (United States)“, dan HARUS restart komputer :((.

Rupanya kesabaran saya habis! :D. Ini harus ada solusinya, setelah searching via google ternyata penyebabnya karena java (YA! OEM Console merupakan aplikasi Java) secara default akan membaca local/regional setting dari komputer, bukan membaca NLS_LANG. Sehingga solusinya, sebelum menjalankan OEM Console kita harus memaksa java untuk menggunakan language dan region yang kita inginkan. Di sini saya menggunakan language english (en) dan region United States (US) (en-US) agar tulisannya muncul dalam huruf latin.

Caranya buka file %ORACLE_HOME%\BIN\oemapp.bat, dan cari entry: SET JRE=java %ORACLE_OEM_JAVAMX% lalu tambahkan parameter user.language dan user.region seperti terlihat di bawah ini.

REM Dede-, Jkt, 20120625
REM SET JRE=java %ORACLE_OEM_JAVAMX%
REM Dede+, Jkt, 20120625, http://cookbook.remuslife.com
SET JRE=java -Duser.language=en -Duser.region=US %ORACLE_OEM_JAVAMX%

Edit oemapp.bat

Editing oemapp.bat

OEM Console in English (US)

OEM Console setelah menggunakan language dan region en-US.

OEM Console in France (FR)

OEM Console menggunakan language dan region fr-FR.

References:

Keyword:
Howto change font characters on OEM Console 10g to english characters

Howto change the font type/style on Oracle Enterprise Manager Console (10g) Tue - Jun 26, 2012

Posted by supriyatna in All, Howto, Oracle.
Tags: , ,
add a comment

Pada Oracle Enterprise Manager Console, jika ingin mengubah jenis font kita dapat melakukannya melalui menu “Configuration > Font Setting…”. Namun defaultnya hanya dapat menggunakan jenis font berikut: Dialog, SanSerif, Serif, Monospaced, DialogInput. Selain font diatas tidak ada pilihan lain yang tersedia.

Bagaimana jika ingin mengubah  ke jenis font selain yang ada di atas? Contohnya saya menyukai font Tahoma, ini mudah! ini dapat dilakukan dengan mengedit langsung file dbappprf.properties yang berada di folder:

<ORACLE_HOME>\sysman\config\dbappprf.properties

Pada komputer saya file tersebut terletak di:

D:\ORACLE\ORA102\sysman\config\dbappprf.properties

Edit/tambahkan entry berikut:

/<username>/GlobalApp/FontPref=<font_type>,<font_style>,<font_size>

Keterangan:
<font_type> : Jenis font seperti Arial, Tahoma, Consolas, etc…
<font_style>: 0 = Standard, 1 = Bold, 2 = Italic, 3 = Italic Bold
<font_size> : Ukuran font

Sebagai contoh, saya ingin menggunakan font Tahoma (Bold) dengan ukuran font 11, maka saya tambahkan baris berikut pada file dbappprf.properties:

/dede/GlobalApp/FontPref=Tahoma,1,11

Keyword:
Howto change the font type/style on Oracle Enterprise Manager Console (10g)

Oracle: Menampilkan hari dan bulan dalam bahasa Indonesia Sat - May 14, 2011

Posted by supriyatna in All, Howto, Oracle, Programming.
Tags: , , ,
1 comment so far

Ingin menampilkan hari dan bulan dalam bahasa Indonesia? ngga perlu repot-repot buat function sendiri, karena sudah tersedia built-in function dari Oracle.

select TO_CHAR(TO_DATE('01-FEB-2011'), 'DAY, DD MONTH YYYY'
    ,'nls_date_language = INDONESIAN') from dual;

TO_CHAR(TO_DATE('01-FEB-2
-------------------------
SELASA, 01 FEBRUARI 2011

Posted at indo-oracle facebook group on 12 May 2011

Upload/import DBASE/FoxPro DBF files into Oracle Database Sun - Apr 24, 2011

Posted by supriyatna in All, Oracle, Programming.
Tags: , , ,
6 comments

If you need to import/upload data from DBF (DBASE or Foxpro database files), there is a script that already developed by Tom Kyte.

I have made some modification to fulfill my need, here are the changes:

  • Add UPLOAD_TIME column to keep date of upload process from DBF into oracle
  • Add support of MEMO fields that has more than 4000 characters

Howto use this script?

First you need to prepare Oracle directory where the dbf files will you stored
You can called it to any name you like. For my example i choose to named it as DIRDBF.

create or replace directory dirdbf as 'e:\data\dbf';
grant read,write on directory dirdbf to <username>;

Show structure table of employee.DBF that located on DIRDBF directory
This will also generate Oracle’s command of “create table ” which constructed from DBF structure format. This table should be exists in Oracle database before import/upload process in next step.

begin
    dbase_fox.load_table( 'DIRDBF','employee.DBF','DT_EMPLOYEE'
        ,p_show => TRUE );
end;
/

Uplad employee.DBF that located on DIRDBF directory into Oracle database
This is simple, you just need to change the p_show parameter to FALSE

begin
    dbase_fox.load_table( 'DIRDBF','employee.DBF','DT_EMPLOYEE'
        ,p_show => FALSE );
end;
/

References:

Package Specification

create or replace package dbase_fox
as

    -- procedure to a load a table with records
    -- from a DBASE file.
    --
    -- Uses a BFILE to read binary data and dbms_sql
    -- to dynamically insert into any table you
    -- have insert on.
    --
    -- p_dir is the name of an ORACLE Directory Object
    --       that was created via the CREATE DIRECTORY
    --       command
    --
    -- p_file is the name of a file in that directory
    --        will be the name of the DBASE file
    --
    -- p_tname is the name of the table to load from
    --
    -- p_cnames is an optional list of comma separated
    --          column names.  If not supplied, this pkg
    --          assumes the column names in the DBASE file
    --          are the same as the column names in the
    --          table
    --
    -- p_show boolean that if TRUE will cause us to just
    --        PRINT (and not insert) what we find in the
    --        DBASE files (not the data, just the info
    --        from the dbase headers....)

    procedure load_Table( p_dir    in varchar2,
                          p_file   in varchar2,
                          p_tname  in varchar2,
                          p_cnames in varchar2 default NULL,
                          p_show   in BOOLEAN default FALSE);
end;
/

Package Body

create or replace package body dbase_fox
as

-- Might have to change on your platform!!!
-- Controls the byte order of binary integers read in
-- from the dbase file
BIG_ENDIAN      constant boolean default TRUE;

type dbf_header is RECORD
(
    version    varchar2(25), -- dBASE version number
    year       int,          -- 1 byte int year, add to 1900
    month      int,          -- 1 byte month
    day        int,             -- 1 byte day
    no_records int,             -- number of records in file,
                             -- 4 byte int
    hdr_len    int,             -- length of header, 2 byte int
    rec_len    int,             -- number of bytes in record,
                             -- 2 byte int
    no_fields  int           -- number of fields
);

type field_descriptor is RECORD
(
    name     varchar2(11),
    type     char(1),
    length   int,   -- 1 byte length
    decimals int    -- 1 byte scale
);

type field_descriptor_array
is table of
field_descriptor index by binary_integer;

type rowArray
is table of
varchar2(4000) index by binary_integer;

-- Dede from Indonesia, to support char > 4000
type rowArrayCLOB
is table of
clob index by binary_integer;

g_process_time  varchar2(10) default to_char(sysdate,'YYYYMMDD');   -- Dede+, Jkt, 20091109_1

g_cursor binary_integer default dbms_sql.open_cursor;

-- Function to convert a binary unsigned integer
-- into a PLSQL number
function to_int( p_data in varchar2 ) return number
is
    l_number number default 0;
    l_bytes  number default length(p_data);
begin
    if (big_endian)
    then
        for i in 1 .. l_bytes loop
            l_number := l_number +
                        ascii(substr(p_data,i,1)) *
                        power(2,8*(i-1));
        end loop;
    else
        for i in 1 .. l_bytes loop
            l_number := l_number +
                        ascii(substr(p_data,l_bytes-i+1,1)) *
                        power(2,8*(i-1));
        end loop;
    end if;

    return l_number;
end;

-- Alex from Russia add this function
-- to convert a HexDecimal value
-- into a Decimal value
function Hex2Dec( p_data in varchar2 ) return number
is
    l_number number default 0;
    l_bytes  number default length(p_data);
    byte_number number;
    byte_string varchar2 (1);
begin
  if( l_bytes > 0 ) then
    for i in 1 .. l_bytes loop
         byte_string := substr(p_data,l_bytes-i+1,1);
         case byte_string
            when 'A' then byte_number:=10;
            when 'B' then byte_number:=11;
            when 'C' then byte_number:=12;
            when 'D' then byte_number:=13;
            when 'E' then byte_number:=14;
            when 'F' then byte_number:=15;
            else byte_number:=to_number(byte_string);
         end case;
         l_number := l_number + byte_number * power(16,(i-1));
    end loop;
    return l_number;
  else
    return 0;
  end if;
end;

--Mattia from Italy add this function
function mytrim(p_str in varchar2) return varchar2 is
i number;
j number;
v_res varchar2(100);
begin
  for i in 1 .. 11 loop
    if ascii(substr(p_str,i,1)) = 0 then
     j:= i;
     exit;
    end if;
  end loop;
  v_res := substr(p_str,1,j-1);
  return v_res;
end mytrim;

-- Routine to parse the DBASE header record, can get
-- all of the details of the contents of a dbase file from
-- this header

procedure get_header
(p_bfile        in bfile,
 p_bfile_offset in out NUMBER,
 p_hdr          in out dbf_header,
 p_flds         in out field_descriptor_array )
is
    l_data            varchar2(100);
    l_hdr_size        number default 32;
    l_field_desc_size number default 32;
    l_flds            field_descriptor_array;
begin
    p_flds := l_flds;

    l_data := utl_raw.cast_to_varchar2(
                       dbms_lob.substr( p_bfile,
                                        l_hdr_size,
                                        p_bfile_offset ) );
    p_bfile_offset := p_bfile_offset + l_hdr_size;

    p_hdr.version    := ascii( substr( l_data, 1, 1 ) );
    p_hdr.year       := 1900 + ascii( substr( l_data, 2, 1 ) );
    p_hdr.month      := ascii( substr( l_data, 3, 1 ) );
    p_hdr.day        := ascii( substr( l_data, 4, 1 ) );
    p_hdr.no_records := to_int( substr( l_data,  5, 4 ) );
    p_hdr.hdr_len    := to_int( substr( l_data,  9, 2 ) );
    p_hdr.rec_len    := to_int( substr( l_data, 11, 2 ) );
    p_hdr.no_fields  := trunc( (p_hdr.hdr_len - l_hdr_size)/
                                           l_field_desc_size );

    for i in 1 .. p_hdr.no_fields
    loop
        l_data := utl_raw.cast_to_varchar2(
                         dbms_lob.substr( p_bfile,
                                          l_field_desc_size,
                                          p_bfile_offset ));
        p_bfile_offset := p_bfile_offset + l_field_desc_size;
        p_flds(i).name := mytrim(substr(l_data,1,11));
        p_flds(i).type := substr( l_data, 12, 1 );
        p_flds(i).length  := ascii( substr( l_data, 17, 1 ) );
        p_flds(i).decimals := ascii(substr(l_data,18,1) );
    end loop;

    p_bfile_offset := p_bfile_offset +
                          mod( p_hdr.hdr_len - l_hdr_size,
                               l_field_desc_size );
    dbms_output.put_line('get_header passed.');
end;

function build_insert
( p_tname in varchar2,
  p_cnames in varchar2,
  p_flds in field_descriptor_array) return varchar2
is
    l_insert_statement long;
begin
    l_insert_statement := 'insert into ' || p_tname || '(';
    if ( p_cnames is NOT NULL )
    then
        l_insert_statement := l_insert_statement ||
                              p_cnames || ') values (';
    else
        for i in 1 .. p_flds.count
        loop
            if ( i <> 1 )
            then
               l_insert_statement := l_insert_statement||',';
            end if;
            l_insert_statement := l_insert_statement ||
                            '"'||  p_flds(i).name || '"';
        end loop;
        l_insert_statement := l_insert_statement ||',"UPLOAD_TIME"';    -- Dede+, Jkt, 20091109_1
        l_insert_statement := l_insert_statement ||
                                           ') values (';
    end if;
    for i in 1 .. p_flds.count
    loop
        if ( i <> 1 )
        then
           l_insert_statement := l_insert_statement || ',';
        end if;
        if ( p_flds(i).type = 'D' )
        then

            l_insert_statement := l_insert_statement ||
                     'to_date(:bv' || i || ',''yyyymmdd'' )';
        else
            l_insert_statement := l_insert_statement ||
                                                ':bv' || i;
        end if;
    end loop;
    -- Dede+, Jkt, 20091109_1, START
    l_insert_statement := l_insert_statement ||
             ',to_date(:bv' || (p_flds.count+1) || ',''yyyymmdd'' )';
    -- Dede+, Jkt, 20091109_1, END
    l_insert_statement := l_insert_statement || ')';

    return l_insert_statement;
end;

function get_row
( p_bfile in bfile,
  p_bfile_offset in out number,
  p_hdr in dbf_header,
  p_flds in field_descriptor_array,
  f_bfile in bfile,
  -- memo_block in number ) return rowArray     -- Dede-, Jkt, 20090828
  memo_block in number ) return rowArrayCLOB    -- Dede+, Jkt, 20090828
is
    --l_data  varchar2(4000);   -- Dede-, Jkt, 20090828
    l_data  CLOB;               -- Dede+, Jkt, 20090828
    l_row   rowArrayCLOB;       -- Dede+, Jkt, 20090828
    l_n     number default 2;
    f_block number;
begin
    l_data := utl_raw.cast_to_varchar2(
                   dbms_lob.substr( p_bfile,
                                    p_hdr.rec_len,
                                    p_bfile_offset ) );
    p_bfile_offset := p_bfile_offset + p_hdr.rec_len;

    l_row(0) := substr( l_data, 1, 1 );

    for i in 1 .. p_hdr.no_fields loop
        l_row(i) := rtrim(ltrim(substr( l_data,
                                        l_n,
                                        p_flds(i).length ) ));
        if ( p_flds(i).type = 'F' and l_row(i) = '.' )
        then
            l_row(i) := NULL;
-------------------working with Memo fields
        elsif ( p_flds(i).type = 'M' ) then
           --Check is file exists
           if( dbms_lob.isopen( f_bfile ) != 0) then
              --f_block - memo block length
              f_block  := Hex2Dec(dbms_lob.substr( f_bfile, 4, to_number(l_row(i))*memo_block+5 ));
              --to_number(l_row(i))*memo_block+9 - offset in memo file *.fpt, where l_row(i) - number of
              --memo block in fpt file
              l_row(i) := utl_raw.cast_to_varchar2(dbms_lob.substr( f_bfile, f_block, to_number(l_row(i))*memo_block+9));
              --l_row(i) := substr(utl_raw.cast_to_varchar2(dbms_lob.substr( f_bfile, f_block, to_number(l_row(i))*memo_block+9)),1,4000);
           else
              l_row(i) := NULL;
              /*
              dbms_output.put_line('Not found .fpt file');
              exit;
              */
           end if;
-------------------------------------------
        end if;
        l_n := l_n + p_flds(i).length;
    end loop;
    return l_row;
end get_row;

procedure show( p_hdr    in dbf_header,
                p_flds   in field_descriptor_array,
                p_tname  in varchar2,
                p_cnames in varchar2,
                p_bfile  in bfile )
is
    l_sep varchar2(1) default ',';
    l_length_num_adj  number default 3; -- Dede+, Jkt, 20090828, to handle "ORA-01438: value larger than specified precision allowed for this column" error

    procedure p(p_str in varchar2)
    is
        l_str long default p_str;
    begin
        while( l_str is not null )
        loop
            dbms_output.put_line( substr(l_str,1,250) );
            l_str := substr( l_str, 251 );
        end loop;
    end;
begin
    p( 'Sizeof DBASE File: ' || dbms_lob.getlength(p_bfile) );
    p( 'DBASE Header Information: ' );
    p( chr(9)||'Version = ' || p_hdr.version );
    p( chr(9)||'Year    = ' || p_hdr.year   );
    p( chr(9)||'Month   = ' || p_hdr.month   );
    p( chr(9)||'Day     = ' || p_hdr.day   );
    p( chr(9)||'#Recs   = ' || p_hdr.no_records);
    p( chr(9)||'Hdr Len = ' || p_hdr.hdr_len  );
    p( chr(9)||'Rec Len = ' || p_hdr.rec_len  );
    p( chr(9)||'#Fields = ' || p_hdr.no_fields );

    p( chr(10)||'Data Fields:' );
    for i in 1 .. p_hdr.no_fields
    loop
        p( 'Field(' || i || ') '
             || 'Name = "' || p_flds(i).name || '", '
             || 'Type = ' || p_flds(i).Type || ', '
             || 'Len  = ' || p_flds(i).length || ', '
             || 'Scale= ' || p_flds(i).decimals );
    end loop;

    p( chr(10) || 'Insert We would use:' );
    p( build_insert( p_tname, p_cnames, p_flds ) );

    p( chr(10) || 'Table that could be created to hold data:');
    p( 'create table ' || p_tname );
    p( '(' );

    for i in 1 .. p_hdr.no_fields
    loop
        -- if ( i = p_hdr.no_fields ) then l_sep := ')'; end if;    -- Dede-, Jkt, 20091109_1
        dbms_output.put
        ( chr(9) || '"' || p_flds(i).name || '"   ');

        if ( p_flds(i).type = 'D' ) then
            p( 'date' || l_sep );
        elsif ( p_flds(i).type = 'F' ) then
            p( 'float' || l_sep );
        elsif ( p_flds(i).type = 'N' ) then
            if ( p_flds(i).decimals > 0 )
            then
                -- p( 'number('||p_flds(i).length||','||                -- Dede-, Jkt, 20090828
                p( 'number('||(TO_NUMBER(p_flds(i).length) + l_length_num_adj)||','||   -- Dede+, Jkt, 20090828
                              p_flds(i).decimals || ')' ||
                              l_sep );
            else
                p( 'number('||p_flds(i).length||')'||l_sep );
            end if;
        elsif ( p_flds(i).type = 'M' ) then
            p( 'clob' || l_sep);
        else
            p( 'varchar2(' || p_flds(i).length || ')'||l_sep);
        end if;
    end loop;
    p( chr(9) || '"UPLOAD_TIME"   date '||')');     -- Dede+, Jkt, 20091109_1
    p( '/' );
end;

procedure load_Table( p_dir in varchar2,
                      p_file in varchar2,
                      p_tname in varchar2,
                      p_cnames in varchar2 default NULL,
                      p_show in boolean default FALSE )
is
    l_bfile    bfile;
    f_bfile    bfile;
    l_offset   number default 1;
    l_hdr      dbf_header;
    l_flds     field_descriptor_array;
    -- l_row      rowArray;     -- Dede-, Jkt, 20090828
    l_row      rowArrayCLOB;    -- Dede+, Jkt, 20090828
    f_file     varchar2(25);
    memo_block   number;
    n_dummy     number;
begin
    f_file := substr(p_file,1,length(p_file)-4) || '.fpt';
    l_bfile := bfilename( p_dir, p_file );
    dbms_lob.fileopen( l_bfile );

-----------------------  Alex from Russia add this
    f_bfile := bfilename( p_dir, f_file );
    if( dbms_lob.fileexists(f_bfile) != 0 ) then
      dbms_output.put_line(f_file || ' - Open memo file');
      dbms_lob.fileopen( f_bfile );
    end if;
--------------------------------------------------
    get_header( l_bfile, l_offset, l_hdr, l_flds );
    if ( p_show )
    then
        show( l_hdr, l_flds, p_tname, p_cnames, l_bfile );
    else
        dbms_output.put_line('before parse(): '||p_tname||'/'||p_cnames);   -- Dede+
        dbms_sql.parse( g_cursor,
                        build_insert( p_tname, p_cnames, l_flds ),
                        dbms_sql.native );
        dbms_output.put_line('after parse()');
--      Memo block size in ftp file
        if ( dbms_lob.isopen( f_bfile ) > 0 ) then
            memo_block := Hex2Dec(dbms_lob.substr(f_bfile, 2, 7));
        else
            memo_block := 0;
        end if;

        dbms_output.put_line( build_insert( p_tname, p_cnames, l_flds ) );
        for i in 1 .. l_hdr.no_records loop
            --dbms_output.put('fetch row#: '||i);
            l_row := get_row( l_bfile,
                              l_offset,
                              l_hdr,
                              l_flds, f_bfile, memo_block );
            --dbms_output.put_line('..done');
            if ( l_row(0) <> '*' ) -- deleted record
            then
                for i in 1..l_hdr.no_fields loop
                    n_dummy := i;
                    /*  -- Dede-, Jkt, 20090828
                    dbms_sql.bind_variable( g_cursor,
                                            ':bv'||i,
                                            l_row(i),
                                            4000 );
                    */
                    -- Dede+, Jkt, 20090828, START
                    if ( length( l_row(i) ) <= 4000 ) then
                        dbms_sql.bind_variable( g_cursor, ':bv'||i, l_row(i), 4000 );
                    else
                        dbms_sql.bind_variable( g_cursor, ':bv'||i, l_row(i) );
                    end if;
                    -- Dede+, Jkt, 20090828, END

                end loop;

                dbms_sql.bind_variable( g_cursor, ':bv'||(l_hdr.no_fields+1), g_process_time ); -- Dede+, Jkt, 20091109_1

                if ( dbms_sql.execute( g_cursor ) <> 1 )
                then
                    raise_application_error( -20001,
                                 'Insert failed ' || sqlerrm );
                end if;
            end if;
        end loop;
    end if;
    dbms_lob.fileclose( l_bfile );
    if ( dbms_lob.isopen( f_bfile ) > 0 ) then
      dbms_lob.fileclose( f_bfile );
    end if;
exception
    when others then
        dbms_output.put_line('something error!!!');
        dbms_output.put_line(l_row(1)||' '||l_row(2));
        dbms_output.put_line(l_row(32));
        dbms_output.put_line(l_row(n_dummy));
        if ( dbms_lob.isopen( l_bfile ) > 0 ) then
            dbms_lob.fileclose( l_bfile );
        end if;
        if ( dbms_lob.isopen( f_bfile ) > 0 ) then
            dbms_lob.fileclose( f_bfile );
        end if;
        RAISE;
end;
end dbase_fox;
/