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
Karkuvelraja T
No comments:
Post a Comment