Oracle: Create function that returns multiple rows

Question

I have created a type inside a package and declared a function as below.

type backupStatus_t is table of d_backupstatus%ROWTYPE;

function getLatestBackupInfo return backupStatus_t;

Now, inside the package body, I have defined the function as below.

function getLatestBackupInfo RETURN backupStatus_t as
      v_backupStatus backupStatus_t;
      begin
        select * bulk collect into v_backupStatus from d_backupstatus;
        return v_backupStatus;
      end;

Package compiles successfully. When I try to call this function, I am getting "invalid datatype".

Can anyone help??

Please edit your question and show example code where you're trying to call this function. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Dec 25, 2021 at 18:33
Please describe the problem you want to solve and DB version you use. PL/SQL types are not known to SQL. But if you really want to return dataset from a function, you may consider SQL macro or pipelined function. — astentx
– astentx, Commented Dec 25, 2021 at 22:34

Mohamad TAGHLOBI · Accepted Answer · 2021-12-25 18:22:48Z

1

Try to create your "backupStatus_t" type outside of your package, like :

CREATE OR REPLACE TYPE backupStatus_t AS TABLE OF d_backupstatus%ROWTYPE;

answered Dec 25, 2021 at 18:22

Mohamad TAGHLOBI

5916 silver badges11 bronze badges

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

1 Comment

William Robertson Over a year ago

Good approach, but d_backupstatus%ROWTYPE needs the PL/SQL compiler so won't work in SQL.

Collectives™ on Stack Overflow

Oracle: Create function that returns multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related