Get max length of VARCHAR2 column in PL/SQL?

I'm writing PL/SQL blocks to pack selected fields from a table row into a buffer, which will be written (via Oracle pipe) to MQSeries. Thus, the fields have to be a predictable length so they can be unpacked correctly.

Given a table that looks like this:

emp_no  varchar2(8),
emp_name_1st varchar2(15),
emp_name_last varchar2(30);

I need to pack the fields with statements like this:

DBMS_PIPE.PACK_MESSAGE(RPAD(NVL(emp_no,' '),8));
DBMS_PIPE.PACK_MESSAGE(RPAD(NVL(emp_name_1st,' '),15));
DBMS_PIPE.PACK_MESSAGE(RPAD(NVL(emp_name_last,' '),30));

It offends me to have to put literals in the code for the field widths. If the fields are resized, then the code breaks. But how can my PL/SQL code get the maximum size of the field?

I've tried LENGTH(), but it doesn't return the maximum if the field is shorter than that. VSIZE looked promising, but it returns NULL if the field is null. I could write a SQL query to go after the metadata in USER_TABLES, but that strikes me as very clumsy. Anybody got a better way?

Cc of follow-ups appreciated.

--

Jeff Boes jboes_at_qtm.net
http://www.qtm.net/~jboes/

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Aug 19 1998 - 14:04:31 CDT