Creating a function to return multiple columns and multiple rows but it errors out

Question

I am trying to create a function called get_adjusted_income_info(name) which returns multiple columns and multiple rows. For example, running SELECT get_adjusted_income_info('Sarah') FROM DUAL will return:

name	source	income
Sarah	Car wash	256
Sarah	Management	462
Sarah	Stocker	764

From this point I want to create a view that loops through all the names in the employees table and executes the get_adjusted_income_info() function. This will result in a view with all of the employees adjusted incomes like so:

name	source	income
Bob	Car wash	391
Bob	Cashier	19
Sarah	Car wash	256
Sarah	Management	462
Sarah	Stocker	764

I can't get my function to work as it tells me there is a ORA-00900: invalid SQL statement. However, I can't figure it out.

Table/Sample data:

CREATE TABLE employees (
name varchar(32) NOT NULL,
age NUMBER NOT NULL
);

CREATE TABLE incomes (
name varchar(32) NOT NULL,
source varchar(32) NOT NULL,
amount NUMBER NOT NULL
);

INSERT INTO employees (name, age) VALUES ('Bob', 30);
INSERT INTO employees (name, age) VALUES ('Sarah', 25);

INSERT INTO incomes (name, source, amount) VALUES ('Bob', 'Car wash', 391);
INSERT INTO incomes (name, source, amount) VALUES ('Bob', 'Cashier', 19);
INSERT INTO incomes (name, source, amount) VALUES ('Sarah', 'Car wash', 128);
INSERT INTO incomes (name, source, amount) VALUES ('Sarah', 'Management', 231);
INSERT INTO incomes (name, source, amount) VALUES ('Sarah', 'Stocker', 382);

My attempt at creating the function:

CREATE OR REPLACE TYPE income_info_obj AS OBJECT( name varchar(32), source varchar(32), amount NUMBER); 
CREATE OR REPLACE TYPE income_info_tbl AS TABLE OF income_info_obj; 

CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar(32)) RETURN income_info_tbl
    PIPELINED
AS
    income_tbl   income_info_tbl;
    PRAGMA autonomous_transaction;
BEGIN
    
    SELECT
        vw_result_typ(
          e.name,
          i.source,
          CASE e.age < 30 THEN i.income * 2
          ELSE i.income as income
        )
    BULK COLLECT
    INTO income_tbl
    FROM
        employees e
    LEFT JOIN incomes i
        ON e.name = i.name
    WHERE e.name = p_name;


    FOR i IN 1..income_tbl.count LOOP
        PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
    END LOOP;
    
END get_adjusted_values;
/

PS: I understand I can do a JOIN with some case statements, but this is me trying to keep my question simple.

Answer 1

Here you go...top to the bottom through the debugging process

SQL> CREATE TABLE employees (
  2  name varchar(32) NOT NULL,
  3  age NUMBER NOT NULL
  4  );

Table created.

SQL>
SQL> CREATE TABLE incomes (
  2  name varchar(32) NOT NULL,
  3  source varchar(32) NOT NULL,
  4  amount NUMBER NOT NULL
  5  );

Table created.

SQL>
SQL> INSERT INTO employees (name, age) VALUES ('Bob', 30);

1 row created.

SQL> INSERT INTO employees (name, age) VALUES ('Sarah', 25);

1 row created.

SQL>
SQL> INSERT INTO incomes (name, source, amount) VALUES ('Bob', 'Car wash', 391);

1 row created.

SQL> INSERT INTO incomes (name, source, amount) VALUES ('Bob', 'Cashier', 19);

1 row created.

SQL> INSERT INTO incomes (name, source, amount) VALUES ('Sarah', 'Car wash', 128);

1 row created.

SQL> INSERT INTO incomes (name, source, amount) VALUES ('Sarah', 'Management', 231);

1 row created.

SQL> INSERT INTO incomes (name, source, amount) VALUES ('Sarah', 'Stocker', 382);

1 row created.

