ORA-00907 when comparing ref cursors with BINARY_... columns
Describe the bug
If ref cursor has BINARY_DOUBLE or BINARY_FLOAT columns, cursor comparison fails with ORA-00907.
Provide version info
Information about utPLSQL and Database version,
SQL> set serveroutput on SQL> declare 2 l_version varchar2(255); 3 l_compatibility varchar2(255); 4 begin 5 dbms_utility.db_version( l_version, l_compatibility ); 6 dbms_output.put_line( l_version ); 7 dbms_output.put_line( l_compatibility ); 8 end; 9 / 12.2.0.1.0 12.2.0 PL/SQL procedure successfully completed SQL> select substr(ut.version(),1,60) as ut_version from dual; UT_VERSION ------------------------------------------------------------ v3.1.6.2729 SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0 SQL> select * from nls_session_parameters; PARAMETER VALUE ------------------------------ ---------------------------------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected SQL> select substr(dbms_utility.port_string,1,60) as port_string from dual; PORT_STRING ------------------------------------------------------------ x86_64/Linux 2.4.xx
Information about client software
Executed using ut.run('UT_TEST'); from PLSQL Developer.
To Reproduce
begin
ut.run('UT_TEST');
end;
/
test utplsql
Gives success for identical data with floats [.016 sec] (FAILED - 1)
Failures:
1) success_on_same_data_float
ORA-00907: missing right parenthesis
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 318
ORA-06512: at "UT3.UT_COMPOUND_DATA_HELPER", line 596
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 309
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 366
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 "TDS2.UT_TEST", line 17
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 318
ORA-06512: at "UT3.UT_COMPOUND_DATA_HELPER", line 596
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 309
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 366
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 "TDS2.UT_TEST", line 17
ORA-06512: at line 6
Finished in .020824 seconds
1 tests, 0 failed, 1 errored, 0 disabled, 0 warning(s)
Expected behavior
Test should succeed.
Example code
Content of UT_TEST.pks:
create or replace package ut_test is
--%suite(test for utplsql float)
--%test(Gives success for identical data with floats)
procedure success_on_same_data_float;
end ut_test;
Content of UT_TEST.pkb:
create or replace package body ut_test is
procedure success_on_same_data_float
as
l_expected sys_refcursor;
l_actual sys_refcursor;
begin
open l_expected for
select cast(3.14 as binary_double) as pi_double,
cast(3.14 as binary_float) as pi_float
from dual;
open l_actual for
select cast(3.14 as binary_double) as pi_double,
cast(3.14 as binary_float) as pi_float
from dual;
--Act
ut3.ut.expect( l_actual ).to_equal( l_expected );
end;
end ut_test;