Skip to main content

Generate DBF file in Oracle Directory Using Oracle PL/SQL


Objective:

To generate Database File (DBF) in Oracle Directory using Oracle PL/SQL.

Scenario:

Customer asks us to generate database file format report in oracle directory itself; from there customer will download the report. 

Solution:

Step 1: Call the PL/SQL procedure to generate DBF report in oracle directory.


Step 2: Package sssl_dbase_pkg_clob.

<< Package Specification <>

CREATE OR REPLACE PACKAGE sssl_dbase_pkg_clob
AS
  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);
  PROCEDURE dump_table(
      p_dir          IN VARCHAR2,
      p_file         IN VARCHAR2,
      p_tname        IN VARCHAR2,
      p_cnames       IN VARCHAR2 DEFAULT NULL,
      p_where_clause IN VARCHAR2 DEFAULT ' 1=1 ');
END;
/ 

<< Package Body <>

CREATE OR REPLACE PACKAGE BODY sssl_dbase_pkg_clob
AS
  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 VARCHAR2(50), -- number of records in file,
    hdr_len    VARCHAR2(40), -- length of header, 2 byte int
    rec_len    VARCHAR2(40), -- number of bytes in record,
    no_fields  INT           -- number of fields
  );
TYPE field_descriptor
IS
  record
  (
    NAME   VARCHAR2(50),
    fname  VARCHAR2(50),
  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(32767) INDEX BY binary_integer;
  g_cursor binary_integer DEFAULT dbms_sql.open_cursor;
FUNCTION ite(
    tf  IN boolean,
    yes IN VARCHAR2,
    NO  IN VARCHAR2 )
  RETURN VARCHAR2
IS
BEGIN
  IF ( tf ) THEN
    RETURN yes;
  ELSE
    RETURN NO;
  END IF;
END ite;
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;

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 ) );
  dbms_output.put_line( p_hdr.VERSION );
  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, 6, 4 ) );
  p_hdr.hdr_len    := to_int( substr( l_data, 9, 3 ) );
  p_hdr.rec_len    := to_int( substr( l_data, 11, 3 ) );
  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     := rtrim(substr(l_data,1,11),chr(0));
    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 );
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 || ') 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;
  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 )
  RETURN rowarray
IS
  l_data VARCHAR2(4000);
  l_row rowarray;
  l_n NUMBER DEFAULT 2;
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;
    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 ',';
PROCEDURE p(
    p_str IN VARCHAR2)
IS
  l_str LONG DEFAULT p_str;
BEGIN
  while( l_str IS NOT NULL )
  loop
    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;
    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||','|| p_flds(i).decimals || ')' ||
        l_sep );
      ELSE
        p( 'number('||p_flds(i).LENGTH||')'||l_sep );
      END IF;
    ELSE
      p( 'varchar2(' || p_flds(i).LENGTH || ')'||l_sep);
    END IF;
  END loop;
  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;
  l_offset NUMBER DEFAULT 1;
  l_hdr dbf_header;
  l_flds field_descriptor_array;
  l_row rowarray;
BEGIN
  l_bfile := bfilename( p_dir, p_file );
  dbms_lob.fileopen( l_bfile );
  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_sql.parse( g_cursor, build_insert(p_tname,p_cnames,l_flds),
    dbms_sql.NATIVE );
    FOR i IN 1 .. l_hdr.no_records
    loop
      l_row         := get_row( l_bfile, l_offset, l_hdr, l_flds );
      IF ( l_row(0) <> '*' ) -- deleted record
        THEN
        FOR i IN 1..l_hdr.no_fields
        loop
          dbms_sql.bind_variable( g_cursor, ':bv'||i, l_row(i), 4000 );
        END loop;
        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 );
exception
WHEN others THEN
  IF ( dbms_lob.isopen( l_bfile ) > 0 ) THEN
    dbms_lob.fileclose( l_bfile );
  END IF;
  raise;
END;

PROCEDURE put_header(
    p_tname  IN VARCHAR2,
    p_cnames IN VARCHAR2 DEFAULT NULL,
    l_hdr    IN out dbf_header,
    vflds    IN out field_descriptor_array)
IS
  v_value_list strtabletype;
  vcursor VARCHAR2(4000);
TYPE rc
IS
  REF
  CURSOR;
    col_cur rc;
    i     INTEGER:=0;
    l_cnt NUMBER;
  BEGIN
    BEGIN
      SELECT
        count(*)
      INTO
        l_cnt
      FROM
        user_tab_cols
      WHERE
        table_name=upper(p_tname);
  END;
  IF l_cnt       > 0 THEN
    IF p_cnames IS NOT NULL THEN
      vcursor   :=
      'select substr(column_name,1,12),                     
