Primary key not returned on create_row when XMLTYPE column is present about table-api-generator

[ogobrecht]

Hi Paolo,

confirmed. This is a Bug. We introduced in 0.5.0 the possibility to return the whole row instead of the primary key (parameter p_return_row_instead_of_pk ).

The problem is that returning all table columns into a row type variable is not working when you have a xmltype column in your table. Here two example blocks for your test table - the first one will run:

set serveroutput on size unlimited
declare
  v_row test%rowtype;
begin
  insert into test (
    table_id /*PK*/,
    xml_template
  ) values (
    test_seq.nextval,
    xmltype('<TEST/>')
  ) returning table_id into v_row.table_id;
  dbms_output.put_line('table_id=' || to_char(v_row.table_id));
end;
/

As soon as you include the xmltype column you will get an error (ORA-22816: unsupported feature with RETURNING clause):

set serveroutput on size unlimited
declare
  v_row test%rowtype;
begin
  insert into test (
    table_id /*PK*/,
    xml_template
  ) values (
    test_seq.nextval,
    xmltype('<TEST/>')
  ) returning table_id, xml_template into v_row;
  dbms_output.put_line('table_id=' || to_char(v_row.table_id));
end;
/

This was the reason to decide to do the extra fetch when an xml column is present. This should only be the case when also the parameter p_return_row_instead_of_pk is set - but we forgot this condition in the code.

I will fix this on the weekend and provide a corrected version.

By the way, we are working currently on a new version which will introduce methods for bulk processing. Are you interested in this? If so, you are welcome as an early adopter for tests ;-)

You will hear from me the next days, best regards
Ottmar

[ogobrecht]

Hi Paolo,

the bugfix release 0.5.1 is available. Thank you for reporting the issue.

Best regards
Ottmar

[softinn72]

Hi Ottmar,
thanks a lot for the quick fix and the explanation!

Indeed I'd be very interested in the bulk processing functionality of the new version and I hope to find some time to test the development version.

I also have a new enhancement request, but I'll open a new ticket for it.

Thanks again,
Paolo

[ogobrecht]

Hi Paolo,

some hints for the new version:

• We check in currently every running version in the branch named "development".
• The docs for using the bulk methods needs to be aligned.

There are breaking changes:

To have similar behaviour between the bulk and the standard methods we skipped the checking for changes - that means all updates are passed thru the table instead of checked, if any changes occured to the record to avoid unnecessary changes.

We skipped also the generic change log. This was a problem before when multi column primary keys exist and is now a bigger problem with the bulk methods. If you need this feature we recommend to use the flashback data archive and not a trigger based solution because this is slowing down your overall data model performance. Hopefully you are not used the generic change log in the past -otherwise you have some amount of work to migrate to an other solution - sorry for that.

We introduced support for audit columns. That means the table API can do this for you instead of a trigger based solution. You should hide your data model in an extra schema and give only execute rights to the APIs to avoid manipulation of the audit columns. This is known as the SmartDB od PinkDB paradigm - see also this interesting post by Philip Salvisberg.

Let me know, if you need more informations or if you have any questions.

Best regards
Ottmar