Another One For You Budding Oracle Guru's

[Ardesco]

I am creating some flat files extracting some data from Oracle using toad, however I would like to put in a header and a footer. Is there a way to add a header row and a footer row to your output ?

Example of the format of SQL i'm using below:

SELECT

''''field1''''||''''field2''''||''''field3'''' as flat_file

FROM

foo

ORDER Y

field1

[SallyAnne]

I am creating some flat files extracting some data from Oracle using toad, however I would like to put in a header and a footer. Is there a way to add a header row and a footer row to your output ?

Example of the format of SQL i'm using below:

SELECT

''''field1''''||''''field2''''||''''field3'''' as flat_file

FROM

foo

ORDER Y

field1

Depends what you want your header and footer to be? if it's just text cant you just do: -


SELECT
'my lovely header'
,''''field1''''||''''field2''''||''''field3''''
,'my lovely footer'
as flat_file

FROM

foo

ORDER Y

field1

[SallyAnne]

Ooops - no that wont work.

Try
SELECT
'my lovely header' from dual
UNION
select ''''field1''''||''''field2''''||''''field3''''
from whatever tables
where whatever
order by whatever
UNION
select 'my lovely footer' from dual
as flat_file


xxx

[SallyAnne]

no, no, that wont work either......(I should really learn to think before I type).....

[Kyajae]

no, no, that wont work either......(I should really learn to think before I type).....

Or learn Oracle before you think................

[EqualOpportunities]

no, no, that wont work either......(I should really learn to think before I type).....

And whilst you're on, can you explain the SQL92 joins to me? You know, UNION, LEFT, RIGHT, yadda, yadda, yadda...

Never understood 'em

[SallyAnne]

It'd be really simple if you just stuck it in a pl/sql package...

what about this?

select 'my lovely header'||CHR(13) || CHR(10) from dual
UNION ALL
select DISTINCT my_fields
from my_tables
where whatever
UNION ALL
Select 'my lovely footer'||
CHR(13) ||
CHR(10) from dual

(the CHR(13)||CHR(10) bit is a carriage return - but I dont know if you'll need that).

[andy]

It'd be really simple if you just stuck it in a pl/sql package...

what about this?

select 'my lovely header'||CHR(13) || CHR(10) from dual
UNION ALL
select DISTINCT my_fields
from my_tables
where whatever
UNION ALL
Select 'my lovely footer'||
CHR(13) ||
CHR(10) from dual

(the CHR(13)||CHR(10) bit is a carriage return - but I dont know if you'll need that).

I would think run these statements individually without 'union all'

[SallyAnne]

I would think run these statements individually without 'union all'

I think he wanted to do quite a few without manually editting them.

Just plain lazy if you ask me

[Ardesco]

I think he wanted to do quite a few without manually editting them.

Just plain lazy if you ask me

Cheers SA will try it out, and it is for a few scripts I can leave with a readme that says run this script and save the output as a flat file. That way others can't screw it up later on (Well OK they can, but it should be much harder...)