Oracle 8i Stored Proc - Arrays of Different Types

Hi!  New to the board.  I am looking at creating an Oracle Stored Procedure and, within the procedure, want to use an array.  The array needs to be able to hold different data types (i.e. VARCHAR2 and NUMBER).  Is this even possible?  I am under the impression that the VARRAY type can only be used for one data type.  Thanks to anyone and everyone in advance! :)

James Haefner
Thursday, February 19, 2004

CREATE OR REPLACE PACKAGE DUMMY IS

type TFoo is record (
  text varchar2(100),
  num number
);

type TFooList is table of TFoo index by binary_integer;

procedure Init;
function Ex1(pFoo in TFoo) return number;
function Ex2 (pFoo in TFoo)return varchar2;

END;
/

CREATE OR REPLACE PACKAGE BODY DUMMY IS

procedure Init;

MyFooList TFooList;

begin
  MyFooList(1).text:='Hello';
  MyFooList(1).num:=100;
  MyFooList(2).text:='World';
  MyFooList(2).num:='200';

  dbms_output.put_line(Ex1(MyFooList(1));
  dbms_output.put_line(Ex2(MyFooList(1));
  dbms_output.put_line(Ex1(MyFooList(2));
  dbms_output.put_line(Ex2(MyFooList(2));
end;

END;
/

There you go.

HTH,

Patrik
Thursday, February 19, 2004

function Ex1(pFoo in TFoo) return number is
begin
  return pFoo.num;
end;

function Ex2 (pFoo in TFoo)return varchar2 is
begin
  retutn pFoo.text;
end;

There, a bit too quick posting the first reply. You get the
idea :-)

Patrik
Thursday, February 19, 2004

Thank you, Patrik.  From what you provided, I was able to create the record type and implement it in the actual package body.  I am running into one more problem though and don't know if there is a solution for this one.

Here is an excerpt of my code:

FOR y IN 1 .. Z LOOP                                  --Z is a num
             
  atotal := 0;
  open c_count;                                          --open cursor
      LOOP
          FETCH c_count INTO atotal;
          EXIT WHEN c_count%NOTFOUND;
      END LOOP;
  close c_count;                                          --close cursor
 
  myArray(y).text:= atotal;                        --Doesn't work.

  atotal := 0;
  dateVarFrom := dateVarFrom + (7 * y);
  dateVarTo := dateVarTo + (7 * y);
             
END LOOP;

It doesn't like the use of the variable on the left-hand side of the assignment (myArray(y).txt).  The error is PLS-00308: This construct is not allowed as the origin of an assignment.

Is there someway to counteract this, or is it just not possible?

Thanks again for your help, Patrik.  And thank you to anyone else who can assist me on this one.

J

James Haefner
Thursday, February 19, 2004

James,

Check to see that the cursor c_count returns a value that is compatible with MyArray.text.

Can it be that c_count is declared as cursor%rowtype ?

This test of mine works....

create package test is

type TItem is record (
  test varchar2(20)
);

type TItemList is table of TItem index by binary_integer;

procedure test_proc;

end;
/

create package body test is

procedure test_proc is

ItemList TItemList;

begin
  for i in 1..10 loop
    ItemList(i).test:='Hello';
  end loop;

  for i in 1..10 loop
    dbms_output.put_line(ItemList(i).text);
  end loop;

end;

end;
/

So I take it, its a type missmatch problem of some kind, it is perfectly valid to use loop variables to index arrays.

In the case you rewrote some of the code for the purpose of posting it (changing variable names and such) make sure you are not using reserved words or data type names as variable names. That is illegal.

For further information, you can try searching over at
http://asktom.oracle.com

Patrik
Thursday, February 19, 2004

James,

Reading your code a bit more closely, I can see that this code will never work properly as written....

The inner loop:
      LOOP
          FETCH c_count INTO atotal;
          EXIT WHEN c_count%NOTFOUND;
      END LOOP;

Will loop over the result set each time the cursor is opened and closed; so it will always leave you with the last record in the table that c_count selects from.

You should do something along the lines of this:

y:=0;
open c_count;
LOOP
  y:=y+1;
  FETCH c_count INTO atotal;
  if c_count is not null then
    myArray(y).text:= atotal;
  end if;
  EXIT WHEN c_count%NOTFOUND;
END LOOP
close c_count;

and get rid of the outer loop completely. I dont know if this is what you want, but it seems more likely :-)

Since you are on Oracle 8i, you should try to use BULK FETCHES when fetching the cursors.

OPEN/FETCH/CLOSE is inefficient.

Good luck.

Patrik
Thursday, February 19, 2004

  Recent Topics

  Fog Creek Home