CODEWITHSUNDEEP
sqloracle-databaseplsql
Adrian
Adrian

Reputation: 836

PL/SQL - Returning a table from a function

I am creating a function that returns a table as a result. This function is basically doing comparison between two tables, and returns data set that if two tables contain different data.
P_min_id and p_max_id are ignorable, and will be implemented in the future once this function is working.
I modified the original code from http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html. The error messages are commented in the following code.

create or replace function return_objects(
  p_min_id in number,
  p_max_id in number
)
return t_nested_table as
  v_ret   t_nested_table;
begin
  select *              // Error(8,7): PL/SQL: SQL Statement ignored
  into
    v_ret
  from 
  (
    select 
    *
    from 
    (
      select * from 
      (
        select * from SCHEMA.TEST            
        minus
        select * from SCHEMA.TEST_1
      )
      union all
      select * from
      (
        select * from SCHEMA.TEST_1
        minus
        select * from SCHEMA.TEST
      )
    )
  )
return v_ret;           

end return_objects;

It would be appreciated if you can fix my code, but I want to know why this code should not work. Please give me some keyword, so that I can research. or relevant website for reference would be also appreciated.

Upvotes: 0

Views: 1127

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

Add a semicolon after the end of the SQL statement and use bulk collect to populate the nested table. Here's a working example:

create or replace type t_nested_table is table of varchar2(100);

create or replace function return_objects(
  p_min_id in number,
  p_max_id in number
)
return t_nested_table as
  v_ret   t_nested_table;
begin
  select *
  bulk collect into v_ret
  from 
  (
    select 'A' from dual union all
    select 'B' from dual
  );

  return v_ret;

end;
/

select return_objects(1,2) from dual;

UPDATE

Based on the question edits there is also a privilege issue. Your user probably has access to the tables through a role, but to create a function those privileges should be granted directly to your user.

Upvotes: 4

Related Questions