Swadhin Ray
asked on
export text or csv or excel from oracle
Hello Experts,
I need help in modifying the current function on my database like:
1) This function is able to generate text , csv , xls file from oracle database. But heading are missing where I also need to export the headings.
2) Attached the screen shot for the way required while exporting the text file from this function. As this function is generic , so need to add the logic for line terminator when the file has to be text file only not for csv and xls should be "Windows <CR><LF>" :
3) Issue while exporting in xls file:
When I execute the block for xls file then the data are been exported all on one column as like below:
Function code is as below:
I need help in modifying the current function on my database like:
1) This function is able to generate text , csv , xls file from oracle database. But heading are missing where I also need to export the headings.
2) Attached the screen shot for the way required while exporting the text file from this function. As this function is generic , so need to add the logic for line terminator when the file has to be text file only not for csv and xls should be "Windows <CR><LF>" :
3) Issue while exporting in xls file:
When I execute the block for xls file then the data are been exported all on one column as like below:
OUTUT:
data1,data2,true
Function code is as below:
CREATE OR REPLACE FUNCTION DUMP_FILE ( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
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;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query,
dbms_sql.native );
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;
dbms_sql.define_column( l_theCursor, 1,
l_columnValue, 2000 );
l_status := dbms_sql.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
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.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
return l_cnt;
end dump_file;
/
Is this two question?
1: how to export using Toad (or similar GUI)?
2: how to get headers in the output file using your UTL_FILE function?
To get the headers using your UTL_FILE function, just use another UTL_FILE.PUT_LINE with the header text outside the loop.
1: how to export using Toad (or similar GUI)?
2: how to get headers in the output file using your UTL_FILE function?
To get the headers using your UTL_FILE function, just use another UTL_FILE.PUT_LINE with the header text outside the loop.
chubby_informer,
Interesting observation: That is the first link in Google if I use the exact question subject :export text or csv or excel from oracle
Interesting observation: That is the first link in Google if I use the exact question subject :export text or csv or excel from oracle
ASKER
@slightwv : I am only looking for an solution from PLSQL function not from Toad or other GUI.
As you said using UTL_FILE.PUT_LINE ??
Here how I can add the columns that will be there on my sql query ?
Here is my PLSQL block for how I am executing this function for csv file:
So the function should be modified in such a way that it should dynamically take the columns for what ever the query I am passing it through my PLSQL BLOCK .
As you said using UTL_FILE.PUT_LINE ??
Here how I can add the columns that will be there on my sql query ?
Here is my PLSQL block for how I am executing this function for csv file:
DECLARE
l_rows NUMBER;
BEGIN
L_ROWS := DUMP_FILE( 'select * from emp', ',', 'MY_DIR', 'test.csv' );
dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END;
So the function should be modified in such a way that it should dynamically take the columns for what ever the query I am passing it through my PLSQL BLOCK .
I've not done much with DBMS_SQL but my guess is the for loop making calls to dbms_sql.column_value with a value of l_columnValue is the actual column name?
If so, declare a varchar2 variable and build the header record in that loop.
Something like:
header_record := header_record || l_columnValue || ',';
then outside the loop, removethe trailing ',':
header_record := rtrim(header_record,',');
If so, declare a varchar2 variable and build the header record in that loop.
Something like:
header_record := header_record || l_columnValue || ',';
then outside the loop, removethe trailing ',':
header_record := rtrim(header_record,',');
ASKER
I have changed the function a bit to get the headers like as below code, but the result is not getting pupolated properly .
Here is the output I get after calling this function:
OUTPUT:
EMPLOYEE_ID|FIRST_NAME|LAS T_NAME|SAL ARY|100|St even|King| 24000
101|Neena|Kochhar|17000
102|Lex|De Haan|17000
103|Alexander|Hunold|9000
After salary the values should come in next line but it is not coming.
Need help in fixing this.
Function Code:
Here is the output I get after calling this function:
DECLARE
l_rows NUMBER;
BEGIN
L_ROWS := DUMP_FILE( 'select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from EMP', '|', 'ORA_DIR', 'test.txt' );
dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END;
OUTPUT:
EMPLOYEE_ID|FIRST_NAME|LAS
101|Neena|Kochhar|17000
102|Lex|De Haan|17000
103|Alexander|Hunold|9000
After salary the values should come in next line but it is not coming.
Need help in fixing this.
Function Code:
CREATE OR REPLACE
FUNCTION DUMP_FILE(
p_query IN VARCHAR2,
p_separator IN VARCHAR2 DEFAULT ',',
p_dir IN VARCHAR2 ,
p_filename IN VARCHAR2 )
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_descTbl dbms_sql.desc_tab;
BEGIN
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
--dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
FOR i IN 1 .. 255
LOOP
BEGIN
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '|' );
EXCEPTION
WHEN OTHERS THEN
IF ( SQLCODE = -1007 ) THEN
EXIT;
ELSE
raise;
END IF;
END;
END LOOP;
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
l_status := dbms_sql.execute(l_theCursor);
LOOP
EXIT
WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
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.new_line( l_output );
l_cnt := l_cnt+1;
END LOOP;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
RETURN l_cnt;
END dump_file;
DBMS_OUTPUT.PUT does not add a newline. DBMS_OUTPUT.PUTLINE does.
ASKER
I dont see anything on DBMS_OUTPUT.PUT on my code but on "UTL_FILE.PUT " when replaced to UTL_FILE.PUT_LINE then I get the below result on my output file :
EMPLOYEE_ID|
FIRST_NAME|
LAST_NAME|
SALARY|
100|
Steven|
King|
24000
101|
Neena|
Kochhar|
17000
EMPLOYEE_ID|
FIRST_NAME|
LAST_NAME|
SALARY|
100|
Steven|
King|
24000
101|
Neena|
Kochhar|
17000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot slightwv I missed to put the put_line after headers loop.
Here is the final code which is giving me the perfect result :
PLSQL block to run this:
Here is the final code which is giving me the perfect result :
create or replace
FUNCTION DUMP_FILE(
p_query IN VARCHAR2,
p_separator IN VARCHAR2 DEFAULT ',',
p_dir IN VARCHAR2 ,
p_filename IN VARCHAR2 )
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_descTbl dbms_sql.desc_tab;
BEGIN
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
--dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
FOR i IN 1 .. 255
LOOP
BEGIN
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '|' );
EXCEPTION
WHEN OTHERS THEN
IF ( SQLCODE = -1007 ) THEN
EXIT;
ELSE
raise;
END IF;
END;
END LOOP;
[b]utl_file.new_line( l_output ); -- Missed to put here [/b]
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
l_status := dbms_sql.execute(l_theCursor);
LOOP
EXIT
WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
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.new_line( l_output );
l_cnt := l_cnt+1;
END LOOP;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
RETURN l_cnt;
END dump_file;
PLSQL block to run this:
DECLARE
l_rows NUMBER;
BEGIN
L_ROWS := DUMP_FILE( 'select * from emp', '|', 'ORA_DIR', 'final.txt' );
dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END;
ASKER
Thanks a ton ...
ASKER
OUTPUT:
data1,data2,True,