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