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

Friendly URL: Redirect to Different Page after Login in Oracle APEX 20.1

Oracle has updated apex.oracle.com to APEX 20.1 which includes among other features the new Friendly URL option. Here i am giving an example to redirect to different page after login in Oracle APEX 20.1 [Friendly URL Enabled] Step 1: Define home page for each user in emp master table as below Step 2: To enable Friendly URL Syntax, follow below steps, 1) On the Workspace home page, click the App Builder icon. 2) Select an application (The Application home page appears). 3) From Application home page, you can access the Definition page in TWO ways: Click the Edit Application Properties button. From Shared Components:              1) Click Shared Components .              2) Under Application Logic, click Application Definition Attributes . The Definition page appears. 4) Under Properties, configure the Friendly URL s attribute: Click Apply Changes to save your ch...
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