how to generate a liste from a table by plsql function?

Question

I need to create a function to generate a list from a table by a select query. First, I created a package and function to generate a table but I get ERROR.

CREATE OR REPLACE PACKAGE test AS
    TYPE date_record IS RECORD(
       list_target VARCHAR2(50));
    TYPE date_table IS TABLE OF date_record;
    FUNCTION get_ups(partition_date_in VARCHAR2)
        RETURN date_table
        PIPELINED;
END;


CREATE OR REPLACE PACKAGE BODY test AS
    FUNCTION get_ups(partition_date_in VARCHAR2)
        RETURN date_table
        PIPELINED IS
        rec            date_record;
    BEGIN
        SELECT ltrim(TO_CHAR(MONTH,'mm-yyyy'),'0') BULK COLLECT AS rec
            FROM 
                (
                select add_months (trunc (to_date('09/01/2019','dd/mm/yyyy'), 'MM'), 1*Level -1)
                Month   FROM Dual
                CONNECT BY Level <= MONTHS_BETWEEN(to_date('09/01/2019','dd/mm/yyyy'), to_date('09/02/2019','dd/mm/yyyy')) + 1
                order by MONTH
                );
        -- you would usually have a cursor and a loop here   
        PIPE ROW (rec);
        RETURN;
    END get_ups;
END;

When I run

SELECT *  FROM table(test.get_ups('09/01/2019'));

Errore SQL [4063] [72000]: ORA-04063: package body "SYS.TEST" contiene errori

Can you please help to solve this issue and generate a list?

`package body "SYS.TEST" ` For future reference please don't use the SYS schema for your own objects. It is an Oracle-maintained schema and vital to the integrity of your database. Using it for your own purposes risks breaking your database (and in real life it would invalidate your Oracle Support contract). Use SYS to create a user account and grant it privileges, then use that schema to build your programs. — APC, Dec 03 '19 at 17:37
Run `select * from user_errors;` to see what compilation errors affect that package. — TenG, Dec 03 '19 at 19:57

Littlefoot · Answer 1 · 2019-12-04T15:55:39.187

You are dealing with dates, but yet want to pass and retrieve strings; why would you want to do that?

If I understood what you want to do, it is to pass date parameter to a function and get list of dates. As you never used the parameter in your code, I presumed that you'd want to get dates between date (passed as a parameter) and sysdate.

Here's how you'd do that.

As you want to use it from SQL, you'll have to create types at that level:

SQL> create or replace type t_my_row is object (val date);
  2  /

Type created.

SQL> create or replace type t_my_tab is table of t_my_row;
  2  /

Type created.

SQL>

Package spec & body:

SQL> create or replace package test as
  2        function get_ups(par_date in date) return t_my_tab;
  3  end;
  4  /

Package created.

SQL> create or replace package body test as
  2    function get_ups (par_date in date)
  3      return t_my_tab
  4    is
  5      l_tab   t_my_tab := t_my_tab();
  6    begin
  7      select t_my_row(add_months (trunc(par_date, 'MM'), Level - 1)) month
  8        bulk collect into l_tab
  9        from dual
 10       connect by level <= abs(months_between(par_date, sysdate) + 1);
 11
 12      return l_tab;
 13    end get_ups;
 14  end test;
 15  /

Package body created.

SQL>

Testing: function returns DATES. Apply TO_CHAR function to format it as you want, e.g. MM-YY

SQL> select to_char(val, 'mm-yy') result
  2    from table(test.get_ups(date '2019-01-09'));

RESULT
------
01-19
02-19
03-19
04-19
05-19
06-19
07-19
08-19
09-19

9 rows selected.

SQL>

If that's what you wanted, fine. If not, adjust the function so that it calculates what you want, now that you have the general idea.

P.S. Oh, yes: error you got (with your code) says that BULK COLLECT requires INTO (not AS).

But, even if you fix it, the next error you'll get will be

cannot mix between single row and multi-row (BULK) in INTO list

and so forth. If you're patient enough, you might reach the end and eventually compile package body. If not, have a look at my code.

Thank you for your interaction. the goal is to generate a list of dates MM-YYYY [1-19,2-19,...], but between I need to create a table of dates. By the way, I run ur code, I got Errore SQL [904] [42000]: ORA-00904: "GET_UPS": identificativo non valido — Siham MB, Dec 04 '19 at 08:43
You're welcome. I apologize for misleading you, it wasn't my intention ... First i created a standalone function, then a package, and - when posting test results - called the wrong one. Sorry again. **Anyway**: I fixed my code, you should be able to run it without problems. The difference between "then" and "now" is that types are created at SQL level, not within the package specification. The rest is more or less the same. — Littlefoot, Dec 04 '19 at 15:57

score -1 · Answer 2 · answered Dec 03 '19 at 15:45

I don't know the purpose of your code but if you change it to:

CREATE OR REPLACE PACKAGE BODY tst AS
    FUNCTION get_ups(partition_date_in VARCHAR2)
        RETURN date_table
        PIPELINED IS
        rec            date_record;
    BEGIN
        for cur in (
        SELECT ltrim(TO_CHAR(MONTH,'mm-yyyy'),'0')  r
            FROM 
                (
                select add_months (trunc (to_date('09/01/2019','dd/mm/yyyy'), 'MM'), 1*Level -1)
                Month   FROM Dual
                CONNECT BY Level <= MONTHS_BETWEEN(to_date('09/01/2019','dd/mm/yyyy'), to_date('09/02/2019','dd/mm/yyyy')) + 1
                order by MONTH
                ))
        loop
            PIPE row(date_record(cur.r));
        end loop;
    END get_ups;
END;
/

It will work.

Unfortunately no, I get this ERROR "Errore SQL [900] [42000]: ORA-00900: istruzione SQL non valida" — Siham MB, Dec 03 '19 at 16:19
Why in the loop you called date_record as a function? it s not a function — Siham MB, Dec 03 '19 at 17:08

how to generate a liste from a table by plsql function?

2 Answers2