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

Question

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

Navigation

#1 by (2 votes)
#2 by (1 votes)

5

I hope to be clear on this issue, I will define it in a generic way but I think it's enough

1) I create a FUNCTION any that returns a VARCHAR, does not matter much what is returned

2) I create a VIEW that uses this FUNCTION

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

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

Sample Codes:

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;

oracle oracle11c

asked by anonymous 11.09.2015 / 16:19

2 answers

Find all instances of a pattern in a text Relate two mysql tables with data from the same column?

score 2 · Answer 1

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

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

In most cases this does not make much difference since the space used to store a variable-length string is independent of the size specified in the type (eg, 32767 occupies the same space as 'SALADA' than it would occupy as VARCHAR2(10) ).

That said, for reasons of corretude and for cases where the type definition it is important to ensure the form of future entries (eg, in a command of type VARCHAR2(255) ) it is possible to make a CREATE TABLE AS of the function return:

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

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

score 1 · Answer 2

In your case, to specify the size of a return you can simply enjoy the functions SUBSTR 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 LPAD will fill the left side of the return with 0 until it has the length equal to 6, if the length is greater than 6, using SUBSTR it will delimit the length to 6, discarding the remainder.

Is it clear? I hope I have helped.