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

On Dec 11, 5:23 pm, DA Morgan <damor..._at_psoug.org> wrote:
> sajrizv..._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('..._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.htmlhttp://www.psoug.org/reference/utl_mail.html
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Thanks Daniel. This is of great help. Received on Mon Dec 15 2008 - 06:22:50 CST