Re: export table to csv
Date: Tue, 7 Apr 2009 08:59:32 -0700 (PDT)
Message-ID: <e19d03ea-168d-4e14-9761-841f46eb55f9_at_s28g2000vbp.googlegroups.com>
On Apr 7, 8:43 am, Shakespeare <what..._at_xs4all.nl> wrote:
> ddf schreef:
>
>
>
>
>
> > On Apr 7, 2:39 am, Shakespeare <what..._at_xs4all.nl> wrote:
> >> blumagic schreef:
>
> >>> 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:
> >>> PROCEDURE generate_csv (view_name IN VARCHAR2)
> >>> IS
> >>> 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;
> >>> /
> >> After changing the parameter name (and the calling url) to p_view_name,
> >> this proc works fine on my database.
>
> >> Shakespeare- Hide quoted text -
>
> >> - Show quoted text -
>
> > Please post the changes you made.
>
> > David Fitzjarrell
>
> Changed
>
> >>> PROCEDURE generate_csv (view_name IN VARCHAR2)
>
> into
> >>> PROCEDURE generate_csv (p_view_name IN VARCHAR2)
>
> and used the url
>
> http://<myserver>/portal/pls/portal/portal.generate_csv?p_viewname=<aviewname>
>
> and it works.
>
> I use the portal account because I'm testing on a portal database. It
> must be replaced by the proper DAD.
> You MAY have to check whether the DAD user has enough rights on the view.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -
Thank you.
David Fitzjarrell Received on Tue Apr 07 2009 - 17:59:32 CEST