Re: Using type <typename> is table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 02 Jul 1999 12:34:25 GMT
Message-ID: <3784b1b0.142726109@newshost.us.oracle.com>

A copy of this was sent to Anurag Minocha <anurag_at_synergy-infotech.com> (if that email address didn't require changing) On Fri, 02 Jul 1999 16:33:56 +0530, you wrote:

>Hi ,
>In a stored procedure i am using the following statement in the
>declarative part
>
>Type maxnumtype is table of number index by binary_integer.;
>maxnum maxnumtype;
>
>I am assigning some values to this type using some for loops.
>for i in 1..100 loop
> maxnum(i)=i+10;
>end loop;
>
>Now my question is how do i find the maximum value between maxnum(1) and
>maxnum(10),maxnum(11) and maxnum(20) and so on .. WITHOUT USING A FOR
>LOOP.
>I have to find the maximum in these range and insert into a particlar
>table.
>
>Currently i have created a table where i insert the values and then find
>the maximum using the max function,but feel that it is a overhead. If
>any of you can suggest a solution to the above problem or some other
>method i will be very greatful.
>
>Any help will be appreciated
>
>thanks
>anurag
>
>reply at
>anurag_at_synergy-infotech.com
>

If you have Oracle8 and the objects stuff, you can code like this:

SQL> create or replace type myTableType as table of number;   2 /

Type created.

SQL>
SQL> declare

  2          l_x     myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );
  3          l_max number;
  4  begin
  5          select max( a.column_value ) into l_max
  6            from THE ( select cast( l_x as mytableType ) from dual ) a;
  7  
  7          dbms_output.put_line( l_max );

  8 end;
  9 /
9

PL/SQL procedure successfully completed.

Note that the table type MUST BE A SQL type -- not a plsql type so you create it outside of a plsql spec for this to work.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 02 1999 - 07:34:25 CDT