Re: export table to csv

From: blumagic <riccardo.dibella_at_gmail.com>
Date: Mon, 6 Apr 2009 06:06:28 -0700 (PDT)
Message-ID: <b6131f89-6f13-4ccb-a1d1-ea87b024aef3_at_y7g2000yqa.googlegroups.com>

[Quoted] [Quoted] On 6 Apr, 14:38, ddf <orat..._at_msn.com> wrote:
> On Apr 6, 5:08 am, blumagic <riccardo.dibe..._at_gmail.com> wrote:
>
>
>
>
>
> > Hi!My problem is this:
> > i have created a procedure to export a sql table to csv file using
> > dynamic sql and the package dbms_sql. It works eith the tables defined
> > on my database but it doesn't work with the view that are referred to
> > table defined in other databases. The procedure doesn't work with this
> > instruction:
>
> > DBMS_SQL.parse (cus_3, 'SELECT * FROM ' || p_view_name,
> > DBMS_SQL.NATIVE);
> > The procedure reads the view, insert the values in the table created
> > but when i pass the function htp.p it doesn't show anything and it
> > doesn't open the csv file. This problem doesn't exist if i pass in the
> > parameter of the procedure a table.
>
> > Why it reads the tables but not the view???????
>
> > Thanks to everybody.
>
> > Riccardo
>
> Post the code for this procedure; we can't tell you anything until you
> do.
>
> David Fitzjarrell- Nascondi testo citato
>
> - Mostra testo citato -

the procedure is:

[Quoted] [Quoted] PROCEDURE generate_csv (view_name IN VARCHAR2) IS

[Quoted]    i               INTEGER           := 0;
   v_class         VARCHAR2 (100);
   v_filename      VARCHAR2 (100);
   v_output        VARCHAR2 (3000);
   v_output_desc   VARCHAR2 (3000);
   cus_3           INTEGER           := DBMS_SQL.open_cursor;
   y               INTEGER           := 0;
   j               NUMBER;
   c               INTEGER;
   columnvalue     VARCHAR2 (4000);
   col             NUMBER            := 0;
   l_desctbl       DBMS_SQL.desc_tab;

BEGIN    v_filename:=p_view_name;

   OWA_UTIL.mime_header ('application/vnd.ms-excel" charset="iso-8859-1"',

                         FALSE
                        );

   HTP.p ('Content-Disposition: filename="' || v_filename || '.csv"');

   OWA_UTIL.http_header_close;

   DBMS_SQL.parse (cus_3, 'SELECT * FROM ' || p_view_name, DBMS_SQL.NATIVE);    DBMS_SQL.describe_columns (cus_3, col, l_desctbl);

   FOR j IN 1 .. col
   LOOP

      IF j = 1
      THEN
         v_output_desc := l_desctbl (j).col_name;
      ELSE
         v_output_desc := v_output_desc || ';' || l_desctbl
(j).col_name;
      END IF;

   END LOOP;    v_output_desc := v_output_desc || ';';

   HTP.p (v_output_desc);

   FOR y IN 1 .. col
   LOOP
      DBMS_SQL.define_column (cus_3, y, columnvalue,4000);    END LOOP;    c := DBMS_SQL.EXECUTE (cus_3);

   LOOP
      EXIT WHEN DBMS_SQL.fetch_rows (cus_3) = 0;

      FOR y IN 1 .. col
      LOOP
         DBMS_SQL.COLUMN_VALUE (cus_3, y, columnvalue);

         IF y = 1
         THEN
            v_output := columnvalue;
         ELSE
            v_output := v_output || ';' || columnvalue;
         END IF;
      END LOOP;

      HTP.p (v_output);

   END LOOP; END;
/ Received on Mon Apr 06 2009 - 15:06:28 CEST