469,125 Members | 1,863 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Spooling Techniques.....

1
I have to write a procedure using spooling techniques where the procedure will take some data from a table and will write it in a file defined in the spool command. I'm giving the procedure........previously it was done using utl_file but now we need spooling for that...... the target path is \xyz.........

CREATE OR REPLACE PROCEDURE PROC_FILE_GENERATOR AS
CURSOR C1 IS
SELECT DISTINCT SERVICE_NUM FROM L_EDW_BB_AVAILABILITY_MASTER GROUP BY SERVICE_NUM ;
V_FILE_HANDLE UTL_FILE.FILE_TYPE;
V_INPUT_FILE UTL_FILE.FILE_TYPE;
V_FILE_NAME VARCHAR2(50);
V_LINE VARCHAR2(1000);
V_HEADER VARCHAR2(1);
v_record_count number(8);
v_DN_count number(8);
V_FILE_NAME_READ VARCHAR2(50);
INPUT_BUFFER varchar2(4000);
V_DATE VARCHAR2(8);
BEGIN
V_FILE_NAME := 'wholesale.dat';
V_FILE_HANDLE := UTL_FILE.FOPEN('/xyz',V_FILE_NAME,'W');
select to_CHAR(sysdate,'DDMMYYYY') INTO V_DATE FROM DUAL;
V_LINE := '0,BT RETAIL,BBCHECK,'||V_DATE||',V1,F1,V2';
UTL_FILE.PUT_LINE(V_FILE_HANDLE, V_LINE);
V_FILE_NAME_READ:='contact_info.dat';
V_INPUT_FILE:=UTL_FILE.FOPEN('/xyz',V_FILE_NAME_READ,'R');
utl_file.get_line (V_INPUT_FILE,INPUT_BUFFER);
--dbms_output.put_line(input_buffer);
UTL_FILE.PUT_LINE(V_FILE_HANDLE,INPUT_BUFFER);
utl_file.fclose(V_INPUT_FILE);
FOR REC1 IN C1
LOOP
EXIT WHEN C1%NOTFOUND;
V_LINE := '7'|| ',' || REC1.SERVICE_NUM ;
UTL_FILE.PUT_LINE(V_FILE_HANDLE, V_LINE);
v_DN_count:=c1%ROWCOUNT;
v_record_count:=3+v_DN_count;
END LOOP;
V_LINE:='9'||','||V_record_COUNT||','||v_DN_count;
UTL_FILE.PUT_LINE(V_FILE_HANDLE, V_LINE);
UTL_FILE.FCLOSE(V_FILE_HANDLE);
END PROC_FILE_GENERATOR;

please help in resolving this..............
Jan 10 '07 #1
0 1636

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

25 posts views Thread by JW | last post: by
15 posts views Thread by christopher diggins | last post: by
2 posts views Thread by Martin | last post: by
19 posts views Thread by JoeC | last post: by
16 posts views Thread by Panos Laganakos | last post: by
6 posts views Thread by Maitre Bart | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.