SQL>
SQL> CREATE OR REPLACE TYPE income_info_obj AS OBJECT( name varchar(32), source varchar(32), amount NUMBER);
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE income_info_tbl AS TABLE OF income_info_obj;
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar(32)) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          vw_result_typ(
 10            e.name,
 11            i.source,
 12            CASE e.age < 30 THEN i.income * 2
 13            ELSE i.income as income
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END get_adjusted_values;
 29  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/49     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

12/27    PLS-00103: Encountered the symbol "THEN" when expecting one of
         the following:
         * & - + / at mod remainder rem when <an exponent (**)> and or
         || multiset
         The symbol "* was inserted before "THEN" to continue.

13/11    PLS-00103: Encountered the symbol "ELSE" when expecting one of
         the following:
         * & - + / at mod remainder rem when <an exponent (**)> and or
         ||

SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          vw_result_typ(
 10            e.name,
 11            i.source,
 12            CASE e.age < 30 THEN i.income * 2
 13            ELSE i.income as income
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END get_adjusted_values;
 29  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
12/16    PL/SQL: ORA-00907: missing right parenthesis
28/5     PLS-00113: END identifier 'GET_ADJUSTED_VALUES' must match
         'GET_ADJUSTED_INCOME_INFO' at line 1, column 10

SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          vw_result_typ(
 10            e.name,
 11            i.source,
 12            CASE e.age < 30 THEN i.income * 2
 13            ELSE i.income as income
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END;
 29  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
12/16    PL/SQL: ORA-00907: missing right parenthesis
SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          vw_result_typ(
 10            e.name,
 11            i.source,
 12            CASE e.age < 30 THEN i.income * 2
 13            ELSE i.income end as income
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END;
 29  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
12/16    PL/SQL: ORA-00907: missing right parenthesis
SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          vw_result_typ(
 10            e.name,
 11            i.source,
 12            CASE when e.age < 30 THEN i.income * 2
 13            ELSE i.income end as income
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END;
 29  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
13/29    PL/SQL: ORA-00907: missing right parenthesis
SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          income_info_obj(
 10            e.name,
 11            i.source,
 12            CASE when e.age < 30 THEN i.income * 2
 13            ELSE i.income end as income
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END;
 29  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
13/29    PL/SQL: ORA-00907: missing right parenthesis

SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          income_info_obj(
 10            e.name,
 11            i.source,
 12            CASE when e.age < 30 THEN i.income * 2
 13            ELSE i.income end
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_info_obj( income_tbl(i).name, income_tbl(i).source, income_tbl(i).income ) );
 26      END LOOP;
 27
 28  END;
 29  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
13/16    PL/SQL: ORA-00904: "I"."INCOME": invalid identifier
25/9     PL/SQL: Statement ignored
25/93    PLS-00302: component 'INCOME' must be declared
SQL>
SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          income_info_obj(
 10            e.name,
 11            i.source,
 12            CASE when e.age < 30 THEN i.income * 2
 13            ELSE i.income end
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_tbl(i) );
 26      END LOOP;
 27
 28  END;
 29  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION GET_ADJUSTED_INCOME_INFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
13/16    PL/SQL: ORA-00904: "I"."INCOME": invalid identifier
SQL>
SQL> CREATE OR REPLACE FUNCTION get_adjusted_income_info(p_name varchar) RETURN income_info_tbl
  2      PIPELINED
  3  AS
  4      income_tbl   income_info_tbl;
  5      PRAGMA autonomous_transaction;
  6  BEGIN
  7
  8      SELECT
  9          income_info_obj(
 10            e.name,
 11            i.source,
 12            CASE when e.age < 30 THEN i.amount * 2
 13            ELSE i.amount end
 14          )
 15      BULK COLLECT
 16      INTO income_tbl
 17      FROM
 18          employees e
 19      LEFT JOIN incomes i
 20          ON e.name = i.name
 21      WHERE e.name = p_name;
 22
 23
 24      FOR i IN 1..income_tbl.count LOOP
 25          PIPE ROW ( income_tbl(i) );
 26      END LOOP;
 27
 28  END;
 29  /

Function created.

SQL> sho err
No errors.
SQL>
SQL>