CODEWITHSUNDEEP
sqloracle-database
Patrick Szalapski
Patrick Szalapski

Reputation: 9439

How do I specify a literal number with a limited precision in an Oracle Database query?

How do I specify a literal number with a limited precision in the SELECT clause of an Oracle PL/SQL query? Is CAST the only way?

I want to do this, but the column's type ends up as NUMBER, whereas I want the type to match l.changed_rows_log_id, which is a NUMBER(18,0).

SELECT COALESCE(l.changed_rows_log_id, 1) AS Col1 FROM mytable l;

If I leave it at that, consuming processes may balk at type NUMBER since it might contain very large numbers--all because of a literal 1.

Instead I have to do this, awkwardly. Is there a better way?

SELECT CAST(COALESCE(l.changed_rows_log_id, 1) AS NUMBER(18,0)) AS Col1 FROM mytable l;

Upvotes: 2

Views: 145

Answers (1)

Patrick Szalapski
Patrick Szalapski

Reputation: 9439

There seems to be no other way than using CAST as in the question. Seems like a real shortcoming of PL/SQL syntax.

Upvotes: 0

Related Questions