Re: pl/sql

Dear Thomas,

thank you for your quick response, I tried following code but I get a server error at runtime. Whats wrong?

CREATE OR REPLACE PROCEDURE "ANDI"."GETMIN" IS  TYPE Nodes IS TABLE OF NUMBER(10,0);
ve_vert Nodes := Nodes();
smallestValue NUMBER;

BEGIN
  FOR i IN 1..20 LOOP
    /*init array*/
    ve_vert.extend;
    ve_vert(i) := i;
  END LOOP;   select min(a.column_value) into smallestValue from table(cast(ve_vert as Nodes)) a;
  DBMS_OUTPUT.PUT_LINE('wert = ' || TO_CHAR(smallestValue )); END; Thanks
Andreas Schwaiger

Thomas Kyte <tkyte_at_us.oracle.com> schrieb in im Newsbeitrag: Gs70N2Dc7MmS4wlN+jp5AxPLOeq2_at_4ax.com...
> A copy of this was sent to "Andreas Schwaiger"
<andreas.schwaiger_at_strasser.at>
> (if that email address didn't require changing)
> On Fri, 1 Oct 1999 13:58:17 +0200, you wrote:
>
> >i have following script
> >
> >TYPE Nodes IS TABLE OF NUMBER(10,0) INDEX BY BINARY_INTEGER;
> >di_dist Nodes;
> >
> >When i fill the di_dist with Numbers, how can I get the minimum value
> >without using a LOOP statement?
> >Thanks
> >Andi
> >
> >
> >
>
> I suppose you could use a GOTO and a label...
>
> tkyte_at_8.0> declare
> 2 TYPE Nodes IS TABLE OF NUMBER(10,0) INDEX BY BINARY_INTEGER;
> 3 di_dist Nodes;
> 4
> 4 idx number;
> 5 maxVal number ;
> 6 begin
> 7 di_dist(1) := 1;
> 8 di_dist(-2) := 2;
> 9
> 9 idx := di_dist.first;
> 10 <<LABEL1>>
> 11 if ( idx is null ) then GOTO LABEL2; end if;
> 12 if ( maxVal is null or di_dist(idx) > maxVal ) then
> 13 maxVal := di_dist(idx);
> 14 end if;
> 15 if ( idx <> di_dist.last ) then
> 16 idx := di_dist.next(idx);
> 17 GOTO LABEL1;
> 18 end if;
> 19 <<LABEL2>>
> 20 dbms_output.put_line( 'Max = ' || maxVal );
> 21 end;
> 22 /
> Max = 2
>
> PL/SQL procedure successfully completed.
>
> but thats probably not what you meant :)
>
>
> In 8.0 and up, you could use an Object Type instead of a plsql table and
do
> this:
>
> tkyte_at_8.0> create or replace type myTableType as table of number;
> 2 /
>
> Type created.
>
> tkyte_at_8.0>
> tkyte_at_8.0> REM now, we want to "select * from PLSQL_FUNCTION()" not from a
> table:
> tkyte_at_8.0>
> tkyte_at_8.0> create or replace function getMyTableType return myTableType
> 2 as
> 3 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8,
9 );
> 4 begin
> 5 return l_x;
> 6 end;
> 7 /
>
> Function created.
>
> tkyte_at_8.0>
> tkyte_at_8.0>
> tkyte_at_8.0> REM here we go... selecting from it:
> tkyte_at_8.0>
> tkyte_at_8.0> select max( a.column_value ) val
> 2 from THE ( select cast( getMyTableType() as mytableType ) from
dual ) a
> 3 /
>
> VAL
> ----------
> 9
>
>
> In 7.x, there is a 'trick' you can use to select from a plsql table. If
you are
> interested in that, see:

http://www.deja.com/getdoc.xp?AN=417733109&fmt=text
> for an example...
>
>
>
> --
> See http://osi.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 Mon Oct 11 1999 - 05:41:29 CDT