Primary key not returned on create_row when XMLTYPE column is present #29
Description
Hi Ottmar,
it seems I found a bug in the generated API package where in a very specific case the create_row API function is not returning the PK after successfully creating the record. The issue is present in version 0.5.0, but it worked fine in previous versions. Our database is version 12c (12.2.0.1.0).
Here is a quick test case:
CREATE TABLE test
(
table_id NUMBER NOT NULL CONSTRAINT table_id_pk PRIMARY KEY
, xml_template XMLTYPE
);
CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1;
BEGIN
om_tapigen.compile_api(p_table_name => 'TEST', p_sequence_name => 'TEST_SEQ');
END;
/
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_table_id TEST.table_id%TYPE;
BEGIN
l_table_id := test_api.create_row(p_xml_template => XMLTYPE('<TEST/>'));
-- verify that the function did not return the PK
dbms_output.put_line('l_table_id='||TO_CHAR(l_table_id));
END;
/
-- verify that the record was created successfully
SELECT * FROM test;By looking at the generated code, it's easy to spot why the PK will not be correctly returned, since the sequence value is not considered at all:
FUNCTION create_row (
p_table_id IN "TEST"."TABLE_ID"%TYPE DEFAULT NULL /*PK*/,
p_xml_template IN "TEST"."XML_TEMPLATE"%TYPE )
RETURN "TEST"."TABLE_ID"%TYPE IS
v_return "TEST"."TABLE_ID"%TYPE;
BEGIN
INSERT INTO "TEST" (
"TABLE_ID" /*PK*/,
"XML_TEMPLATE" )
VALUES (
COALESCE( p_table_id, "TEST_SEQ".nextval ),
p_xml_template );
-- returning clause does not support XMLTYPE,so we do here an extra fetch
v_return := read_row ( p_table_id => p_table_id )."TABLE_ID";
RETURN v_return;
END create_row;Additionally I wonder why the returning clause is not used in this case, as the following amended code works fine in database 12c:
INSERT INTO "TEST" (
"TABLE_ID" /*PK*/,
"XML_TEMPLATE" )
VALUES (
COALESCE( p_table_id, "TEST_SEQ".nextval ),
p_xml_template )
RETURN "TABLE_ID"
INTO v_return;