-
How to use Spool from a PL/SQL Stored Procedure?
Hi Friends,
I had a requirement where I have to use spool technique in a stored procedure.From thwe stored procedure the output data has to be spooled to a file. How I can do this ?
The following is my code.Pls modify the code and give me the script. As I am newbie let me know if any errors are there.
I need to call this procedure from a Scheduler on a fixed date.How can I do this.
CREATE OR REPLACE PROCEDURE GETDATE
AS
CURSOR C1 IS SELECT * FROM NEWAFFAIRS
WHERE DOB BETWEEN TO_DATE('07-'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MON')||'-'
||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY'),'DD-MON-YYYY') AND TO_DATE('07-'||TO_CHAR((SYSDATE),'MON')||'-'
||TO_CHAR((SYSDATE),'YYYY'),'DD-MON-YYYY');
CREC C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO CREC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(CREC.NEWID||','||CREC.SURNAME||','||CREC.NAMES||','||CREC.DOB);
END LOOP;
CLOSE C1;
END;
Thanks In Advance.
Kishan
SUROOP B
-
Use UTL_FILE package to write to a file, and use DBMS_JOB package to schedule it.
-
Check the following example for UTL_FILE usage.
http://www.jlcomp.demon.co.uk/faq/utl_file.html
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|