Introduction
The customer asks us to generate a database file format report in oracle directory itself; from there customer will download the report.
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
Summary
This Post described procedure will help us to generate Database File (DBF) in oracle directory through PL/SQL.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
