ORA-01727 numeric precision specifier is out of range (1 to 38) cause and solution
The following is the ORA-01727 error message, you can learn the cause and solution of the Oralce error.
ORA-01727 numeric precision specifier is out of range (1 to 38)
Cause: The precision specified for a number column in a CREATE/ALTER TABLE or CREATE CLUSTER statement must be a digit between 1 and 38. If no precision is specified, a default precision of 22 digits is used.
Solution: Specify numeric precision between 1 and 38 and retry the statement.
Case:
When the column with FLOAT datatype returns table of %rowtype inside a pipelined function, it fails with below error. This error is seen while using WITH clause.
ORA-01727: numeric precision specifier is out of range (1 to 38)
> sqlplus scott/tiger SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 25 12:44:33 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table temp_prj_projects (budget float); Table created. SQL> create or replace package temp_pkg_project_analysis is TYPE tab_project_list IS TABLE OF TEMP_PRJ_PROJECTS%ROWTYPE; FUNCTION f_project_list RETURN tab_project_list PIPELINED; end temp_pkg_project_analysis; / Package created. SQL> create or replace package body temp_pkg_project_analysis is FUNCTION f_project_list RETURN tab_project_list PIPELINED IS BEGIN return; END; end temp_pkg_project_analysis; / Package body created. SQL> with mt_projects as ( select t.budget from table(temp_pkg_project_analysis.f_project_list) t ) select sum(b.budget) from mt_projects p, mt_projects b; / from mt_projects p, mt_projects b * ERROR at line 7: ORA-00604: error occurred at recursive SQL level 1 ORA-01727: numeric precision specifier is out of range (1 to 38)
CAUSE
This is a BUG 28701040 in Oracle, which is still being worked by the development team
SOLUTION
Till the time the fix is available, you can try the below workaround.
Workaround :
a. Convert FLOAT column to NUMBER column
b. use cast function while retrieving it from the function
with mt_projects as ( select cast(t.budget as float) budget from table(temp_pkg_project_analysis.f_project_list) t ) select sum(b.budget) from mt_projects p, mt_projects b; SUM(B.BUDGET) ------------- SQL> c. use record type instead of %rowtype SQL> create or replace package temp_pkg_project_analysis is TYPE rec_temp_prj_projects IS RECORD (BUDGET FLOAT); TYPE tab_project_list IS TABLE OF REC_TEMP_PRJ_PROJECTS; FUNCTION f_project_list RETURN tab_project_list PIPELINED; end temp_pkg_project_analysis; / Package created. SQL> create or replace package body temp_pkg_project_analysis is FUNCTION f_project_list RETURN tab_project_list PIPELINED IS BEGIN return; END; end temp_pkg_project_analysis; / Package body created. SQL> with mt_projects as ( select t.budget from table(temp_pkg_project_analysis.f_project_list) t ) select sum(b.budget) from mt_projects p, mt_projects b; SUM(B.BUDGET) ------------- SQL>