How to use sequence in a function in PLSQL?

Question

1. In my schema, I have a reference number column in my settlement table with a null value of varchar2 type, that I want to update it continuously.
2. Then I've created a sequence called ref_seq_num using a built-in function.

3. I want to use it (ref_seq_num) within my function get_ref_num to update the sequence ref. number to my settlement table, which the return type also is varchar2 and I have a function like below

   CREATE OR REPLACE FUNCTION get_ref_num RETURN settlement.ref_nr %TYPE IS
     v_ref_seq settlement.ref_nr%TYPE;
   BEGIN
     v_ref_seq := to_char(sysdate, 'YYYYMMDD')||LPAD(ref_seq_num.nextval, 8,'0');
     RETURN v_ref_seq; 
   END get_ref_num;
   

However, I bum into this error message 1/55 PLS-00302: component 'ref_nr' must be declared. I also tried changing the data type to varchar2 and error message is PLS-00215: String length constraints must be in range (1 .. 32767) How can I fix it?

Answer 1

According to your code, it seems that there's a table whose name is SETTLEMENT, but it doesn't contain the REF_NR column.

The following example shows how to do that:

SQL> create sequence ref_seq_num;

Sequence created.

A table that does contain the REF_NR column (which is then used in the function):

SQL> create table settlement (ref_nr varchar2(20));

Table created.

Your code, unmodified:

SQL> CREATE OR REPLACE FUNCTION get_ref_num RETURN settlement.ref_nr %TYPE IS
  2    v_ref_seq settlement.ref_nr%TYPE;
  3  BEGIN
  4    v_ref_seq := to_char(sysdate, 'YYYYMMDD')||LPAD(ref_seq_num.nextval, 8,'0');
  5    RETURN v_ref_seq;
  6  END get_ref_num;
  7  /

Function created.

Testing:

SQL> select get_ref_num from dual;

GET_REF_NUM
--------------------------------------------------------------------------------
2019050400000001

SQL>

Answer 2

If you have a column called ref_nr within settlement table, you code must work properly. I think the problem in the second case raises due to missing data precision part ( should be such as varchar2(16) ) for defining the variable as v_ref_seq varchar2. I would prefer using a numeric type such as number or int to hold the values for ref_nr, since they are all numeric, and this data type protects the data remain as numeric. Whenever you need to query you may use to_char function preventing exponential display( select to_char(ref_nr) from settlement ).

Moreover, if you use Oracle 12c version, you don't need to create such an extra function, just alter your table so that being sequence as your default for the column :

alter table settlement 
modify ref_nr default to_char(sysdate, 'yyyymmdd')||lpad(ref_seq_num.nextval, 8,'0');