CODEWITHSUNDEEP
sqloracle-databaseplsql
Feyam
Feyam

Reputation: 21

Invalid Datatype with user defined function

I created this package and function to return a sequence of dates, given a start date and stop date (optional).
The package compiles and I can see that it is there with no errors in TOAD. Anytime I try to execute the function, I receive an error stating:

[Error] Execution (10: 8): ORA-00902: invalid datatype

I do have the right privileges as Ive created other packages with procs and functions; therefore, I'm sure its due to problems with my code.

The logic is below:

CREATE OR REPLACE PACKAGE TEST_PKG AS


   TYPE table_of_dates IS TABLE OF date;
   
   FUNCTION get_date_table(p_begin in date
                          ,p_stop in date default sysdate
                          ,p_next in integer default 1)
        RETURN table_of_dates;
   
END TEST_PKG;

/



CREATE OR REPLACE package body TEST_PKG as


function get_date_table(p_begin in date
                       ,p_stop in date default sysdate
                       ,p_next in integer default 1)
        RETURN table_of_dates
         IS
            
            date_table    table_of_dates;
            
         BEGIN
            
            with date_sq as (
                select 
                    p_begin begin_date
                    ,p_stop end_date from dual
            )
            select
                d.begin_date + level - p_next
            bulk collect into date_table
            from date_sq d
            where
                1=1
            /* TODO: Check this math */
            connect by level <= (d.end_date - d.begin_date) + p_step
            ;


        RETURN date_table;
    END get_date_table;

end TEST_PKG;
/

Some example code to try to test the function is:

select TEST_PKG.get_date_table(sysdate) from dual; --  <--causes error shown above

Upvotes: 0

Views: 651

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

If you want to use that type at SQL level (you do), then you have to create it at SQL level, not within the package.

SQL> create or replace type table_of_dates is table of date;
  2  /

Type created.

Package spec:

SQL> CREATE OR REPLACE PACKAGE TEST_PKG AS
  2
  3
  4  --   TYPE table_of_dates IS TABLE OF date;
  5
  6     FUNCTION get_date_table(p_begin in date
  7                            ,p_stop in date default sysdate
  8                            ,p_next in integer default 1)
  9          RETURN table_of_dates;
 10
 11  END TEST_PKG;
 12
 13  /

Package created.

Package body: what is p_step at line #25? You never declared it, so I commented it.

SQL> CREATE OR REPLACE package body TEST_PKG as
  2
  3  function get_date_table(p_begin in date
  4                         ,p_stop in date default sysdate
  5                         ,p_next in integer default 1)
  6          RETURN table_of_dates
  7           IS
  8
  9              date_table    table_of_dates;
 10
 11           BEGIN
 12
 13              with date_sq as (
 14                  select
 15                      p_begin begin_date
 16                      ,p_stop end_date from dual
 17              )
 18              select
 19                  d.begin_date + level - p_next
 20              bulk collect into date_table
 21              from date_sq d
 22              where
 23                  1=1
 24              /* TODO: Check this math */
 25              connect by level <= (d.end_date - d.begin_date) -- + p_step
 26              ;
 27
 28
 29          RETURN date_table;
 30      END get_date_table;
 31
 32  end TEST_PKG;
 33  /

Package body created.

Testing:

SQL> select * From table(test_pkg.get_date_table(date '2021-07-15'));

COLUMN_VAL
----------
15.07.2021
16.07.2021
17.07.2021
18.07.2021
19.07.2021

SQL>

Seems to be OK (as far as the error you reported is concerned).

Upvotes: 2

Related Questions