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
<>
<< Begins >>
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;
<< Ends>>
<< Package Body
<>
<< Begins >>
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;
<< Ends>>
Fig 2: Package

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

By
Karkuvelraja T
  • January 2, 2017 | 21 views
  • Comments