case data_type                     
when ''DATE'' then ''D''                     
when ''NUMBER'' then ''N''                    
else ''C'' end ,                     
case data_type                     
when ''NUMBER'' then NVL(data_precision,22)                     
when ''DATE'' then 8                     
else data_length end,                     
case data_type                     
when ''NUMBER'' then data_scale                    
end ,                     
column_name   from user_tab_cols                
where column_name IN (select * from TABLE (cast(str2tbl(UPPER('''
      ||p_cnames||'''))
as strTableType)))                
and   table_name='''
      ||upper(p_tname)||'''                
order by column_id';
    ELSE
      vcursor:=
      'select SUBSTR(column_name,1,12),                     
case data_type                     
when ''DATE'' then ''D''                     
when ''NUMBER'' then ''N''                    
else ''C'' end ,                     
case data_type                     
when ''NUMBER'' then NVL(data_precision,22)                     
when ''DATE'' then 8                     
else data_length end,                     
case data_type                     
when ''NUMBER'' then data_scale                    
end ,                     
column_name              
from user_tab_cols              
where table_name='''
      ||upper(p_tname)||'''              
order by column_id';
    END IF;
  ELSE
    IF p_cnames IS NOT NULL THEN
      vcursor   :=
      'select substr(column_name,1,12),                     
case data_type                     
when ''DATE'' then ''D''                     
when ''NUMBER'' then ''N''                    
else ''C'' end ,                     
case data_type                     
when ''NUMBER'' then NVL(data_precision,22)                     
when ''DATE'' then 8                     
else data_length end,                     
case data_type                     
when ''NUMBER'' then data_scale                    
end ,                     
column_name   from ALL_TAB_COLUMNS                
where column_name IN (select * from TABLE (cast(str2tbl(UPPER('''
      ||p_cnames||'''))
as strTableType)))                
and   table_name='''
      ||upper(p_tname)||'''                
order by column_id';
    ELSE
      vcursor:=
      'select SUBSTR(column_name,1,12),                     
case data_type                     
when ''DATE'' then ''D''                     
when ''NUMBER'' then ''N''                    
else ''C'' end ,                     
case data_type                     
when ''NUMBER'' then NVL(data_precision,22)                     
when ''DATE'' then 8                     
else data_length end,                     
case data_type                     
when ''NUMBER'' then data_scale                    
end ,                     
column_name              
from ALL_TAB_COLUMNS              
where table_name='''
      ||upper(p_tname)||'''              
order by column_id';
    END IF;
  END IF;
  OPEN col_cur FOR vcursor;
  loop
    i:=i+1;
    fetch
      col_cur
    INTO
      vflds(i).NAME,
      vflds(i).TYPE,
      vflds(i).LENGTH,
      vflds(i).decimals,
      vflds(i).fname;
    exit
  WHEN col_cur%notfound;
  END loop;
  CLOSE col_cur;
  l_hdr.VERSION   :='03';
  l_hdr.YEAR      :=to_number(to_char(SYSDATE,'yyyy'))-1900;
  l_hdr.MONTH     :=to_number(to_char(SYSDATE,'mm'));
  l_hdr.DAY       :=to_number(to_char(SYSDATE,'dd'));
  l_hdr.rec_len   :=1; -- to be set later
  l_hdr.no_fields :=vflds.count;
  l_hdr.hdr_len   :=to_char((l_hdr.no_fields*32)+33,'FM000x');
END;

PROCEDURE put_rows(
    p_tname        IN VARCHAR2,
    p_where_clause IN VARCHAR2 DEFAULT '1=1 ',
    vrow           IN out rowarray,
    vflds          IN field_descriptor_array)
IS
TYPE rc
IS
  REF
  CURSOR;
    cur rc;
    i INTEGER:=0;
    vselectlist CLOB;--VARCHAR2(32767);
    v_cur CLOB;      --VARCHAR2(32767);
    v_cnt NUMBER := 0;
  BEGIN
    FOR l IN 1..vflds.count
    loop
      v_cnt         := v_cnt+1;
      IF v_cnt       =1 THEN
        vselectlist := vselectlist||ite(l!=1,'||','')||
        'to_clob(utl_raw.cast_to_raw(rpad(NVL(TRIM('||
        CASE
        WHEN vflds(l).TYPE='N' THEN
          'to_char(' || vflds(l).fname||')'
        END ||
        CASE
        WHEN vflds(l).TYPE='N' THEN
          ')'
        END ||
        CASE
        WHEN vflds(l).TYPE='D' THEN
          'to_char('||vflds(l).fname||','||chr(39)||'yyyymmdd'||chr(39)||')'
        END||
        CASE
        WHEN vflds(l).TYPE='D' THEN
          ')'
        END ||
        CASE
        WHEN vflds(l).TYPE='C' THEN
          vflds(l).fname||')'
        END ||','' ''),'||vflds(l).LENGTH||','' '')))';
      ELSE
        vselectlist := vselectlist||ite(l!=1,'||','')||
        'utl_raw.cast_to_raw(rpad(NVL(TRIM('||
        CASE
        WHEN vflds(l).TYPE='N' THEN
          'to_char(' || vflds(l).fname||')'
        END ||
        CASE
        WHEN vflds(l).TYPE='N' THEN
          ')'
        END ||
        CASE
        WHEN vflds(l).TYPE='D' THEN
          'to_char('||vflds(l).fname||','||chr(39)||'yyyymmdd'||chr(39)||')'
        END||
        CASE
        WHEN vflds(l).TYPE='D' THEN
          ')'
        END ||
        CASE
        WHEN vflds(l).TYPE='C' THEN
          vflds(l).fname||')'
        END ||','' ''),'||vflds(l).LENGTH||','' ''))';
      END IF;
      v_cnt := NULL;
    END loop;
    v_cur:='select '||vselectlist||' from '||p_tname||' where '||p_where_clause
    ;
    dbms_output.put_line (v_cur);
    OPEN cur FOR v_cur;
    loop
      i:=i+1;
      fetch
        cur
      INTO
        vrow(i);
    exit
  WHEN cur%notfound;
  END loop;
  CLOSE cur;
