CODEWITHSUNDEEP
oracle-databasefunctionplsqluser-defined-types
Surya
Surya

Reputation: 591

How to return a table of values from a function in oracle

I have tried some of the answers and could not make my code work: Here is what I want and here is what I did: I have a table which is two column type and I want to query that table using a function to return exactly the same table to be routed to VB.NET so that I can display it in a DatagridView control. I am a novice to PL/SQL which is the problem for me. The first problem I intended to solve is to create the function.

-- DECLARE A RECORD TYPE
create or replace type shipper_type AS OBJECT
(
shipper_id number,  shipper_name varchar2(7)
);
/

CREATE TYPE t_shipper as table of shipper_type;
/

    create or replace function get_shipper return t_shipper is 
    temp_list t_shipper:= t_shipper();
    is  
     --  shipper_record shipper_type;
    begin
      for i in (  (select shipper.shipper_id, shipper.shipper_name) list from shipper) 
      loop
        temp_list.extend;
      temp_list(temp_list.last):= t_shipper(list);   
    end loop;

    return(temp_list);
   end get_shipper;
   /

When I try to compile this code I am getting the following errors: Errors for FUNCTION GET_SHIPPER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00103: Encountered the symbol "IS" when expecting one of the
         following:
         begin function pragma procedure subtype type <an identifier>
         <a double-quoted delimited-identifier> current cursor delete
         exists prior

6/63     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         . ( , * @ % & - + / at mod remainder rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as
         from || multiset

Upvotes: 0

Views: 460

Answers (2)

APC
APC

Reputation: 146239

You get those compilation messages because you have several syntax errors in your code. Two instances of IS, missing the type in the select, those unnecessary brackets in the return. You can correct those but you should simplify your code too.

The easiest way to populate a nested table is with a bulk collect.

create or replace function get_shipper return t_shipper is 
    temp_list t_shipper:= t_shipper();
begin
    select shipper_type(shipper.shipper_id, shipper.shipper_name) 
    bulk collect into temp_list
    from shipper;

    return temp_list ;
end get_shipper;
/

Upvotes: 2

Rene
Rene

Reputation: 10541

Try it without () in the return statement and add an end; at the end.

return temp_list; 

end;

Also you might want to take a look at so called pipelined functions: http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

Upvotes: 0

Related Questions