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

On Apr 6, 5:08*am, blumagic <riccardo.dibe... (AT) gmail (DOT) com> wrote:
Post the code for this procedure; we can't tell you anything until you
do.


David Fitzjarrell

On 6 Apr, 14:38, ddf <orat... (AT) msn (DOT) com> wrote:
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;
/

blumagic schreef:
If this is your real code, it won't compile. Your parameter is
view_name, and in your code you use p_view_name.

Shakespeare

blumagic schreef:
One more thing: there is no dbms_sql.close_cursor in your code.

Shakespeare

blumagic schreef:
After changing the parameter name (and the calling url) to p_view_name,
this proc works fine on my database.

Shakespeare

Sorry for the errors in the procedure. It works fine also in my
database but only for table created in it, if i insert a view about a
table in other database it doesn't work, the htp.p doesn't show
anything. The strange thing is that the procedure take the values from
the view or the table, but for the table show the csv file and open
the link, for the view this link doesn't appear.

On Apr 7, 2:39*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
Please post the changes you made.


David Fitzjarrell

ddf schreef:
Changed

into
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

On Apr 7, 8:43*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
Thank you.


David Fitzjarrell