Re: export table to csv

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=<aviewna­me>
>
> 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