Re: export table to csv
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 07 Apr 2009 09:39:18 +0200
Message-ID: <49db0329$0$189$e4fe514c_at_news.xs4all.nl>
>
> 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;
> /
Date: Tue, 07 Apr 2009 09:39:18 +0200
Message-ID: <49db0329$0$189$e4fe514c_at_news.xs4all.nl>
blumagic schreef:
> On 6 Apr, 14:38, ddf <orat..._at_msn.com> wrote:
[Quoted] >> 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 Received on Tue Apr 07 2009 - 09:39:18 CEST