Home » SQL & PL/SQL » SQL & PL/SQL » text files with column names
text files with column names [message #297148] Wed, 30 January 2008 06:05 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
With utl_file,ican generate text files.
But these text files does not contain the column names at the top.
How to generate text files with column names.
for ex,something like:

a,b,c,d
1,2,3,4----row1
5,6,7,8----row2

Re: text files with column names [message #297149 is a reply to message #297148] Wed, 30 January 2008 06:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add the results of a query on (user/all/dba)_tab_columns, or, if you construct your query by selecting not all columns, just construct the line yourself
Re: text files with column names [message #297151 is a reply to message #297148] Wed, 30 January 2008 06:17 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Can you ellaborate a bit more?

i also tried to use col sep

IT works but the column names keep coming up after some records.
There are thousands of records in table.What linesize/pagesize should i set so that column names come only once?
Re: text files with column names [message #297152 is a reply to message #297151] Wed, 30 January 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you post the code we can elaborate a little bit more.
What's the relation bewteen utl_file and colsep, linesize or pagesize?

Regards
Michel

[Updated on: Wed, 30 January 2008 06:24]

Report message to a moderator

Re: text files with column names [message #297154 is a reply to message #297148] Wed, 30 January 2008 06:29 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
THis is one way.
SQL> set colsep ,
SQL>spool e:\file.txt
SQL>select * from a;
spool off

There are many records in a.
I want column names to come only once.

2nd way
How to use utl_file for the same?

3rd way
What Frank recommended but i can't understand it fully.
Re: text files with column names [message #297158 is a reply to message #297154] Wed, 30 January 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ set pagesize 50000
If there are more lines you can't have 1 and only 1 header line.

2/ Use all_tab_columns to get column names and utl_file them

3/ This is what Frank said

Regards
Michel
Re: text files with column names [message #297171 is a reply to message #297154] Wed, 30 January 2008 07:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this out.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056

Regards

Raj
Re: text files with column names [message #297188 is a reply to message #297148] Wed, 30 January 2008 09:32 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
Use all_tab_columns to get column names and utl_file them


How to?
Re: text files with column names [message #297195 is a reply to message #297188] Wed, 30 January 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to?

The same way you do with your own data.

Regards
Michel
Re: text files with column names [message #297211 is a reply to message #297148] Wed, 30 January 2008 12:16 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
DECLARE
  FILE1  UTL_FILE.FILE_TYPE;
BEGIN
  
FILE1=UTL_FILE.FOPEN('DIR','file.txt','w');
  
  FOR C IN (SELECT C1,
                   C2,
                   C3
            FROM   A)
  LOOP
    UTL_FILE.PUT_LINE(FILE1,C.C1,C.C2,C.C3);
  END LOOP;
  
  UTL_FILE.FCLOSE(FILE1);
END;


Now,where should i
Quote:
Use all_tab_columns to get column names and utl_file them
?

The above link uses collections and i am not familiar with them.
Moreover,it uses some advance built ins that would take time to understand.

[Updated on: Wed, 30 January 2008 12:17]

Report message to a moderator

Re: text files with column names [message #297213 is a reply to message #297211] Wed, 30 January 2008 12:22 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
check this out.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056
Regards
Raj

Did you check that link ? In the above link search for the keyword "describe_columns". It explains clearly how to do it with a working example.

Regards

Raj

[Updated on: Wed, 30 January 2008 12:22]

Report message to a moderator

Re: text files with column names [message #297214 is a reply to message #297211] Wed, 30 January 2008 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
UTL_FILE.PUT_LINE(FILE1,C.C1,C.C2,C.C3);

This is syntaxically wrong.
You have to at least read the documentation about the package.

As you know the column names, why not:
utl_file.put_line('C1 C2 C3');

Regards
Michel
Re: text files with column names [message #297222 is a reply to message #297148] Wed, 30 January 2008 13:22 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
DECLARE
  FILE1  UTL_FILE.FILE_TYPE;
   COL1   VARCHAR2(10);
BEGIN
  FILE1 := UTL_FILE.FOPEN('P_DIR','file.txt','w');
  
  FOR V IN (SELECT *
            FROM   ALL_TAB_COLS
            WHERE  TABLE_NAME = 'A')
  LOOP
    COL1 := COL1
            ||','
            ||V.COLUMN_NAME;
  END LOOP;
  
  UTL_FILE.PUT_LINE(FILE1,SUBSTR(COL1,2));
  
  FOR C IN (SELECT *
            FROM   A)
  LOOP
    UTL_FILE.PUT_LINE(FILE1,C.C1
                            ||','
                            ||C.C2
                            ||','
                            ||C.C3
                            ||','
                            ||C4);
  END LOOP;
  
  UTL_FILE.FCLOSE(FILE1);
END;


Frank, I am sorry for understanding your point too late.
Michel,you are right.I wrote that code in the message box.
The tom kyte link is too complex.
Whats the need to make things complex when we can do it in simpler ways.
Re: text files with column names [message #297315 is a reply to message #297222] Thu, 31 January 2008 02:06 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should add an order by to the columns-query.
Otherwise the columns will be returned in random order. Even if you THINK the output is correct now, it might change anytime.
Previous Topic: Need to skip unique constraint
Next Topic: to_char function
Goto Forum:
  


Current Time: Sun Dec 04 00:38:56 CST 2016

Total time taken to generate the page: 0.09648 seconds