Reply to: Another One For You Budding Oracle Guru's

[mashetti]

just my 2p worth...

if you used a quality tool like sqlplus then you can just use the prompt keyword. so you just go:

spool my_file.txt
prompt this is my header
select * from dual --or whatever your query is
/
prompt this is my footer
spool off

just remember that union will implicitly group and order whereas union all will not. if you post something like this at forums.oracle.com in the sql / pl/sql forum you will get loads of responses from loads of know it alls... oh, and the difference will be that it will work, unlike alot of the guff above (although it did work in the end)!

[TheRightStuff]

Hi everyone.

We are looking for Oracle application developers to work in Brussel for NATO.

If you feel you are qualified and interested.. please send me a note

cheers

Pat

what in Bristol?
No thanks.
'WE SPEAK ENGLISH OVER HERE'

[Ardesco]

Do I sound qualified after starting this thread ???

[Pat]

Oracle application developers for NATO

Hi everyone.

We are looking for Oracle application developers to work in Brussel for NATO.

If you feel you are qualified and interested.. please send me a note

cheers

Pat

[OrangeHopper]

[MrsGoof]

For my penny's worth.

Why work with a flat file?

Why not using "insert into x select * from y@db_link"?

If you must use a flat file then don't bother with the column name line and use sqlldr to load it into the second database.

how about this for a solution
oracle -> flat file -> aparse -> oracle

[OrangeHopper]

For my penny's worth.

Why work with a flat file?

Why not using "insert into x select * from y@db_link"?

If you must use a flat file then don't bother with the column name line and use sqlldr to load it into the second database.

[Ardesco]

Got it working by doing this:

SELECT DISTINCT
 
INSERT_STATEMENT
 
FROM
 
(SELECT 
 
'INSERT INTO <TABLE_NAME> (A, B, C) VALUES ('''||<fooA>''', '''||<fooB>''', '''||<fooC>''');' AS INSERT_STATEMENT
 
WHERE <fooB> = XXX
 
ORDER BY <fooZ>)
UNION ALL
 
SELECT 'COMMIT;' FROM dual

Simple when you know how I guess

[Weltchy]

Add an additional column to the SQL that tracks the order by for each separate section, and move the ORDER BY clause to the bottom of the entire union. Change the ORDER BY to sort by the additional column and have the second sort field as <fooZ>.

It would read something like this


SELECT

'INSERT INTO <TABLE_NAME> (A, B, C) VALUES ('''||<fooA>''', '''||<fooB>''', '''||<fooC>''');' AS INSERT_STATEMENT,1 ROW_ORDER

WHERE <fooB> = XXX

UNION ALL

SELECT 'COMMIT;',2 ROW_ORDER FROM dual

ORDER BY ROW_ORDER ASC, <fooZ> ASC


Edit : Oh, if you want to ensure the ROW_ORDER doesnt get exported, wrap the SQL inside a table and explicitly define the field. ie

SELECT INSERT_STATEMENT
FROM
(

) ExportData
ORDER BY ROW_ORDER ASC, <fooZ> ASC

[andy]

try it without union all

[Ardesco]

Bleh i'm not Oracle guru, which I freely admit (and should be fairly clear from my question).

What i'm trying to do is create a flat file that will be used to insert data into a specific table on a duplicate DB to fill it up with test data.

The query i'm trying to do is as follows:

SELECT 
 
'INSERT INTO <TABLE_NAME> (A, B, C) VALUES ('''||<fooA>''', '''||<fooB>''', '''||<fooC>''');' AS INSERT_STATEMENT
 
WHERE <fooB> = XXX
 
ORDER BY <fooZ>
UNION ALL
 
SELECT 'COMMIT;' FROM dual

The problem I have is the order by. If I do a SELECT DISTINCT I have to add <fooZ> as a second output column (which isn't a big deal as I can just discard the second column later on) but the problem that i'm having is the ORDER By seems to break the union.

It's probably a very simple solution, but I admit it's something i'm not sure how to do.

[SallyAnne]

<<Slaps hand against forehead>>

You guys are honestly contractors?

It's frightening isn't it?

[Weltchy]

<<Slaps hand against forehead>>

You guys are honestly contractors?

[thunderlizard]

you can sort out the ordering like this but it's rather ugly

select mydata from
(
select 1 recordtype, 'my header' mydata
from dual
union
select 2 recordtype, "field 1 " || field 2 "" mydata
from mytable
union
select 3 recordtype, 'my footer' mydata
)
order by recordtype;

[EqualOpportunities]

You and me both. Take the existing join mechanisms that make sense and everybody understands, and replace them with something that makes no sense and is therefore difficult to understand and learn.

I tried them once - didn't like it, didn't bother again...