have type.Numeric not assume any default precision/scale

[Former user]

When an Oracle NUMBER (without precision and scale arguments) is reflected, it ends up as an {{{OracleNumeric(precision=10, scale=2)}}}, which is way less than what was allowed by the Database. There don't seem to be default values for precision and scale in Oracle itself: a NUMBER always has a precision of 38 and variable scale. (It can hold all kinds of numbers without losing precision: 123, 123.456, 1.23456789, 123456789012345 and so on, up to 38 digits)

Also, the other way round, there is no way of creating such a column type through SQLAlchemy since {{{OracleNumeric()}}} always ends up being {{{OracleNumeric(precision=10, scale=2, asdecimal=True, length=None)}}}.

[Mike Bayer]

I haven't had time to test the first issue to confirm this behavior - the values should be received but we don't seem to have a unit test for that. If it is a bug, keep in mind that SQLA has no use for precision and scale other than issuing CREATE TABLE, which is moot here since you are reflecting an existing schema. I would not in any case recommend SQLA's Table construct to be used to "copy" an existing schema - you should use the tools that the database provides to accomplish this since SQLA will never support the full range of schema details supported by the database.

I also cannot reproduce your second issue - both Numeric(anynumber, anyprec) and OracleNumeric(anynumber, anyprec) faithfully preserve the given values when issuing DDL. You also didn't specify what version of SQLAlchemy. Can you please post a fully working test case and further detail ?

[Former user]

Replying to zzzeek:

I haven't had time to test the first issue to confirm this behavior - the values should be received but we don't seem to have a unit test for that. If it is a bug, keep in mind that SQLA has no use for precision and scale other than issuing CREATE TABLE, which is moot here since you are reflecting an existing schema. I would not in any case recommend SQLA's Table construct to be used to "copy" an existing schema - you should use the tools that the database provides to accomplish this since SQLA will never support the full range of schema details supported by the database.

I also cannot reproduce your second issue - both Numeric(anynumber, anyprec) and OracleNumeric(anynumber, anyprec) faithfully preserve the given values when issuing DDL.

That's right! If you do specify precision and scale in your SQLA-Column everything is fine. If you reflect a column created with NUMBER(15,5) in Oracle it will be an {{{OracleNumeric(15,5)}}} in SQLAlchemy (0.5.* btw).

The problem was: if you don't specify a precision/scale in Oracle it will end up as a {{{OracleNumeric(10, 2)}}} in SQLA.

For example:

CREATE TABLE test ( col1 NUMBER );

reflected looks like

Table('test', <meta>, Column(u'col1', OracleNumeric(precision=10, scale=2, ...) ...)

.

On the other Hand:

Table('test', meta, Column('col1', OracleNumeric())).create()

ends up as

CREATE TABLE test (col1 NUMBER(10,2))

while it should rather be

CREATE TABLE test (col1 NUMBER)

.

Of course, all this isn't too much of a problem unless I try to actually create tables from my SQLA metadata, because, as you said, SQLA doesn't care that much about the precision/scale arguments.

[Mike Bayer]

oh ok, you'd like types.Numeric to output NUMBER if no precision/scale is provided. good point. Its something we can look into in 0.6 where we are revisiting the types system.

[Mike Bayer]

8fc5005dfe3eb66a46470ad8a8c7b95fc4d6bdca

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)