Problem when using identity column about table-api-generator HOT 4 CLOSED

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

from table-api-generator.

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

from table-api-generator.

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

from table-api-generator.

Hi Kim,

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

Best regards
Ottmar

from table-api-generator.