Returning an array from a stored procedue.



Can't  figure out the correct syntax. for passing output array. Most of the examples I've found use 'packages' which are not viable in this application which specifically needs a straight stored procedure.that also does NOT make use of a cursor as output.

Receive following compilation error.

Line: 3 Column: 26  Error: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

   <an identifier> <a double-quoted delimited-identifier>
The symbol "<an identifier> was inserted before "TABLE" to continue.

create or replace
PROCEDURE               TESTUSR.SP_ODR_GETDATACUSTODIANV3 
(  O_RETURN OUT Varchar2.TABLE%TYPE) 
AS 
 
TYPE MyArray is TABLE of Varchar2(80) Index by BINARY_INTEGER;
 
BEGIN
  DECLARE
    p_OneName Varchar(100);
  p_Records SYS_REFCURSOR;
  p_Values Varchar2(32767);
  CRLF1      CONSTANT CHAR(2) := CHR(13)||CHR(10); 
    p_Array MyArray;
    p_Count int :=1 ;
 BEGIN
  open p_Records FOR 
   SELECT distinct SA_DATACUSTODIAN 
    from Roscoe.INT_SA_REMEDY 
    where upper(Substr(SA_Status,1,1))='A'
    order by SA_DATACUSTODIAN;
         
    LOOP
    fetch p_Records into p_OneName;
    exit when p_Records%notfound;
      p_Array(p_Count) := p_OneName;
      p_Count := p_Count + 1;
    p_Values := p_Values || p_OneName || CRLF1;
    -- p_Values := p_Values || p_OneName || ',' ;
    -- dbms_output.put_line(p_OneName);
  END LOOP;
  
  O_RETURN:=p_Array;
 
 
 END;
END SP_ODR_GETDATACUSTODIANV3;

Open in new window

aszymcikAsked:
Who is Participating?
 
sdstuberCommented:
you don't have to use that exact type,

as mentioned before you could create a package with a more customized associative array declaration

for example

create or replace my_package is
   TYPE MyArray is TABLE of Varchar2(80) Index by BINARY_INTEGER;
end my_package;

then declare your parameter as

(  O_RETURN OUT my_package.myarray )


   
0
 
slightwv (䄆 Netminder) Commented:
You cannot create/reference generic objects that way.

You need to create the TYPE in the database itself then reference that data type in the OUT parameter.
0
 
slightwv (䄆 Netminder) Commented:
Outside the procedure:
create TYPE MyArray is TABLE of Varchar2(80) Index by BINARY_INTEGER;


Then:
PROCEDURE               TESTUSR.SP_ODR_GETDATACUSTODIANV3
(  O_RETURN OUT MyArray)
AS
...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
declare your parameter  and p_array to be of type dbms_sql.varchar2a


(  O_RETURN OUT dbms_sql.varchar2a )


or, if you don't want to use dbms_sql,  create a separate package that defines your own associative array type.

your app doesn't have to call that package, it can call your procedure

you might also want to look at the some of the options of whatever app you have, it would be an odd restriction to be unable to support packages.
0
 
sdstuberCommented:
>>> create TYPE MyArray is TABLE of Varchar2(80) Index by BINARY_INTEGER;

this is illegal syntax

you could create a nested table type, but those are used differently than the code you have

0
 
slightwv (䄆 Netminder) Commented:
>>this is illegal syntax

Correct.  I apologize for the quick copy/paste.
0
 
aszymcikAuthor Commented:
Have used suggestion from

sdstuber:
declare your parameter  and p_array to be of type dbms_sql.varchar2a

(  O_RETURN OUT dbms_sql.varchar2a )

to successfully compile package, still awaiting confirmation from developer of 3rd party app (that has these unique requirements)   that are able to to succcessfully modify their code to utilize this format..
0
 
aszymcikAuthor Commented:
Assistance was much appreciated!

Sorry for taking so long to accept solution,  as I was waiting for confirmation from a 3rd party that approach was workable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.