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  |
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 #434651 is a reply to message #434642] |
Thu, 10 December 2009 02:19  |
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 |
 |
Michel Cadot
Messages: 68641
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
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 01:51:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
