Re: Trying to export to a text file using PL/SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 11 Dec 2008 09:23:16 -0800
Message-ID: <1229016200.647050@bubbleator.drizzle.com>


sajrizvi80_at_gmail.com 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('DDD_at_gmail.com');
>
> 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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Dec 11 2008 - 11:23:16 CST

Original text of this message