Thorsten Kettner 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.
>
> Thanks in advance,
> Thorsten.
As Laurenz says ... query the data dictionary. Though you could also
save a lot of trouble by creating the column as a CLOB and never caring
again.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Sep 14 2007 - 10:36:35 CDT
