Home » RDBMS Server » Server Utilities » Export table to .csv file (oracle 10g)
Export table to .csv file [message #434639] Thu, 10 December 2009 01:18 Go to next message
mnair
Messages: 5
Registered: January 2009
Junior Member
Hi,
I need to export data from a table with the following structure.which contains date columns. But the date is stored as "no: of milliseconds from jan1,1971" in the table. The table structure is

Revision_id number;
actual_start_time number;
actual_end_time number;
Name varchar2;

The columns actual_start_time and actual_end_time are actually date columns. But the date is stord as no: of milliseconds from jan1,1971.As a result, these columns have been created as number columns, though they store date values.

when I export this data into a .csv file, I want to get actual_start_time and actual_end_time in date format(dd/mm/yyyy).

I am using the following procedure to export to .csv

create or replace PROCEDURE TEST_FILE_WRITE_MOD(p_sql IN VARCHAR2,
p_filename IN VARCHAR2) IS

v_finaltxt VARCHAR2(4000);
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB; --Pl/sql collection based on the DBMS_SQL.DESC_TAB collection type
col_num NUMBER;
v_fh UTL_FILE.FILE_TYPE;


BEGIN
c := DBMS_SQL.OPEN_CURSOR;
--Parse the query
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);


--Retrieve column information
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
--Define each of the column names
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
END CASE;
END LOOP;
-- This part outputs the HEADER
v_fh := UTL_FILE.FOPEN('UTL_FILE_DIR',p_filename,'w',32767);
FOR j in 1..col_cnt
LOOP
v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
END LOOP;


UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
UTL_FILE.NEW_LINE(v_fh);
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
v_finaltxt := NULL;
FOR j in 1..col_cnt
LOOP
--Retrieve each column value
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
ELSE
v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
END CASE;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
END LOOP;
UTL_FILE.FCLOSE(v_fh);
--clean up
DBMS_SQL.CLOSE_CURSOR(c);
END;



When I export the data to a csv file, I want the actual_start time and actual_end_time in date format. But i am getting the date as milliseconds only.

Please help
Re: Export table to .csv file [message #434642 is a reply to message #434639] Thu, 10 December 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?hl=en&source=hp&q=unix+to+oracle+date&aq=0&oq=unix+to+orac&aqi=g1g-m1

Results 1 - 10 of about 4,640,000 for unix to oracle date. (0.10 seconds)

Regards
Michel

[Updated on: Thu, 10 December 2009 01:29]

Report message to a moderator

Re: Export table to .csv file [message #434651 is a reply to message #434642] Thu, 10 December 2009 02:19 Go to previous messageGo to next message
mnair
Messages: 5
Registered: January 2009
Junior Member
But I am not sure how to use this conversion with dbms_sql.column_value in this procedure. Actually we have an user defined function called nt which will convert milliseconds to date format. But I am not sure how to use it in the following procedure to get the date.

I tried modifying the following code

WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');


as

WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
v_finaltxt := ltrim(v_finaltxt||','||to_char(nt(v_n_val),'DD/MM/YYYY HH24:MI:SS'),',');

But still I am getting the date as milliseconds only in .csv
Re: Export table to .csv file [message #434653 is a reply to message #434651] Thu, 10 December 2009 02:24 Go to previous message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But still I am getting the date as milliseconds only in .csv

If you really do:
Quote:
to_char(nt(v_n_val),'DD/MM/YYYY HH24:MI:SS'),

I do no trust you, how Oracle could convert the date string you pass to a number of miiliseconds, you did not do what you think you did.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Multiple table insert from single data file
Next Topic: Export Error
Goto Forum:
  


Current Time: Tue Sep 27 02:35:19 CDT 2016

Total time taken to generate the page: 0.10580 seconds