Determine Precision and Scale from a Numeric Column 1

[runmd]

Is it possible to find out the precision and scale of a numeric column? I have a PL\SQL script that determines the max length of each column in a table. The script contains an IF block that reads the max length of columns that are of data type NUMBER, VARCHAR2, CHAR or any other data type. I would like to write the new PL\SQL statements within the ELSIF MyDataType = 'NUMBER' block to handle it. Some of the columns will have a precision and scale but will not have decimal data in it. So, I would like to check for that as well. This is just a small piece of the PL\SQL code.

IF MyDataType = 'VARCHAR2' THEN
      dbms_output.put_line (
      'SELECT ' || '''' || MyCol || '''' || ',' || '''' || MyDataType  || '''' || ',' || ' ' || '''' || '(' || '''' || ',' || 'max(length (' || MyCol || ')) as max' || ', ' || '''' || '), ' || '''' ||  ' FROM SUM070_DEMOGRAPHIC;'  );
    ELSIF MyDataType = 'NUMBER' THEN
      dbms_output.put_line (
      'SELECT ' || '''' || MyCol || '''' || ',' || '''' || MyDataType  || '''' || ',' || ' ' || '''' || '(' || '''' || ',' || 'max(length (' || MyCol || ')) as max' || ', ' || '''' || '), ' || '''' ||  ' FROM SUM070_DEMOGRAPHIC;' );
    ELSIF MyDataType = 'CHAR' THEN
      dbms_output.put_line (
      'SELECT ' || '''' || MyCol || '''' || ',' || '''' || MyDataType  || '''' || ',' || ' ' || '''' || '(' || '''' || ',' || 'max(length (' || MyCol || ')) as max' || ', ' || '''' || '), ' || '''' ||  ' FROM SUM070_DEMOGRAPHIC;' );
    ElSE
      dbms_output.put_line (
      'SELECT ' || '''' || MyCol || '''' || ',' || '''' || MyDataType  || '''' || ', ' || '''' || ',' || '''' ||  ' FROM SUM070_DEMOGRAPHIC WHERE rownum = 1;' );    
    END IF;

 

[Thargy]

SELECT DATA_SCALE, DATA_PRECISION
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = <table name>
AND COLUMN_NAME = <column name>;

will give you precislely what you need. The size of the data in the field is no indicator of its specified scale and precision.

Regards

T

 

[runmd]

Thargy,

This is perfect! I'll post my code after I get it working.

 

[SantaMufasa]

RunMD,

There are a couple of tweaks that you need to do with your code to ensure that it does what you want:[ul][li]Your logic for NUMBER columns does not properly accommodate maximum lengths for values behind the decimal point. For example, if your "max(length(..." is for a NUMBER column which contents are "4278.93", your resulting column definition is "NUMBER(7)" instead of "NUMBER(6,2)". I recommend that for NUMBER columns you simply define them as "NUMBER" and not "NUMBER(x)" or "NUMBER(x,y)". This gives you the flexibility to have any-sized values both to the left, and to the right, of the decimal point, without any loss of data space or precision. You can then rely upon your application to restrict input values boundaries that are within your business rules.

[/li][li]Your code does not properly handle length specifications where the current contents of the column are all NULL. Your resulting code (in such a case) produces "VARCHAR2()", which Oracle does not accept/support.[/li][/ul]It appears that your code is attempting to create column-definition maximums that are no larger than the maximum lengths of your current data. I'm questioning why you are doing this at all. Having column definition maximum sizes that are larger than your "business-rule" maximums, does not "waste" even a single byte of additional space. But making your columns artificially smaller than your business rules allow exposes you to risks of dissatisfied users (when they cannot enter reasonably lengthed data due to artificially small column-size maximums) and avoidable database maintenance (resulting from your need to increase the max sizes of your columns in order to make your users happy again).


So, I suggest you reconsider why you are running the above code...It artificially limits your database without any savings in disk space.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.