END;

PROCEDURE dump_table(
    p_dir          IN VARCHAR2,
    p_file         IN VARCHAR2,
    p_tname        IN VARCHAR2,
    p_cnames       IN VARCHAR2 DEFAULT NULL,
    p_where_clause IN VARCHAR2 DEFAULT ' 1=1 ')
IS
  l_hdr dbf_header;
  vflds field_descriptor_array;
  vrow rowarray;
  v_outputfile utl_file.file_type;
  vcount     INT;
  vstarttime DATE;
  vendtime   DATE;
  v_cnt      NUMBER:=20;
BEGIN
  dbms_output.ENABLE(1000000);
  vstarttime:=SYSDATE;
  put_header(p_tname,p_cnames,l_hdr,vflds);
  put_rows(p_tname,p_where_clause,vrow,vflds);
  v_outputfile := utl_file.fopen(p_dir,p_file,'wb',32767);
  FOR i IN 1..vflds.count
  loop
    l_hdr.rec_len:=l_hdr.rec_len+vflds(i).LENGTH;
  END loop;
  l_hdr.rec_len    :=to_char(to_number(l_hdr.rec_len),'FM000x');
  l_hdr.rec_len    :=substr(l_hdr.rec_len,-2)|| substr(l_hdr.rec_len,1,2);
  l_hdr.no_records :=to_char(vrow.count,'FM0000000x');
  l_hdr.no_records :=substr(l_hdr.no_records,-2)|| substr(l_hdr.no_records,5,2)
  || substr(l_hdr.no_records,3,2)|| substr(l_hdr.no_records,1,2);
  l_hdr.hdr_len:=substr(l_hdr.hdr_len,-2)|| substr(l_hdr.hdr_len,1,2);
  utl_file.put_raw(v_outputfile, rpad(l_hdr.VERSION||to_char(l_hdr.YEAR,'FM0x')
  ||to_char(l_hdr.MONTH,'FM0x')|| to_char(l_hdr.DAY,'FM0x')||l_hdr.no_records||
  l_hdr.hdr_len|| l_hdr.rec_len,64,'0'));
  FOR i IN 1..vflds.count
  loop
    utl_file.put_raw(v_outputfile,utl_raw.cast_to_raw(to_clob(vflds(i).NAME))||
    REPLACE(rpad('00',12-LENGTH(to_clob(vflds( i).NAME)),'#'),'#','00')||
    utl_raw.cast_to_raw(to_clob(vflds(i).TYPE))||'00000000'|| to_char(vflds(i)
    .LENGTH,'FM0x')||'000000000000000000000000000000');
  END loop;
  utl_file.put_raw(v_outputfile,'0D');
  FOR i IN 1..vrow.count
  loop
    IF ( mod(i,10) = 0 ) THEN
      utl_file.fflush(v_outputfile);
      utl_file.fclose( v_outputfile);
      v_outputfile := utl_file.fopen(p_dir,p_file,'AB',32767);
      IF (i         = 10) THEN
        utl_file.put_raw(v_outputfile,'20'||vrow(i),TRUE);
      ELSE
        utl_file.put_raw(v_outputfile,'20'||vrow(i),TRUE);
      END IF;
    ELSE
      utl_file.put_raw(v_outputfile,'20'||vrow(i),TRUE);
    END IF;
  END loop;
  utl_file.put_raw(v_outputfile,'20',TRUE);
  IF utl_file.is_open(v_outputfile ) THEN
    utl_file.fclose(v_outputfile);
  END IF;
  vendtime:=SYSDATE;
END;
END;
/ 

Conclusion: This procedure will help us to generate Database File (DBF) in oracle directory through PL/SQL.


Comments

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Oracle Application Express Views (APEX)

Application Express Views Search SELECT * FROM apex_dictionary WHERE column_id = 0; View Comment Parent View APEX_APPLICATIONS Applications defined in the current workspace or database user. APEX_WORKSPACES APEX_APPLICATION_ALL_AUTH All authorization schemes for all components by Application APEX_APPLICATIONS ...
Post a Comment
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  ...
9 comments
Read more