Another One For You Budding Oracle Guru's

[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.

[andy]

try it without union all

[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

[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

[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.

[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]

[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

[Ardesco]

Do I sound qualified after starting this thread ???

[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'