CODEWITHSUNDEEP
oracle-databaseplsql
user12333071
user12333071

Reputation: 35

Trying to Create a Generate_Series Function in Oracle db

create
or replace Function Generate_Series (
  MinNumber INTEGER,
  MaxNumber INTEGER,
  NumberToSkip INTEGER
) Return Numbers_t 
As 
  newNumber INTEGER: = MinNumber;
  Numbers_t Numbers_x: = Numbers_t();
Begin 
  for x in (
  with s as (
    Select
      rNo
    From
      (
        select
          level rno
        from
          dual connect by level <= enNumber
      ) A
    Where
      A.rNo >= stNumber
  )
  select
    rno
  from
    s
) loop 
if x.rno <> MinNumber then 
  newNumber: = newNumber + NumberToSkip;
end if;
if newNumber >= MaxNumber then 
  Return;
end if;
  Numbers_x.EXTEND; 
  Numbers_x(Numbers_x.COUNT): = newNumber;
  end loop;
  Return Numbers_x;
End;

Seeing this error 

"Function GENERATE_SERIES compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
0/0       PL/SQL: Compilation unit analysis terminated
13/8      PLS-00320: the declaration of the type of this expression is incomplete or malformed
Errors: check compiler log"

Can someone help me with this TIA.

Upvotes: 2

Views: 3801

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

Not to piddle on your party, but:

  1. Calling user-defined functions from SQL in Oracle is generally a Bad Thing, causing no end of problems.

  2. If you need to generate a series of numbers in Oracle the usual way to do it is with a simple SELECT...FROM DUAL query, as follows:

    SELECT LEVEL-1 FROM DUAL CONNECT BY LEVEL-1 < 24

This generates a list of number from 0 to 23 - adjust as needed.

Upvotes: 2

user330315
user330315

Reputation:

You are overcomplicating things. You don't need a SELECT or FROM DUAL or anything similar.
A plain FOR loop is enough.

Assuming numbers_t is defined as:

CREATE OR REPLACE TYPE numbers_t AS TABLE OF NUMBER;

Then you can use this:

CREATE FUNCTION generate_series (minnumber INTEGER, maxnumber INTEGER)
   RETURN numbers_t
   PIPELINED
   DETERMINISTIC
IS
BEGIN
   FOR i IN minnumber .. maxnumber LOOP
      PIPE ROW (i);
   END LOOP;
   RETURN;
END;
/

To use the function, you need:

SELECT * 
FROM TABLE(generate_series(1,42))

Upvotes: 3

Related Questions