CODEWITHSUNDEEP
sqloracle-databaseplsqltype-conversion
ruifgmonteiro
ruifgmonteiro

Reputation: 709

PL/SQL instruction to get numeric column precision

My goal is to create a function that converts a NUMBER variable to a VARCHAR2 while replacing commas for dots as decimal separator. Besides, the string is returned with a mask which depends on the integer and decimal sizes of the number passed as I_qty_value.

declare

L_result  VARCHAR2(20);          

FUNCTION CONVERT_QTY_FORMAT(I_qty_value IN NUMBER,
                            I_precision IN NUMBER,
                            I_scale     IN NUMBER)

RETURN VARCHAR2 IS
  --
  L_conv_value       VARCHAR2(255);
  L_mask             VARCHAR2(50);
  L_integer_size     NUMBER := I_precision - I_scale;
  L_decimal_size     NUMBER := I_scale;   
  --
BEGIN
  --
  -- Apply mask only if price is a decimal.
  --
  IF round(I_qty_value) = I_qty_value THEN
    --
    L_conv_value := TRIM(TO_CHAR(I_qty_value));
    --
  ELSE
    --
    -- Mask constructor based on value's length and precision.
    --
    L_mask := LTRIM(LPAD('0', L_integer_size , 9)) || 'D' || LTRIM(LPAD('0', L_decimal_size, 0));
    --
    -- Convert number to string using previous mask. 
    --
    L_conv_value := TRIM(REPLACE(TO_CHAR(I_qty_value, L_mask),',','.')); 
    --
  END IF;
  --
  RETURN L_conv_value;
  --
END CONVERT_QTY_FORMAT;  

begin

L_result := CONVERT_QTY_FORMAT(1000.999, 6, 2);
dbms_output.put_line(L_result);  

end;

Although the function is already working, the two input parameters (I_precision and I_scale) are being manually passed. I would like to create an additional function which would return the variable number precision and scale based on the input variable datatype. Is there any PL/SQL instruction or maybe a core table that may help me doing this?

For example, let's suppose tbl1_1.column_1 is a NUMBER(8,3).

What's the best way to get both 8 and 3 values from column_1?

Thanks in advance!

Upvotes: 0

Views: 706

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You can find this information in user_tab_cols and all_tab_cols views:

create table tbl1_1(column_1 number(8,3));

select column_name, data_precision, data_scale 
  from user_tab_cols 
  where table_name = 'TBL1_1' and column_name = 'COLUMN_1';

COLUMN_NAME                    DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
COLUMN_1                                    8          3

Upvotes: 2

Related Questions