oracle returning ID in execute immediate giving "missing keyword" error

Question

Following is an oracle procedure

create or replace 
PROCEDURE INSERT_COMMON(
ENTITY_NAME IN VARCHAR2  
, INSERT_QUERY IN varchar2
) 
AS
NEW_ID NUMBER;
BEGIN
    -- execute insert
    DBMS_OUTPUT.PUT_LINE('INSERT_QUERY: ' || INSERT_QUERY);
    -- execute IMMEDIATE INSERT_QUERY returning ID into NEW_ID;
    -- above gives me a syntax error so using as below
    execute IMMEDIATE INSERT_QUERY || ' returning ID into NEW_ID';
    DBMS_OUTPUT.PUT_LINE('NEW_ID: ' || NEW_ID);

END INSERT_COMMON;

and Pl/SQL I am using

DECLARE
  ENTITY_NAME VARCHAR2(200);
  INSERT_QUERY VARCHAR2(200);
BEGIN
  ENTITY_NAME := 'company';
  INSERT_QUERY := 'INSERT INTO COMPANY (NAME) VALUES (''A  Company 2'')';

  INSERT_COMMON(ENTITY_NAME,INSERT_QUERY);
END;

This gives me following error

Error report:
ORA-00905: missing keyword
ORA-06512: at "SYSTEM.INSERT_COMMON", line 20
ORA-06512: at line 8
00905. 00000 -  "missing keyword"

However, I have tested and following works fine

DECLARE
  NEW_ID NUMBER;
BEGIN

  INSERT INTO COMPANY (NAME) VALUES ('A  Company 2') returning ID into NEW_ID;

  DBMS_OUTPUT.PUT_LINE('NEW_ID: ' || NEW_ID);

END;

Answer 1

You need to specify a bind variable you're returning into in the dynamic sql statement, but you also then need to add the returning into <variable> clause to the execute immediate statement.

I believe (untested, since you didn't provide the statements to set up your table and associated triggers) that the following should sort your issue:

create or replace procedure insert_common (entity_name in varchar2,
                                           insert_query in varchar2)
as
  new_id number;
begin
  -- execute insert
  dbms_output.put_line ('INSERT_QUERY: ' || insert_query);

  -- above gives me a syntax error so using as below
  execute immediate insert_query || ' returning ID into :NEW_ID' returning into new_id;

  dbms_output.put_line ('NEW_ID: ' || new_id);
end insert_common;
/