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)!
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "Another One For You Budding Oracle Guru's"
Collapse
-
Originally posted by PatHi 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
No thanks.
'WE SPEAK ENGLISH OVER HERE'
Leave a comment:
-
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
Leave a comment:
-
Originally posted by OrangeHopperFor 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.
oracle -> flat file -> aparse -> oracle
Leave a comment:
-
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.
Leave a comment:
-
Got it working by doing this:
Code: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
Leave a comment:
-
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> ASCLast edited by Weltchy; 26 June 2007, 10:52.
Leave a comment:
-
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:
Code: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.
Leave a comment:
-
Originally posted by Weltchy<<Slaps hand against forehead>>
You guys are honestly contractors?
It's frightening isn't it?
Leave a comment:
-
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;
Leave a comment:
-
Originally posted by chicaneYou 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.
Leave a comment:
Partners
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: