Geek Answers Handbook

Why does the oracle plsql varchar2 variable need size, but no parameter?

Suppose you have this procedure:

PROCEDURE f (param VARCHAR2) IS var VARCHAR2(10); BEGIN var := 'hi'; END f;

I would like to understand why var specified length is required, but param not. I find it difficult to find information about this in oracle documents. A.

+5
oracle plsql varchar2
source share
2 answers

"Oracle Database derives the length, precision, and scale of an argument from the environment from which the procedure is called."

Please check out this related issue .

Ref: Oracle® Database Reference 10g Release 2 (10.2) Please see semantics / argument / datatype.

+4
source share

The difference is that the subroutine headers have formal parameters, which are replaced by the actual parameters when the subroutine is called:

 create or replace function f( p_x in varchar2 /* a formal parameter */ ,p_y in varchar2 /* a formal parameter */ ) return varchar2 /* a formal parameter */ is begin return p_x || p_y; end; declare v_z varchar2(10); v_x constant varchar2(1) := 'X'; begin v_z := f(v_x, 'Y'); /* actual parameters */ end;

The formal parameter is not limited (but limited subtypes can be used) and also includes information about the parameter mode and the possible default value, which are not relevant when declaring a variable.

The types of formal and actual parameters should not be the same, but compatible.

There are many other details, but you can read them from the PL / SQL subroutine chapter of the PL / SQL Language Reference . See Section Subroutine Parameters.

I don’t know why formal parameters should be unconditional, but I am very pleased that it removes (unnecessary) data from the subroutine header, making it more abstract.

+3
source share

More articles:


All Articles