Re: How can i use table type variables with DBMS_SQL.DEFINE

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/29
Message-ID: <34586aee.8442499@newshost>#1/1

On Wed, 29 Oct 1997 14:50:32 GMT, nsi_at_tornado.be (Massimo Trento) wrote:

> Hi.
>
>I have to write a procedure to define dynamic fields for my query and
>the only dynamic
>way i need to pass dynamic number of argument is throught a varchar2
>table. But the problem is that
>the function DBMS_SQL.DEFINE_COLUMN doesn't accept table type
>variables.
>I'm using Oracle Web Server 2.1 / WIN NT4.
>
>Any help would be apreciated.
>
>Thanx in advance.
>
>here's a sample of the code i wrote :
>--------------------------------------------------------------------------------------
>procedure DynaQuery2 ( TableLabels in owa_util.ident_arr,
[snip]
>DynColumns owa_util.ident_arr;
>
>begin
>
>-- The Dynamic string has already been built
>
> ...... SNIP ........
>
>
> for i in 1..elem loop
> if TableSelect(i) IS NULL then exit; end if;
>
>DBMS_SQL.DEFINE_COLUMN(ReqCursor,i,DynColumns(i),30);
>
> end loop;
>

[snip]

it takes tables, the problem looks to me like you are accessing dynColumns(i) (reading the value) before you assigned a value. For example:

SQL> declare
  2 dynColumns owa_util.ident_arr;   3 begin
  4 dbms_output.put_line( dynColumns(1) );   5 end;
  6 /
declare
*
ERROR at line 1:
ORA-01403: no data found

SQL> declare
2 dynColumns owa_util.ident_arr; 3 begin

  4          dynColumns(1) := NULL;
  5          dbms_output.put_line( dynColumns(1) );

6 end;
7 /

PL/SQL procedure successfully completed.

You see, the first block tried to 'read' the value of dynColumns(1) but dynColumns(1) never existed and the behaviour of a pl/sql table is that when you try to 'read' a value that has never been 'written', it raises NO_DATA_FOUND. The second example corrects the program by 'writing' a value to the table entry before using it as input into another function.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.

I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Wed Oct 29 1997 - 00:00:00 CST

Re: How can i use table type variables with DBMS_SQL.DEFINE_COLUMN in OWS21 ?