Trying to export to a text file using PL/SQL

Hi I have the following query and I need the output to be in the form
of an attachment (text or csv) instead of being in the body of the
email. I am relatively new to PL/SQL and hitting a wall trying to get
this done. I really appreciate your help in looking into this. Thanks

CREATE OR REPLACE PROCEDURE SP_TEST as


--Declare all the variables
v_startdate DATE;
v_enddate DATE;
k NUMBER;


TYPE quest_type IS RECORD (
qcount NUMBER,
qtxt VARCHAR2(1000)
);

TYPE quest_tab IS TABLE OF quest_type
INDEX BY BINARY_INTEGER;

t_quest quest_tab;

-------------------------
CURSOR cur_1 IS

SELECT qtxt,
qcount
FROM
(
SELECT /*+ parallel(A,4)*/
SUM(A.QCNT) AS qcount,
LOWER(TRIM(A.QTEXT)) AS qtxt
FROM Xtable A
WHERE A.DT >= v_startdate
AND A.DT < v_enddate + 1
GROUP BY LOWER(TRIM(A.XX))
ORDER BY SUM(A.XXXX) DESC
)
WHERE ROWNUM < 50;




--------------------------------------------------------------------------------------------

BEGIN

FOR i in 1..7 LOOP
IF TRIM(UPPER(TO_CHAR(NEW_TIME(SYSDATE, 'PST','GMT')-
i ,'DAY'))) LIKE 'MONDAY%' THEN
v_enddate := TRUNC(NEW_TIME(SYSDATE, 'PST','GMT'))-i;
v_startdate:= v_enddate - 6;
END IF;
END LOOP;



ajutl.smtp_file.clear_buffer;
ajutl.smtp_file.add_to(' ');

ajutl.smtp_file.set_subject('TITLE ' || v_startdate || ' - ' ||
v_enddate);

k := 0;

ajutl.smtp_file.add_text('Num'||CHR(9)||'Query Text');

FOR i IN cur_1 LOOP

k := k + 1;

t_quest(k).qcount := i.qcount;
t_quest(k).qtxt := i.qtxt;

ajutl.smtp_file.add_text(t_quest(k).qcount||CHR(9)||t_quest
(k).qtxt);

END LOOP;

ajutl.smtp_file.send;

COMMIT;

END;

wrote:
> Hi I have the following query and I need the output to be in the form
> of an attachment (text or csv) instead of being in the body of the
> email. I am relatively new to PL/SQL and hitting a wall trying to get
> this done. I really appreciate your help in looking into this. Thanks
>
> CREATE OR REPLACE PROCEDURE SP_TEST as
>
>
> --Declare all the variables
> v_startdate DATE;
> v_enddate DATE;
> k NUMBER;
>
>
> TYPE quest_type IS RECORD (
> qcount NUMBER,
> qtxt VARCHAR2(1000)
> );
>
> TYPE quest_tab IS TABLE OF quest_type
> INDEX BY BINARY_INTEGER;
>
> t_quest quest_tab;
>
> -------------------------
> CURSOR cur_1 IS
>
> SELECT qtxt,
> qcount
> FROM
> (
> SELECT /*+ parallel(A,4)*/
> SUM(A.QCNT) AS qcount,
> LOWER(TRIM(A.QTEXT)) AS qtxt
> FROM Xtable A
> WHERE A.DT >= v_startdate
> AND A.DT < v_enddate + 1
> GROUP BY LOWER(TRIM(A.XX))
> ORDER BY SUM(A.XXXX) DESC
> )
> WHERE ROWNUM < 50;
>
>
>
>
> --------------------------------------------------------------------------------------------
>
> BEGIN
>
> FOR i in 1..7 LOOP
> IF TRIM(UPPER(TO_CHAR(NEW_TIME(SYSDATE, 'PST','GMT')-
> i ,'DAY'))) LIKE 'MONDAY%' THEN
> v_enddate := TRUNC(NEW_TIME(SYSDATE, 'PST','GMT'))-i;
> v_startdate:= v_enddate - 6;
> END IF;
> END LOOP;
>
>
>
> ajutl.smtp_file.clear_buffer;
> ajutl.smtp_file.add_to(' ');
>
> ajutl.smtp_file.set_subject('TITLE ' || v_startdate || ' - ' ||
> v_enddate);
>
> k := 0;
>
> ajutl.smtp_file.add_text('Num'||CHR(9)||'Query Text');
>
> FOR i IN cur_1 LOOP
>
> k := k + 1;
>
> t_quest(k).qcount := i.qcount;
> t_quest(k).qtxt := i.qtxt;
>
> ajutl.smtp_file.add_text(t_quest(k).qcount||CHR(9)||t_quest
> (k).qtxt);
>
> END LOOP;
>
> ajutl.smtp_file.send;
>
> COMMIT;
>
> END;

What is this?
ajutl.smtp_file.clear_buffer;
where did it come from?
In what version of Oracle?

Assuming a currently supported version of the product I would
suggest you look into UTL_FILE and UTL_MAIL.
http://www.psoug.org/reference/utl_file.html
http://www.psoug.org/reference/utl_mail.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan.RemoveThis@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org