Reputation: 21285

Defining a type inside an Oracle pl/sql function

I am trying to create a pl/sql function (my first take at pl/sql functions) that converts base 10 number to base 26 string (my base 26 will be A..Z).

create or replace function generateId(numericId IN NUMBER) RETURN VARCHAR2 AS
declare
  type array_t is varray(26) of CHAR;
  char_array array_t := array_t('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
  res varchar2(3);
  targetBase INTEGER := char_array.count;
begin
  LOOP
      res = char_array[REMAINDER(numericId, targetBase)] + result;
      numericId = numericId / targetBase;
  EXIT WHEN (numericId = 0);

  RETURN res;
end;

The error I am getting is:

Error(2,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

My guess is I'm sticking declaration into a wrong place but I can't figure out where it should go.

Upvotes: 2

Answers (4)

Debasish

Reputation: 51

No need to write to declare after CREATE OR REPLACE. Your declare section has already started from create keyword. Again there should be end loop if you don't want to put it in infinite loop.

Upvotes: 0

Marmite Bomber

Reputation: 21063

Beside of the syntax problems here some additional points:

check the input variable before the loop (to avoid endless looping)

use MOD and FLOOR instead of REMAINDER which is using ROUND

Here a sample solution (without arrays - that can be easily added)

create or replace function generateId(numericId IN NUMBER) RETURN VARCHAR2 AS
  v_num NUMBER;
  res varchar2(100);
begin
  v_num := numericId;
  if (v_num < 0 OR v_num != trunc(v_num)) then 
     return NULL; /* or raise exeption */
  end if;

  LOOP
     res := chr(ascii('A')  + MOD(v_num, 26)) || res;
     v_num := FLOOR(v_num/26);
  EXIT WHEN (v_num = 0);
  END LOOP;
  RETURN res;
end;
/



select generateId(35286) code from dual;

CODE     
-----
CAFE

Upvotes: 2