Re: (contd) %type behaviour differs for variable declarations and function parameters

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 02 Nov 1998 23:06:56 GMT
Message-ID: <363e3416.18732587@news.siol.net>

On Mon, 2 Nov 1998 16:26:10 +0100, "Bram Stieperaere" <bse_at_NO_SPAMsodexho-pass.be> wrote:

>now suppose I would like to trunc the parameter to 10 char, without
>hardcoding it's precision. using sys.all_columns seems a bit expensive
>solution to me.

You could use DBMS_DESCRIBE.DESCRIBE_PROCEDURE packaged procedure to get the length constraint of your input parameter declared as %TYPE. The procedure's output parameters LENGTH, PRECISION and SCALE reflect the corresponding parameter constraints if the parameters are declared using TABLE.COLUMN%TYPE notation. Note that all output parameters of this procedure are of type PL/SQL TABLE, so you must actualy get the output values from the pl/sql tables.

Here is a simple example:

SQL> CREATE OR REPLACE PROCEDURE
  2 test_proc (p_ename scott.emp.ename%TYPE,   3 p_job scott.emp.job%TYPE)   4 IS

  5    v_overload DBMS_DESCRIBE.NUMBER_TABLE;
  6    v_position DBMS_DESCRIBE.NUMBER_TABLE;
  7    v_level    DBMS_DESCRIBE.NUMBER_TABLE;
  8    v_arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
  9    v_datatype DBMS_DESCRIBE.NUMBER_TABLE;
 10    v_default  DBMS_DESCRIBE.NUMBER_TABLE;
 11    v_inout    DBMS_DESCRIBE.NUMBER_TABLE;
 12    v_length   DBMS_DESCRIBE.NUMBER_TABLE;
 13    v_precision DBMS_DESCRIBE.NUMBER_TABLE;
 14    v_scale    DBMS_DESCRIBE.NUMBER_TABLE;
 15    v_radix    DBMS_DESCRIBE.NUMBER_TABLE;
 16    v_spare    DBMS_DESCRIBE.NUMBER_TABLE;

 17 BEGIN
 18 DBMS_DESCRIBE.DESCRIBE_PROCEDURE('TEST_PROC',NULL,NULL,

 19      v_overload, v_position, v_level, v_arg_name, v_datatype,
 20      v_default, v_inout, v_length, v_precision, v_scale,
 21      v_radix, v_spare);

 22 DBMS_OUTPUT.PUT_LINE('The length constraint of the imput parameter '

 23                          || v_arg_name(1) ||' is '
 24                          || TO_CHAR(v_length(1)));

 25 DBMS_OUTPUT.PUT_LINE('The length constraint of the imput parameter '

 26                          || v_arg_name(2) ||' is '
 27                          || TO_CHAR(v_length(2)));

 28 END;
 29 /

Procedure created.

SQL> EXEC test_proc('whatever blahblah','whichever blahblah'); The length constraint of the imput parameter P_ENAME is 10 The length constraint of the imput parameter P_JOB is 9

PL/SQL procedure successfully completed.

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Nov 02 1998 - 17:06:56 CST