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>