Skip to content

Number precision when selecting from dual #907

Closed
@lwasylow

Description

@lwasylow

When running a tests like this

create or replace PACKAGE ut_sample_test IS

   --%suite(Sample Test Suite)
   --%tags(suite1)

   --%test(Compare Ref Cursors)
   --%tags(test1)
   PROCEDURE ut_refcursors1;

END ut_sample_test;
/

create or replace PACKAGE BODY ut_sample_test IS

   PROCEDURE ut_refcursors1 IS
      v_actual   SYS_REFCURSOR;
      v_expected SYS_REFCURSOR;
   BEGIN
    open v_expected for select 12345 as test from dual;
    open v_actual   for select 12345 as test from dual;

      ut.expect(v_actual).to_equal(v_expected);
   END;

END ut_sample_test;
/

it fails with
ORA-01438: value larger than specified precision allowed for this column ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 332 ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 380 ORA-06512: at "UT3.UT_EQUAL", line 225 ORA-06512: at "UT3.UT_EXPECTATION", line 26 ORA-06512: at "UT3.UT_EXPECTATION", line 138 ORA-06512: at "UT3.UT_SAMPLE_TEST", line 10

This is due to ut_compound_helper.generate_xmltab_stmt and dbms_sql.describe_columns3 for some reason it set max column len to 4 , precision to 0 and scale to -127 when selecting from dual.

Solution is if the type is NUMBER then use a precision and scale instead of max_col_len.

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions