CODEWITHSUNDEEP
oracle-database
bullfighter
bullfighter

Reputation: 427

Execute immediate in stored procedure: command not properly ended

I got this procedure:

create or replace procedure TEST_PROCEDURE(
                                time_in      in varchar2,
                                repository_in    in varchar2,
                                iteration_in     in varchar2,
                                tar_table_in     in varchar2
                            )


is


    delete_stmt varchar2(2000);
    insert_stmt varchar2(2000);

begin

     delete_stmt := 'delete from ' || tar_table_in || ' where time=' || time_in ||' and repository=' || repository_in || '
     and iteration=' || iteration_in || '; commit;';

     execute immediate delete_stmt;



    insert_stmt := 'insert into ' || tar_table_in || ' (some columns)

    SELECT ' || repository_in || ' as repository, ' || iteration_in || ' as iteration, t1.* FROM dual
    left join
    json_table((select json_response from TEST_TABLE where repository=repository_in), ''$[*]''
                 COLUMNS
                                      time                   varchar2(64) PATH ''$.time'',
                                      session_id                 varchar2(256) PATH ''$.session_id''
                   ) t1
                   on 1=1';

    execute immediate insert_stmt;

end;

It currently throws an ORA-00933 error at the "execute immediate delete_stmt;" line, which hints to something foul in the query string.

I can't seem to find the location of either a missing quote, or semi-colon that would end the command. Anyone able to spot what I'm missing?

Upvotes: 0

Views: 369

Answers (2)

EdStevens
EdStevens

Reputation: 3872

Whenever trying to debug dynamic sql, assign the statement to a variable (which you do already) then use dbms_output to show the exact statement that will be executed:

SQL> create or replace procedure my_proc (time_in      in varchar2,
  2                                  repository_in    in varchar2,
  3                                  iteration_in     in varchar2,
  4                                  tar_table_in     in varchar2)
  5  as
  6  v_sql varchar2(1000);
  7  begin
  8          v_sql:='delete from ' ||
  9          tar_table_in ||
 10          ' where time=' ||
 11          time_in ||
 12          ' and repository=' ||
 13          repository_in || '
 14          and iteration=' ||
 15          iteration_in ||
 16          '; commit;';
 17
 18      dbms_output.put_line('====== begin debug line ======');
 19      dbms_output.put_line(v_sql);
 20      dbms_output.put_line('====== end  debug line ======');
 21  end;
 22  /

Procedure created.

SQL> show errors
No errors.
SQL> set serverout on
SQL> exec my_proc('aaaa','bbbb','cccc','dddd');
====== begin debug line ======
delete from dddd where time=aaaa and repository=bbbb
        and iteration=cccc;
commit;
====== end  debug line ======

PL/SQL procedure successfully completed.

SQL> --
SQL> drop procedure my_proc;

Procedure dropped.

Upvotes: 1

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

  • If you are using SQL Dynamic, then your values in your dynamic query are missing quotes.
  • Don't use commit inside execute immediate.
  • You do the delete, then the insert, then you commit the transaction.

It should be

create or replace procedure TEST_PROCEDURE(
                                time_in      in varchar2,
                                repository_in    in varchar2,
                                iteration_in     in varchar2,
                                tar_table_in     in varchar2
                            )
is
    delete_stmt varchar2(2000);
    insert_stmt varchar2(2000);
begin
     delete_stmt := 'delete from ' || tar_table_in || ' where time= ''' || time_in || ''' and repository= ''' || repository_in || '''
     and iteration = ''' || iteration_in || ''' '; 
     
     execute immediate delete_stmt;
    
     insert_stmt := 'insert into ' || tar_table_in || ' (some columns)
     SELECT ''' || repository_in || ''' as repository, ''' || iteration_in || ''' as iteration, t1.* FROM dual
     left join
     json_table((select json_response from TEST_TABLE where repository=repository_in), ''$[*]''
                 COLUMNS
                                      time                   varchar2(64) PATH ''$.time'',
                                      session_id                 varchar2(256) PATH ''$.session_id''
                   ) t1
                   on 1=1';

    execute immediate insert_stmt;
    commit;
end;

Upvotes: 1

Related Questions