This can be a really exciting addition to utPLSQL. I will be happy to spend time implementing it,

Hello @PhilippSalvisberg /@jgebal,

I am a bit new to Git and having some experience in using PLSQL. I would be happy to contribute in this new feature. It would be helpful if you can guide me or assign me a task so that i can work on it.

Also, if you need any help in any of the other issues also please let me know. I would really appreciate your insights and guidance here.

Thanks and Regards,
Amith

I am a bit new to Git and having some experience in using PLSQL. I would be happy to contribute in this new feature. It would be helpful if you can guide me or assign me a task so that i can work on it.

Also, if you need any help in any of the other issues also please let me know. I would really appreciate your insights and guidance here.

@amithdas99 Thanks for your interest in contributing to the utPLSQL project. Please join the Slack channel to discuss non-issue-related stuff. See https://github.com/utPLSQL/utPLSQL/blob/develop/readme.md#contributing-to-the-project regarding details on how to contribute and how to join the slack channel. Thanks.

@PhilippSalvisberg
I looked at the documentation for the new datatypes.
It seems that the are all SQL-only datatypes. That means, we cannot use them directly in an expectation syntax inside PL/SQL block. Please correct me if I'm wrong as I didn't try it on 23ai yet.

I assume, that the requirement would be to assure that utPLSQL can support those datatypes inside a cursor comparison.

@amithdas99
You are welcome to start contributing to the project.
I am not sure if this specific request will be a good starting point to be honest as it will be touching on one of most complicated elements of the whole utPLSQL - cursor comparison.

Maybe some other, smaller issues, would be a good starting point.

Thank you both @jgebal and @PhilippSalvisberg !!

Hello @PhilippSalvisberg /@jgebal,

I am a bit new to Git and having some experience in using PLSQL. I would be happy to contribute in this new feature. It would be helpful if you can guide me or assign me a task so that i can work on it.

Also, if you need any help in any of the other issues also please let me know. I would really appreciate your insights and guidance here.

Thanks and Regards, Amith

I would recommend starting with #1161

@PhilippSalvisberg I looked at the documentation for the new datatypes. It seems that the are all SQL-only datatypes. That means, we cannot use them directly in an expectation syntax inside PL/SQL block. Please correct me if I'm wrong as I didn't try it on 23ai yet.

I assume, that the requirement would be to assure that utPLSQL can support those datatypes inside a cursor comparison.

I'm not sure I understand your question. However, maybe the following experiment I ran on an Oracle Database 23ai version 23.5 may help understand how the new data types can be used in SQL and PL/SQL.

drop table if exists t;
create table t (
   c1 boolean,
   c2 json,
   c3 vector
);

insert into t (c1, c2, c3) 
values(true, json('[{"name":"test", "value":"hello"}]'), vector('[0,2,2,0,0]'));

set serveroutput on size unlimited
declare
   l_c1 boolean;
   l_c2 json;
   l_c3 vector;
   l_row t%rowtype;
begin
   for r in (select c1, c2, c3 from t) loop
      dbms_output.put_line('explicitly defined types in PL/SQL');
      l_c1 := r.c1; -- converts SQL boolean to PL/SQL boolean
      l_c2 := r.c2;
      l_c3 := r.c3;
      dbms_output.put_line('l_c1: ' || case when l_c1 then 1 else 0 end);
      dbms_output.put_line('l_c2: ' || json_serialize(l_c2));
      dbms_output.put_line('l_c3: ' || vector_serialize(l_c3));
      dbms_output.put_line(null);
      dbms_output.put_line('implicitly defined types via %rowtype');
      l_row := r;
      dbms_output.put_line('l_row.c1: ' || case when l_row.c1 then 1 else 0 end);
      dbms_output.put_line('l_row.c2: ' || json_serialize(l_row.c2));
      dbms_output.put_line('l_row.c3: ' || vector_serialize(l_row.c3));
   end loop;
end;
/

and the output is:

Table T dropped.


Table T created.


1 row inserted.

explicitly defined types in PL/SQL
l_c1: 1
l_c2: [{"name":"test","value":"hello"}]
l_c3: [0,2.0E+000,2.0E+000,0,0]

implicitly defined types via %rowtype
l_row.c1: 1
l_row.c2: [{"name":"test","value":"hello"}]
l_row.c3: [0,2.0E+000,2.0E+000,0,0]


PL/SQL procedure successfully completed.

Thank you @PhilippSalvisberg
That example clarifies a lot.

So it seems like there will be even more work.

PL/SQL BOOLEAN datatype is already supported by utPLSQL.
I see the following tasks that should be done:

• add support for comparison of PL/SQL datatypes
  • JSON
  • VECTOR
• verify/test and if needed add support for Cursor comparison with
  • BOOLEAN columns
  • VECTOR columns
  • JSON columns

Additionally I would throw in XMLTYPE to the mix as it son not natively supported by utPLSQL.

• XMLTYPE in PLSQL
• XMLTYPE column in SQL

verify/test and if needed add support for Cursor comparison with

• BOOLEAN columns
• VECTOR columns
• JSON columns

After closing this issue, I expect the following code to work without throwing an exception.

drop table if exists t;
create table t (
   c1 boolean,
   c2 json,
   c3 vector
);

insert into t (c1, c2, c3)
values(true, json('[{"name":"test", "value":"hello"}]'), vector('[0,2,2,0,0]'));

set serveroutput on size unlimited
<<does_not_work_in_utplsql_3_1_14>>
declare
   c_actual   sys_refcursor;
   c_expected sys_refcursor;
begin
   open c_actual for select * from t;
   open c_expected for select * from (values (true, json('[{"name":"test", "value":"hello"}]'), vector('[0,2,2,0,0]'))) as t (c1, c2, c3);
   ut.expect(c_actual).to_equal(c_expected);
end does_not_work_in_utplsql_3_1_14;
/

<<workaround_with_utplsql_3_1_14>>
declare
   c_actual   sys_refcursor;
   c_expected sys_refcursor;
begin
   open c_actual for select case when c1 then 1 else 0 end as c1, json_serialize(c2) as c2, vector_serialize(c3) as c3 from t;
   open c_expected for select * from (values (1, '[{"name":"test","value":"hello"}]', '[0,2.0E+000,2.0E+000,0,0]')) as t (c1, c2, c3);
   ut.expect(c_actual).to_equal(c_expected);
end workaround_with_utplsql_3_1_14;
/

The output with 3.1.14 looks like this:

Table T dropped.


Table T created.


1 row inserted.


Error starting at line : 12 File @expectations.sql
In command -
<<does_not_work_in_utplsql_3_1_14>>
declare
   c_actual   sys_refcursor;
   c_expected sys_refcursor;
begin
   open c_actual for select * from t;
   open c_expected for select * from (values (true, json('[{"name":"test", "value":"hello"}]'), vector('[0,2,2,0,0]'))) as t (c1, c2, c3);
   ut.expect(c_actual).to_equal(c_expected);
end does_not_work_in_utplsql_3_1_14;
Error report -
ORA-01403: no data found
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 114
ORA-06512: at "UT3.UT_COMPOUND_DATA_HELPER", line 624
ORA-06512: at "UT3.UT_CURSOR_DETAILS", line 115
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 95
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 22
ORA-06512: at "UT3.UT", line 83
ORA-06512: at line 9
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

More Details :
https://docs.oracle.com/error-help/db/ora-01403/
https://docs.oracle.com/error-help/db/ora-06512/
SUCCESS
  Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]


PL/SQL procedure successfully completed.