Oracle - How to force a field size on a Function return

Question

I hope to be clear on this issue , I will define in a general way but I believe that enough

1) I create any FUNCTION that returns a VARCHAR, it does not interfere with the return

2) I create a VIEW using this FUNCTION

Result : The field size that uses the FUNCTION in the VIEW is VARCHAR(4000)

Question : Is there any way in FUNCTION that I can specify the return size ?

Code Example :

create or replace FUNCTION EXEMPLO ( pX IN VARCHAR2)

  RETURN  VARCHAR2 IS

  vSAIDA  VARCHAR2(6);
BEGIN

    --FAZ ALGUMA COISA E OBTEM VSAIDA;

END IF;

    RETURN vSAIDA;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN ' ';
END;

CREATE OR REPLACE VIEW V_EXEMPLO
AS
SELECT EXEMPLO('1') SAIDA FROM DUAL;

Answer 1

Unfortunately in user-defined functions it is not possible to specify the return size of the VARCHAR2.

As you yourself noted, even guaranteed that the function never returns a VARCHAR2 greater than 6, the type of return will be VARCHAR2(4000) (or 32767 if you enable extended strings in Oracle 12c).

In the vast majority of cases this does not make much difference since the space used to store a variable-sized string is independent of the size specified in the type (e. g., 'SALADA' occupies the same space as VARCHAR2(10) than would occupy as VARCHAR2(255)).

That said, for the sake of correctness and for cases where the type definition is important to ensure the form of future entries (e.g., in a type command CREATE TABLE AS) it is always possible to make a CAST return of function:

CREATE OR REPLACE VIEW V_EXEMPLO
AS
SELECT CAST(EXEMPLO('1') AS VARCHAR2(6)) SAIDA FROM DUAL;

---

Source: Dbaspot - How to set the size of string returned by PL/SQL functions?

Answer 2

In your case, to specify the size of a return you can simply enjoy the functions REPLACE and LPAD or RPAD.

Example: return SUBSTR(LPAD('1', 6, '0'), 0, 6);

Assuming your return has to be exactly 6 characters, this instruction using the LPAD will fill the left side of the return with 0 until it has length equal to 6, if the length is greater than 6, using the REPLACE it will delimit the length up to 6, discarding the remainder.

Is that clear? I hope I’ve helped.