Re: pl/sql

A copy of this was sent to "Andreas Schwaiger" <andreas.schwaiger_at_strasser.at> (if that email address didn't require changing) On Mon, 11 Oct 1999 12:41:29 +0200, you wrote:

>
>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;
>

In order to use the TYPE NODES in sql -- it must be known to the database. The way you have done it 'hides' the node type as a local definition in the procedure (the SQL engine doesn't know what NODES is -- only the procedure getmin can 'see' that type).

here is the script that works:

create or replace TYPE Nodes as TABLE OF NUMBER(10,0); /
CREATE OR REPLACE PROCEDURE GETMIN
IS

 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;
/

Here I've created the type NODES outside of the procedure so its a standalone type. this works.

>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:
>>

--
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 - 09:10:19 CDT