Re: export table to csv
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 07 Apr 2009 15:43:57 +0200
Message-ID: <49db588b$0$182$e4fe514c_at_news.xs4all.nl>
>> 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 -
Date: Tue, 07 Apr 2009 15:43:57 +0200
Message-ID: <49db588b$0$182$e4fe514c_at_news.xs4all.nl>
[Quoted] 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 Received on Tue Apr 07 2009 - 15:43:57 CEST