Re: PL/SQL: Get max length of a varchar2 type

Thorsten Kettner <thorsten.kettner_at_web.de> wrote:
> Below are the first lines of a procedure that shall write a log entry
> into a log table. It gets a string of variable length and than cuts it
> so it fits into the column. My problem: How to know the maximum size
> of the column? With %TYPE I get a variable of the corresponding type,
> but how to get its length?
>
> PROCEDURE write_log(p_msg VARCHAR2) IS
> v_msg log_table.msg%TYPE;
> v_msg_maxlen og_table.msg%TYPE_LENGTH; -- does not exist, but
> maybe there is something alike?
> BEGIN
> v_msg := SUBSTR(p_msg, 1, v_msg_maxlen);
> ...
>
> Simply assigning p_msg to v_msg would raise an overflow error, so I
> must know the max size for v_msg somehow and use substr as sketched in
> above code snippet. How can this problem be solved easily? I know I
> could write a query on all_tab_columns(?) and retrieve information on
> a database column, but this seems very much overhead, knowing that the
> information I seek is already inside my v_msg variable. I just don't
> know how to get it.

The only way I can think of is ask the data dictionary:

SELECT CHAR_LENGTH INTO v_msg_maxlen
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='LOG_TABLE' AND COLUMN_NAME='MSG'; Yours,
Laurenz Albe Received on Fri Sep 14 2007 - 07:59:15 CDT