I need to read and write data files that are processed by COBOL programs. Reading (loading) is easy; I created an Oracle loader-type external table using field definitions like "field1 POSITION(nnn) ZONED(7,2)". But I also need write zoned-formatted fields from NUMBER columns of various precision and scale. I wrote a function to format the data, but it needs to know the column value, precision, scale and signed/unsigned indicator (the last three hard-coded parameters in the function call). The view has dozens of columns needing this kind of formatting, and I can't see myself wanting to hard-code like this. Looking it up in USER_TAB_COLS seems like a ton of overhead. I can't help but wonder if there's a better way to do this that I just haven't figured out. I'm not asking for a solution, just a pointer to some Oracle feature I may not have considered or know about.
If you have a SQL query that you will be using to write/unload the data, then you might get some value from using DBMS_SQL.DESCRIBE_COLUMNS
Take a look at this link here for an example of how to use it in code:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059 but the real value for you I think will be the data structure created once you have parsed your query. After you call DBMS_SQL.DESCRIBE_COLUMNS you will have access to array, which for each column your query, you get a row in the array with the following information:
TYPE desc_rec IS RECORD (
col_type BINARY_INTEGER := 0,
col_max_len BINARY_INTEGER := 0,
col_name VARCHAR2(32) := '',
col_name_len BINARY_INTEGER := 0,
col_schema_name VARCHAR2(32) := '',
col_schema_name_len BINARY_INTEGER := 0,
col_precision BINARY_INTEGER := 0,
col_scale BINARY_INTEGER := 0,
col_charsetid BINARY_INTEGER := 0,
col_charsetform BINARY_INTEGER := 0,
col_null_ok BOOLEAN := TRUE);
Hope this helps.