'insert into' in 'execute immediate' clause

Question

Can you check this and tell me why I've got an error, please? How should it look? I have no idea what's wrong here. I need to create a table in a function, and in the same function insert data into this table:

create or replace
function new_tab ( pyt IN varchar2) return number
IS
a number;
b varchar2(20);
begin
a:= ROUND(dbms_random.value(1, 3));
b:='example';

-- This works perfect
execute immediate 'CREATE TABLE create_tmp_table'|| a  ||'(i VARCHAR2(50))';

-- Here`s the problem
execute immediate 'insert into create_tmp_table'||a|| 'values ('|| b ||')'; 

exception
when others then
dbms_output.put_line('ERROR-'||SQLERRM);

return 0;
end;

My result is: ERROR-ORA-00926: missing VALUES keyword. Process exited.

Where is the mistake?

Answer 1

As you are creating a dynamic insert command you have to create it as is. So you are missing the single quotes for the value that is varchar:

execute immediate 
     'insert into create_tmp_table'||a|| ' values ('''|| b ||''');'; 
                                                    ^here     ^and here

And a good suggestion for this type of error is to do some debug. On your case you could create a varchar2 variable and put your insert on it then you use the dbms_output.put_line to this variable. Then you will have your sql command that you can test direct on your database. Something like:

declare
   sqlCommand varchar2(1000);
   --define a and b
begin
   --put some values in a and b
   sqlCommand := 'insert into create_tmp_table'||a|| ' values ('''|| b ||''');';
   dbms_output.put_line(sqlCommand);
end;

Then you will know what is wrong with the dynamic command.