Re: export table to csv

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 Received on Tue Apr 07 2009 - 14:51:23 CEST