Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve Column Name (Oracle 10g - AIX)
Retrieve Column Name [message #379974] Thu, 08 January 2009 15:17 Go to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
Hi,

We developed a shell script contains oracle sql to retrive the column records from more than 5 tables.

We need to generate the output in .xls format. We are done with that. But here is the problem

We are getting only the results in the ouptut, but not the column name. We should generate the Output with all the Column Names.

How we can overcome this. Any variable/command to be set before executing the query.

Thanks
Prashanth
Re: Retrieve Column Name [message #379976 is a reply to message #379974] Thu, 08 January 2009 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
These are your queries so you give the name of the expressions you return and so you can add these ones as column headers to the file you generate.

Regards
Michel
Re: Retrieve Column Name [message #380234 is a reply to message #379976] Fri, 09 January 2009 11:42 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
Hi

I tried using HEADING ON and alias , but still in the Output file I am not able to find the Column Header.

1) SET HEADING ON

2) I tried using alias as below

select '"'|| bias.mger_id ||'"'||','||'"'|| "MGER"

But niether not getting the header.

Any suggestions pls.

Prashanth


Re: Retrieve Column Name [message #380243 is a reply to message #380234] Fri, 09 January 2009 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is your code you know what you do in it, we don't.

Regards
Michel
Re: Retrieve Column Name [message #380248 is a reply to message #379974] Fri, 09 January 2009 12:36 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Show us the current query and your current output.
Re: Retrieve Column Name [message #380250 is a reply to message #380248] Fri, 09 January 2009 13:02 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
Hi,

Pls find the query

SET TERM OFF FEEDBACK OFF ECHO OFF PAGES 0 HEADING ON lines 500
SPOOL log_file_name.sql
SELECT 'SPOOL bsuitlog_'||to_char(sysdate,'YYYYMMDD_HHMISS')||'.csv' from dual;
SPOOL OFF;
@log_file_name.sql
select '"'|| acctopen.application_id ||'"'||','||'"'|| "APPID"
            to_char(acctopen.application_date,'yyyy-mm-dd HH:MI:SS') ||'"'||','||'"'||
            acctopen.user_type ||'"'||','||'"'||
            acctopen.source_code ||'"'||','||'"'||
            acctprods.product_type ||'"'||','||'"'||
            acctopen.package_type ||'"'||','||'"'||
            acctopen.ce_bucket_id ||'"'||','||'"'||
            applicant.first_name ||'"'||','||'"'||
            applicant.middle_name ||'"'||','||'"'||
            applicant.last_name ||'"'||','||'"'||
            acctopen.bsuit_flag ||'"'||','||'"'||
            acctopen.client_agr_ver||'"'||','||'"'||
            acctopen.CLIENT_MANUAL_VER ||'"'||','||'"'||
            acctopen.ONLINE_MARKET_VER ||'"'||','||'"'||
            acctopen.CASH_EDGE_AGR_VER ||'"'||','||'"'||
            acctopen.PLESS_AGR_VER ||'"'||','||'"'||
            acctopen.CP_ACCT_VER ||'"'||','||'"'||
            acctopen.CP_ACCT_AGR_CPB_VER ||'"'
from kschc.ao_acct_open acctopen,
            kschc.ao_acct_applicant applicant,
            kschc.ao_acct_open_prods acctprods
where acctopen.application_id = applicant.application_id
            and acctopen.application_date >= sysdate - 7
            and acctopen.arrival_type_code  in (6,9,10)
            and acctprods.PRODUCT_TYPE not in (14,17,18,19,153,37,36)
            and acctopen.application_id = acctprods.application_id
            and acctopen.application_id in
            (select distinct application_id from kschc.ao_transaction_hist where status=2)
order by  acctopen.application_date desc;

SPOOL OFF
!rm log_file_name.sql
SET TERM ON FEEDBACK ON ECHO ON


OUTPUT:

"6820","2009-01-09 01:06:15","5","BIAS","2","C","1   ","MANAD","W","BLANTON","2","","Client_Manual_20081216","BIAS_MarketPlace_2011","","PSEsignScroll
ableText_20040521","",""


"6817","2009-01-09 11:48:11","3","BIAS","2","","1   ","GEREE","D","BOURNE","2","","Client_Manual_20081216","BIAS_MarketPlace_20011","CEScrollableTe
xt_20071220","Sbcdenote_20040521","",""


Required Output:

APPLID  APPDATE NAME  ID CODE
----------------------------------------------
"6820","2009-01-09 01:06:15","5","BIAS","2","C","1   ","MANAD","W","BLANTON","2","","Client_Manual_20081216","BIAS_MarketPlace_2011","","PSEsignScroll
ableText_20040521","",""


Thanks
Prashanth
Re: Retrieve Column Name [message #380251 is a reply to message #380250] Fri, 09 January 2009 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pagesize 0 => no heading

Regards
Michel
Re: Retrieve Column Name [message #380252 is a reply to message #380251] Fri, 09 January 2009 13:21 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
I didnt get what u mean?

Do you want to Increase Page size and remove No Heading.
Re: Retrieve Column Name [message #380253 is a reply to message #380252] Fri, 09 January 2009 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should read the documentation.
If you set pagesize to 0 then heading is set to OFF (even if you set it to ON).

Regards
Michel
Re: Retrieve Column Name [message #380255 is a reply to message #380253] Fri, 09 January 2009 14:06 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
I tried all the Options but nopes...I am not getting the Column Header.
Re: Retrieve Column Name [message #380256 is a reply to message #380255] Fri, 09 January 2009 14:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course, you basically select only one column.

You concatenate all the table columns into one result column, so there is no way for oracle to give you any meaningful column headers.

That "create excel file" stuff has already been answered a gazillion times, so just search for it.
Re: Retrieve Column Name [message #380258 is a reply to message #380255] Fri, 09 January 2009 14:49 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 08 January 2009 22:28
These are your queries so you give the name of the expressions you return and so you can add these ones as column headers to the file you generate.

Regards
Michel


Previous Topic: Trying to create fact table and getting error:unable to extend temp segment [merged]
Next Topic: SQL Updating with non unique criteria [merged]
Goto Forum:
  


Current Time: Sat Dec 10 03:22:26 CST 2016

Total time taken to generate the page: 0.22332 seconds