CREATING A DIRECTORY FOR GENERATE FILE
=============================================================================================
CREATE OR REPLACE DIRECTORY
FROM_DIR AS
'D:\FILE\';
CREATING A FUNCTION FOR GENERATE FILE
=============================================================================================
CREATE OR REPLACE function DFU_GENERATE_FILE_FROM_DATA( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2,
p_header IN VARCHAR2,
p_endline IN VARCHAR2 DEFAULT chr(13))
/******************* USING FORMATE *******************************
*DECLARE *
* *
* l_var varchar2(4000); *
* *
* BEGIN *
* *
* l_var := DFU_GENERATE_FILE_FROM_DATA( 'SELECT * FROM SLOGONIF', *
* ',', *
* 'FROM_DIR' , *
* 'RAJIB.CSV', *
* ' ', *
* ' ' ); *
* *
* END; *
*****************************************************************/
-- This routine makes certain assumptions.
-- 1) There must be a query and it can't be greater then 32K.
-- 2) The separator must only be one character in length and can't be
-- a CR, LF, binary 0, or null (easy to change).
-- 3) If the p_dir parameter is null, the p_filename must contain the
-- path and filename (/tmp/output.txt)
-- 4) If the p_header parameter is not null, then insert it into the first
-- row of the output file. If the p_separator parameter is not a comma,
-- the comma's in the header string will be replaced with the new
-- separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
-- the separator is a tab, what is put into the file would be
-- 'NAME<tab>FIRST_NAME<tab>LAST_NAME'
-- 5) The value of p_endline will be appended to the end of each line of the
-- output file. It can be used to add a carriage return before the
-- Line Feed is inserted by the NEW_LINE (unix). If the server is running
-- on a windows machine, set this to null since the NEW_LINE will save
-- a CR,LF pair anyway. This can also be used if you needed to put
-- something at the end. For exanple "'|'||CHR(13)" which would put a
-- vertical bar and CR,LF on each line on a unix machine.
--
-- The following are the returned error codes
-- -1 The query is empty
-- -2 The output filename is empty
-- -3 The separator is invalid.
-- -4 The filename only contains the path, no filename specified.
-- -5 The output file can not be opened.
-- -6 The query could not be parsed. It was illegal.
-- 0 The query returned NO records.
-- >0 The number of records returned.
return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_dir VARCHAR2(500);
l_filename VARCHAR2(32);
x_pnt NUMBER(4);
l_header VARCHAR2(2000);
begin
-- sanity check the input
IF p_query IS NULL THEN
RETURN(-1);
END IF;
IF p_filename IS NULL THEN
RETURN(-2);
END IF;
-- Do not allow CR, LF,binary 0, or null to be used as a separator.
-- The length of the separator must be 1 if it exists.
IF p_separator IS NULL OR
p_separator IN (chr(13),chr(10),chr(0)) OR
length(p_separator) > 1 THEN
RETURN(-3);
END IF;
-- If the directory parameter is blank, assume that the directory
-- is included in the filename.
IF p_dir IS NOT NULL THEN
l_dir := p_dir;
l_filename := p_filename;
ELSE
x_pnt := instr(p_filename,'/',-1,1);
-- If no path is specified or no filename is specified,
-- the procedure will not work... get out.
IF x_pnt = 0 OR x_pnt = length(p_filename) THEN
RETURN(-4);
END IF;
l_dir := substr(p_filename,1,x_pnt-1);
l_filename := substr(p_filename,x_pnt+1);
END IF;
-- Check to see if the file can be opened. If ANY error is
-- encountered, exit with a count of -1;
BEGIN
l_output := utl_file.fopen( l_dir, l_filename, 'w', 32767 );
EXCEPTION
WHEN OTHERS THEN
RETURN(-5);
END;
-- Check to see if the query can be processed. if ANY error is
-- encountered, close the output file and exit.
BEGIN
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose( l_output );
RETURN(-6);
END;
-- If the p_header parameter is not null, then insert the line as
-- the first line in the output file. This is used if the user wants
-- to insert column headings. Make sure to use a comma in your header
-- line and the routine will replace all comma;s with the specified
-- separator.
l_header := NULL;
IF p_header IS NOT NULL THEN
l_header := p_header;
IF p_separator <> ',' THEN
l_header := REPLACE(l_header,',',p_separator);
END IF;
END IF;
-- Loop through all the parameters for the select. To support
-- unknown querys, the assumption is that the query will return
-- all columns as varchar2 columns where the data is correctly
-- formatted for inport. A maximum of 255 columns are supported
-- in the query. Each column can't be greater then 2000
-- characters in length.
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
-- This define_column insures that at least one column is defined for the
-- routine.
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
-- Fire the query.
l_status := dbms_sql.execute(l_theCursor);
-- Loop through all the rows returned by the query. Build up the output file
-- by looping through the defined columns.
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
IF l_cnt = 0 AND l_header IS NOT NULL THEN
utl_file.put(l_output, l_header);
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := 1;
END IF;
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := l_cnt+1;
end loop;
-- Processing done. close the cursor and output file.
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
-- Return the number of rows built in the csv file.
return l_cnt;
-- If Any error occures outside of the errors checked above, then raise
-- and error and blow out the procedure.
EXCEPTION
WHEN OTHERS THEN
RAISE;
end DFU_GENERATE_FILE_FROM_DATA;
/
USEING A FUNCTION FOR GENERATE FILE
=============================================================================================
DECLARE
L_DATA varchar2(4000);
BEGIN
L_DATA := DFU_GENERATE_FILE_FROM_DATA( 'SELECT * FROM EMPLOYEES',
',',
'FROM_DIR' ,
'RAJIB.CSV',
' ',
' ' );
END;
=============================================================================================
CREATE OR REPLACE DIRECTORY
FROM_DIR AS
'D:\FILE\';
CREATING A FUNCTION FOR GENERATE FILE
=============================================================================================
CREATE OR REPLACE function DFU_GENERATE_FILE_FROM_DATA( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2,
p_header IN VARCHAR2,
p_endline IN VARCHAR2 DEFAULT chr(13))
/******************* USING FORMATE *******************************
*DECLARE *
* *
* l_var varchar2(4000); *
* *
* BEGIN *
* *
* l_var := DFU_GENERATE_FILE_FROM_DATA( 'SELECT * FROM SLOGONIF', *
* ',', *
* 'FROM_DIR' , *
* 'RAJIB.CSV', *
* ' ', *
* ' ' ); *
* *
* END; *
*****************************************************************/
-- This routine makes certain assumptions.
-- 1) There must be a query and it can't be greater then 32K.
-- 2) The separator must only be one character in length and can't be
-- a CR, LF, binary 0, or null (easy to change).
-- 3) If the p_dir parameter is null, the p_filename must contain the
-- path and filename (/tmp/output.txt)
-- 4) If the p_header parameter is not null, then insert it into the first
-- row of the output file. If the p_separator parameter is not a comma,
-- the comma's in the header string will be replaced with the new
-- separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
-- the separator is a tab, what is put into the file would be
-- 'NAME<tab>FIRST_NAME<tab>LAST_NAME'
-- 5) The value of p_endline will be appended to the end of each line of the
-- output file. It can be used to add a carriage return before the
-- Line Feed is inserted by the NEW_LINE (unix). If the server is running
-- on a windows machine, set this to null since the NEW_LINE will save
-- a CR,LF pair anyway. This can also be used if you needed to put
-- something at the end. For exanple "'|'||CHR(13)" which would put a
-- vertical bar and CR,LF on each line on a unix machine.
--
-- The following are the returned error codes
-- -1 The query is empty
-- -2 The output filename is empty
-- -3 The separator is invalid.
-- -4 The filename only contains the path, no filename specified.
-- -5 The output file can not be opened.
-- -6 The query could not be parsed. It was illegal.
-- 0 The query returned NO records.
-- >0 The number of records returned.
return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_dir VARCHAR2(500);
l_filename VARCHAR2(32);
x_pnt NUMBER(4);
l_header VARCHAR2(2000);
begin
-- sanity check the input
IF p_query IS NULL THEN
RETURN(-1);
END IF;
IF p_filename IS NULL THEN
RETURN(-2);
END IF;
-- Do not allow CR, LF,binary 0, or null to be used as a separator.
-- The length of the separator must be 1 if it exists.
IF p_separator IS NULL OR
p_separator IN (chr(13),chr(10),chr(0)) OR
length(p_separator) > 1 THEN
RETURN(-3);
END IF;
-- If the directory parameter is blank, assume that the directory
-- is included in the filename.
IF p_dir IS NOT NULL THEN
l_dir := p_dir;
l_filename := p_filename;
ELSE
x_pnt := instr(p_filename,'/',-1,1);
-- If no path is specified or no filename is specified,
-- the procedure will not work... get out.
IF x_pnt = 0 OR x_pnt = length(p_filename) THEN
RETURN(-4);
END IF;
l_dir := substr(p_filename,1,x_pnt-1);
l_filename := substr(p_filename,x_pnt+1);
END IF;
-- Check to see if the file can be opened. If ANY error is
-- encountered, exit with a count of -1;
BEGIN
l_output := utl_file.fopen( l_dir, l_filename, 'w', 32767 );
EXCEPTION
WHEN OTHERS THEN
RETURN(-5);
END;
-- Check to see if the query can be processed. if ANY error is
-- encountered, close the output file and exit.
BEGIN
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose( l_output );
RETURN(-6);
END;
-- If the p_header parameter is not null, then insert the line as
-- the first line in the output file. This is used if the user wants
-- to insert column headings. Make sure to use a comma in your header
-- line and the routine will replace all comma;s with the specified
-- separator.
l_header := NULL;
IF p_header IS NOT NULL THEN
l_header := p_header;
IF p_separator <> ',' THEN
l_header := REPLACE(l_header,',',p_separator);
END IF;
END IF;
-- Loop through all the parameters for the select. To support
-- unknown querys, the assumption is that the query will return
-- all columns as varchar2 columns where the data is correctly
-- formatted for inport. A maximum of 255 columns are supported
-- in the query. Each column can't be greater then 2000
-- characters in length.
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
-- This define_column insures that at least one column is defined for the
-- routine.
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
-- Fire the query.
l_status := dbms_sql.execute(l_theCursor);
-- Loop through all the rows returned by the query. Build up the output file
-- by looping through the defined columns.
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
IF l_cnt = 0 AND l_header IS NOT NULL THEN
utl_file.put(l_output, l_header);
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := 1;
END IF;
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := l_cnt+1;
end loop;
-- Processing done. close the cursor and output file.
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
-- Return the number of rows built in the csv file.
return l_cnt;
-- If Any error occures outside of the errors checked above, then raise
-- and error and blow out the procedure.
EXCEPTION
WHEN OTHERS THEN
RAISE;
end DFU_GENERATE_FILE_FROM_DATA;
/
USEING A FUNCTION FOR GENERATE FILE
=============================================================================================
DECLARE
L_DATA varchar2(4000);
BEGIN
L_DATA := DFU_GENERATE_FILE_FROM_DATA( 'SELECT * FROM EMPLOYEES',
',',
'FROM_DIR' ,
'RAJIB.CSV',
' ',
' ' );
END;
No comments:
Post a Comment