By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
a NUMBER,
b NUMBER(*),
c NUMBER(*,127),
d NUMBER(38,127)
);
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_cols
WHERE table_name = 'TABLE_NAME';
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_PRECISION | DATA_SCALE |
|---|---|---|---|---|
| A | NUMBER | 22 | null | null |
| B | NUMBER | 22 | null | null |
| C | NUMBER | 22 | null | 127 |
| D | NUMBER | 22 | 38 | 127 |
BEGIN
DBMS_METADATA.set_transform_param(
DBMS_METADATA.session_transform,
'SQLTERMINATOR',
TRUE
);
DBMS_METADATA.set_transform_param(
DBMS_METADATA.session_transform,
'PRETTY',
TRUE
);
DBMS_METADATA.set_transform_param(
DBMS_METADATA.session_transform,
'SEGMENT_ATTRIBUTES',
FALSE
);
DBMS_METADATA.set_transform_param(
DBMS_METADATA.session_transform,
'STORAGE',
FALSE
);
DBMS_METADATA.set_transform_param(
DBMS_METADATA.session_transform,
'CONSTRAINTS',
FALSE
);
END;
/
1 rows affected
SELECT dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', USER)
FROM DUAL;
| DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME',USER) |
|---|
CREATE TABLE "FIDDLE_IWFWCRVROFTNTBHDDPAM"."TABLE_NAME" ( "A" NUMBER, "B" NUMBER, "C" NUMBER(*,127), "D" NUMBER(38,127) ) ; |
INSERT INTO table_name (a, b, c, d) VALUES (1e-130, 1e-130, 1e-130, 1e-130);
1 rows affected
INSERT INTO table_name (a, b, c, d) VALUES (1e-127, 1e-127, 1e-127, 1e-127);
1 rows affected
INSERT INTO table_name (a, b, c, d) VALUES (1e125, 1e125, 1e125, 1e-90);
1 rows affected
INSERT INTO table_name (a) VALUES (1e126);
ORA-01426: numeric overflow
INSERT INTO table_name (b) VALUES (1e126);
ORA-01426: numeric overflow
INSERT INTO table_name (c) VALUES (1e126);
ORA-01426: numeric overflow
INSERT INTO table_name (d) VALUES (1e-89);
ORA-01438: value larger than specified precision allowed for this column
INSERT INTO table_name (d) VALUES (-0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999);
1 rows affected
INSERT INTO table_name (d) VALUES (+0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999);
1 rows affected
SELECT TO_CHAR(a, '0.0EEEE') AS a,
TO_CHAR(b, '0.0EEEE') AS b,
TO_CHAR(c, '0.0EEEE') AS c,
TO_CHAR(d, '0.0000000000000000000000000000000000000EEEE') AS d
FROM table_name;
| A | B | C | D |
|---|---|---|---|
| 1.0E-130 | 1.0E-130 | 0.0E+00 | 0.0000000000000000000000000000000000000E+00 |
| 1.0E-127 | 1.0E-127 | 1.0E-127 | 1.0000000000000000000000000000000000000E-127 |
| 1.0E+125 | 1.0E+125 | 1.0E+125 | 1.0000000000000000000000000000000000000E-90 |
| null | null | null | -9.9999999999999999999999999999999999999E-90 |
| null | null | null | 9.9999999999999999999999999999999999999E-90 |