How to return multiple value in Oracle Database 12c

Question

I know this question was asked in

Oracle: Return multiple values in a function

or

Returning multiple values from an Oracle 12c function

I followed them but it is causing error, I can not compile it. I am missing something, so I need help please.

my code is

create or replace type child_type AS OBJECT
(
  child_id_number varchar2(2000),
  child_name varchar2(2000),
  other_id varchar2(2000)
);


        CREATE or replace function children_b
        (
                   i_id_number IN VARCHAR2
        )
        RETURN child_type
        AS

child_record child_type;



    BEGIN

                  SELECT LISTAGG(ch.child_id_number, ', ')WITHIN GROUP (ORDER BY ch.child_id_number),
                         LISTAGG(e.mail_name, ', ')WITHIN GROUP (ORDER BY e.mail_name),
                         LISTAGG(ib.other_id,', ')WITHIN GROUP (ORDER BY ib.other_id)

    INTO child_type.child_id_number,child_type.child_name,child_type.other_id

                         FROM entity e
                         JOIN children ch ON ch.child_id_number = e.id_number
                         JOIN ids_base ib ON ib.id_number = ch.child_id_number
                         WHERE ib.ids_type_code = 'BAN'
                         AND ch.id_number IN (i_id_number)
                         GROUP BY ch.id_number;


        return(child_record);



        End children_b;

The error message is Compilation errors for FUNCTION TU_ADIS.TU_CHILDREN_B

Error: PLS-00330: invalid use of type name or subtype name Line: 23 Text: INTO child_type.child_id_number,child_type.child_name,child_type.other_id

Error: PL/SQL: ORA-00904: : invalid identifier Line: 24 Text: FROM bio_entity e

Error: PL/SQL: SQL Statement ignored Line: 20 Text: SELECT LISTAGG(ch.child_id_number, ', ')WITHIN GROUP (ORDER BY ch.child_id_number),

THANK YOU SO MUCH.

Your INTO clause references the variable by its type rather than by its name,child_record. — APC
– APC, Commented Feb 1, 2017 at 17:05

BriteSponge · Accepted Answer · 2017-02-01 17:06:41Z

1

In your INTO clause change the

child_type.child_id_number,child_type.child_name,child_type.other_id

to

child_record.child_id_number,child_record.child_name,child_record.other_id

You are retrieving into an instance of the object not the object itself. I've just created your function and that works for me.

answered Feb 1, 2017 at 17:06

BriteSponge

1,0549 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SASPYTHON Over a year ago

thanks you so muuch!!!!! now I having this error ORA-06530 Reference to Uninitialized when I run the function. you knwo what it is?

BriteSponge Over a year ago

Not sure how you are running it but the object instance returned by the function will need to be returned into something and that something will need initializing.

Collectives™ on Stack Overflow

How to return multiple value in Oracle Database 12c

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related