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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.