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 (no spam) 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 -

On 6 Apr, 14:38, ddf <orat... at (no spam) msn.com> wrote:
On Apr 6, 5:08 am, blumagic <riccardo.dibe... at (no spam) 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;
/

On 6 Apr, 14:38, ddf <orat... at (no spam) msn.com> wrote:
On Apr 6, 5:08 am, blumagic <riccardo.dibe... at (no spam) 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;
/

On 6 Apr, 14:38, ddf <orat... at (no spam) msn.com> wrote:
On Apr 6, 5:08 am, blumagic <riccardo.dibe... at (no spam) 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;
/

blumagic schreef:





On 6 Apr, 14:38, ddf <orat... at (no spam) msn.com> wrote:
On Apr 6, 5:08 am, blumagic <riccardo.dibe... at (no spam) 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 -

ddf schreef:





On Apr 7, 2:39 am, Shakespeare <what... at (no spam) xs4all.nl> wrote:
blumagic schreef:

On 6 Apr, 14:38, ddf <orat... at (no spam) msn.com> wrote:
On Apr 6, 5:08 am, blumagic <riccardo.dibe... at (no spam) 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 -

On Apr 7, 2:39 am, Shakespeare <what... at (no spam) xs4all.nl> wrote:
blumagic schreef:





On 6 Apr, 14:38, ddf <orat... at (no spam) msn.com> wrote:
On Apr 6, 5:08 am, blumagic <riccardo.dibe... at (no spam) 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

PROCEDURE generate_csv (view_name IN VARCHAR2)

PROCEDURE generate_csv (p_view_name IN VARCHAR2)