RE: sqlplus extract question...

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Thu, 6 Jan 2005 09:08:05 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A971DE@exchsen0a1ma>

Chris,

Actually, you are only selecting one big column in this query - everything is concat'ed together. So individual column headings should not appear in the output. I'm guessing that this is simply a default heading length truncation.

If you really need column headings, put another select in front of this query - something like:

select "Fac Id,Oris Code,Facility Name, Description...(etc)" from dual;

followed by your select.

Good Luck!

Tom

-----Original Message-----
From: Chris Stephens [mailto:cstephens16_at_gmail.com] Sent: Thursday, January 06, 2005 8:56 AM To: Mercadante, Thomas F
Cc: oracle-l_at_freelists.org
Subject: Re: sqlplus extract question...

sure, the titles stop at h.NAICS_CODE||'","'||....

SET LINESIZE 32767
SET PAGES 10000
SET LINES 1024 ACCEPT fac_id NUMBER PROMPT 'Enter fac_id:'

spool c:\FAC_CHAR.txt

select h.FAC_ID ||'","'||

h.ORIS_CODE||'","'||
h.FACILITY_NAME||'","'||
h.DESCRIPTION||'","'||
h.STATE||'","'||
h.COUNTY_CD||'","'||
h.SIC_CODE||'","'||
h.EPA_REGION||'","'||
h.NERC_REGION||'","'||
h.NAICS_CODE||'","'||
h.SOURCE_CAT||'","'||
to_char(h.AIRSID)||'","'||
h.FINDSID||'","'||
h.STATEID||'","'||
h.LATITUDE||'","'||
h.LONGITUDE||'","'||
h.USERID||'","'||
h.ADD_DATE||'","'||
h.UPDATE_DATE||'","'||
h.FRS_ID||'","'||
h.PAYEE_ID||'","'||
h.PERMIT_EXP_DATE||'","'||
h.LATLON_SOURCE||'","'||
i.UNIT_ID||'","'||
i.ACCOUNT||'","'||
i.UNIT_DESCRIPTION||'","'||
i.FAC_ID||'","'||
i.OP_STATUS||'","'||
i.UNITID||'","'||
i.STATEID||'","'||
i.BLRSEQ||'","'||
i.RETIRE_DATE||'","'||
i.COMM_OP_DATE||'","'||
i.COMM_OP_DATE_CD||'","'||
i.COMR_OP_DATE||'","'||
i.COMR_OP_DATE_CD||'","'||
i.USERID||'","'||
i.ADD_DATE||'","'||
i.UPDATE_DATE||'","'||
f.UNT_PPL_ID||'","'||
f.UNIT_ID||'","'||
f.PPL_ID||'","'||
f.RESPONSIBILITY_ID||'","'||
f.PRG_ID||'","'||
f.BEGIN_DATE||'","'||
f.END_DATE||'","'||
f.USERID||'","'||
f.UPDATE_DATE||'","'||
f.ADD_DATE||'","'||
g.first_name||'","'||
g.last_name||'","'||
e.comp_name||'","'||
d.BEGIN_DATE||'","'||
d.END_DATE||'","'||
d.USERID||'","'||
d.ADD_DATE||'","'||
d.UPDATE_DATE||'","'||
d.RELATIONSHIP||'","'||
d.UON_ID||'","'||
d.OWN_ID||'","'||
d.UNIT_ID||'","'||
d.ONT_TYPE_CD||'","'||
c.UP_ID||'","'||
c.PRG_ID||'","'||
c.UNIT_ID||'","'||
c.BEGIN_DATE||'","'||
c.REPOWER_FLG||'","'||
c.APP_STATUS||'","'||
c.PRG_CODE||'","'||
c.EXEMPT_TYPE||'","'||
c.REPORT_FREQ||'","'||
c.CLASS||'","'||
c.END_DATE||'","'||
c.OPTIN_IND||'","'||
c.DEF_IND||'","'||
c.DEF_END_DATE||'","'||
c.STATE_REG||'","'||
c.EX_END_DATE||'","'||
c.EX_BEGIN_DATE||'","'||
c.OPHOUR_LIMIT||'","'||
c.SO2_AFFECT_YEAR||'","'||
c.SO2_PHASE||'","'||
c.NOX_PHASE||'","'||
c.NOX_GROUP||'","'||
c.NOX_STAN_LIMIT||'","'||
c.NOX_YEAR||'","'||
c.EE_IND||'","'||
c.EE_TERMYEAR||'","'||
c.USERID||'","'||
c.ADD_DATE||'","'||
c.UPDATE_DATE||'","'||
c.EE_LIMIT||'","'||
c.EX_REC_DATE||'","'||
b.UNIT_ID||'","'||
b.BEGIN_DATE||'","'||
b.FUEL_TYPE||'","'||
b.INDICATOR_CD||'","'||
b.ACT_OR_PROJ_CD||'","'||
b.END_DATE||'","'||
b.OZONE_SEAS_IND||'","'||
b.DEM_SO2||'","'||
b.DEM_GCV||'","'||
b.USERID||'","'||
b.ADD_DATE||'","'||
b.UPDATE_DATE||'","'||
b.SULFUR_CONTENT||'","'||
b.UF_ID||'","'||
a.CTL_ID||'","'||
a.UNIT_ID||'","'||
a.CONTROL_CD||'","'||
a.CE_PARAM||'","'||
a.INSTALL_DATE||'","'||
a.OPT_DATE||'","'||
a.ORIG_CD||'","'||
a.SEAS_CD||'","'||
a.RETIRE_DATE||'","'||
a.USERID||'","'||
a.UPDATE_DATE||'","'||

a.ADD_DATE||'","'||
a.INDICATOR_CD
FROMl a,

	 b,
	 c,
	 d,
	 e,
	 f,
	 g,
	 h,
	 i
WHERE h.fac_id = &fac_id
  and h.fac_id = i.fac_id
  and i.unit_id = a.unit_id (+)
  and i.unit_id = b.unit_id (+)
  and i.unit_id = c.unit_id (+)
  and i.unit_id = d.unit_id (+)
  and d.own_id = e.comp_id  (+)
  and i.unit_id = f.unit_id (+)

and f.ppl_id = g.ppl_id (+)
and f.responsibility_id IN ('PRM','ALT',NULL);

spool off

On Thu, 6 Jan 2005 08:39:14 -0500, Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us> wrote:
> Chris,
>
> Can you show us the Sql you are running?
>
> Tom
>
>
> -----Original Message-----
> From: Chris Stephens [mailto:cstephens16_at_gmail.com]
> Sent: Wednesday, January 05, 2005 7:35 PM
> To: oracle-l_at_freelists.org
> Subject: sqlplus extract question...
>
> I'm creating an extract of some data that will be qa'd in an excel
> spreadsheet. I'm creating a comma delimited file and am including the
> column headings. There are about 60 columns from 5 or 6 tables.
> After (approximately) the fifteenth column, the headings become blank
> and I can't figure out why. The data is fine but the headings are
> missing. I'm not giving the columns any aliases or anything. Is this
> a setting in sqlplus? A quick look through my O'reilly sqlplus book
> didn't do me any good. Before I spend some significant time trying to
> figure out what is causing this, i thought i'd start here.
>
> any ideas?
>
> tia!
>
> ...sarcastism welcome (expected)! :)
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 06 2005 - 08:05:13 CST