Problem when using identity column

[dwarcake]

At the moment we are using an identity column as primary key in each of our tables.
This identity column is configured as "generated always"

By this, it is not allowed to enter a value for this column.
It's always generated by an interal sequence.

When we use the table-api for such a table, the insert goes wrong since it tries to insert NULL here.
Is there any way this can be fixed ?

[ogobrecht]

Hi Kim,

thank your for your feedback. We are currently working on some new features. So, it is the right time to think about identity columns. It should work out of the box when you are using the option GENERATED ON NULL. This option is also a good choice, when you need to be able to migrate data with existing identity values.

If you need to use GENERATED ALWAYS for whatever reason then you could try to fix this with an workaround until we support it out of the box. It should be fairly easy to to do this because we generate only one create_row function which implements the insert. This function is then used by the the create_row procedure and the create_or_update_row function and procedure - so you need to do the changes only in one place. Here a raw workaround which can be generalized by creating a small wrapper procedure - align it to your needs:

--> Generate API
BEGIN
  om_tapigen.compile_api(p_table_name => 'EMPLOYEES');
END;
/

-- Modify API to own needs

DECLARE
  v_clob        CLOB;
  v_cursor      NUMBER;
  v_exec_result PLS_INTEGER;
  PROCEDURE util_execute_sql(p_sql IN OUT NOCOPY CLOB) IS
    v_cursor      NUMBER;
    v_exec_result PLS_INTEGER;
  BEGIN
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor,
                   p_sql,
                   dbms_sql.native);
    v_exec_result := dbms_sql.execute(v_cursor);
    dbms_sql.close_cursor(v_cursor);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_sql.close_cursor(v_cursor);
      RAISE;
  END util_execute_sql;
BEGIN
  v_clob := rtrim(dbms_metadata.get_ddl('PACKAGE_BODY',
                                        'EMPLOYEES_API'),
                  '/');

  -- If you create a wrapper procedure like this anonymous block of code you can generalize the replacements
  v_clob := REPLACE(REPLACE(v_clob,
                            'INSERT INTO EMPLOYEES ( "EMPLOYEE_ID", ',
                            'INSERT INTO EMPLOYEES ( '),
                    'VALUES ( v_pk, ',
                    'VALUES ( ');
                    
  --dbms_output.put_line(v_clob);
  util_execute_sql(v_clob);

END;
/

Hope this helps, best regards
Ottmar

[dwarcake]

Ottmar,

Thanks for your feedback and the example.
I'll look into this and will get back to you when I get it to work.

Kind regards,
Kim

[dwarcake]

@ogobrecht We used the GENERATED ON NULL option as you suggested.

[ogobrecht]

Hi Kim,

thank you for your feedback. I will close now this issue.

Best regards
Ottmar