Generate a .txt file

This is my first time on the forum so a big hello to everyone!!

Firstly I am very noive user of sql to say the least, but i have be requested to create a .txt file

Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc

MR
JOE
BLOGGS

Here is the my code so far, like i said i am a very novice user so try to help me and not condem me for my lack of knowledge!

SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not sure how to do that and how to generate it in a txt file?! I have to produce it in a script and not by a Export/Import wizard in sql server mangement studio!!! Please help

Keith

Note that this is the mysql forum, are you on this database or on Microsoft's SQLServer (aka MsSQL)?

Hey!!! yea was unsure where to post!!!

Its an oracle database, so no MsSQL!

Then this question should be in the oracle forum, I've asked a mod to move this thread there

Here is a fully commented answer to your question.
Start by improving your Google skills ...

Start by improving your Google skills ??

No need to get smart, bell-end!! and if ur not sure what that is....google it!!!

You either use plsql or any programming language you like to achieve that. Native sql can't unless you select your result into a temp table then export as csv or some sort.

Not entirely true, check the link I posted

you could just capture the output with SQLPLUS. Just turn on the logger before you run the query.

OK Heres how iv decide to go; Altering some code i stubbled across!!! But its not quite working yet


EXEC_file UTL_FILE.FILE_TYPE;

cursor read_lpa_input is
select l.input_title,
l.INPUT_SURNAME,
h.LPA_AMT,
h.ELIG_RATE,
h.RATE_REBATE,
h.RR_AMT,
h.LPA_APPLIC,
h.LPA_AMT

from lpa_input l, lpa_history h
WHERE l.CLAIM_NO = h.CLAIM_NO;


BEGIN

exec_file := UTL_FILE.FOPEN ('J:/','output.txt', 'W');

open read_lpa_input;

loop

fetch read_lpa_input into wk_input_surname, wk_history_lpa_amt,
wk_history_elig_rate,
wk_history_rate_rebate,
wk_history_rr_amt,
wk_history_lpa_applic,
wk_history_lpa_amt;

Exit when read_lpa_input%notfound;


UTL_FILE.PUT_LINE(exec_file, wk_input_title);
UTL_FILE.PUT_LINE(exec_file, wk_input_surname);
UTL_FILE.PUT_LINE(exec_file, wk_history_lpa_amt);
UTL_FILE.PUT_LINE(exec_file, wk_history_elig_rate);
UTL_FILE.PUT_LINE(exec_file, wk_history_rate_rebate);
UTL_FILE.PUT_LINE(exec_file, wk_history_rr_amt);
UTL_FILE.PUT_LINE(exec_file, wk_history_lpa_applic);
UTL_FILE.PUT_LINE(exec_file, wk_history_lpa_amt);

end loop;

ERROR:


SQL> lpa_notif1
SP2-0042: unknown command "lpa_notif1" - rest of line ignored.
SQL> @lpa_notif1
fetch read_lpa_input into wk_input_surname, wk_history_lpa_amt,
*
ERROR at line 41:
ORA-06550: line 41, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 41, column 3:
PL/SQL: SQL Statement ignored

close read_lpa_input;
UTL_FILE.FCLOSE(exec_file);
end;
/

See what the error means here (7 versus 8)

EXCELLENT!! cool site!!