Error code ORA-00933 for my stored procedure

Question

I get the following error message in a stored procedure that I created:

ORA-00933: SQL command not properly ended
ORA-06512: at line 11

I have tried Googling it, but could not find anything applicable as it tells my to try to eliminate any 'ORDER BY'.

declare
    cursor a_tab is
       select table_name
         from all_tables
        where owner = 'OFFERINGWORKSPACE'
          and (TABLE_NAME like 'EBA_%' or TABLE_NAME = 'SURVEY_2.0');

    v_tab_name  varchar2(500);
begin
    open a_tab;
    loop
        fetch a_tab into v_tab_name;
        exit when a_tab%notfound;
    
        EXECUTE IMMEDIATE 'delete ' || v_tab_name;
    end Loop;
    close a_tab;
    
    open a_tab;
    Loop 
        fetch a_tab into v_tab_name;
        Exit when a_tab%notfound;
    
        EXECUTE IMMEDIATE 'insert into ' || v_tab_name || '(select * from OFFERINGWORKSPACE.'||v_tab_name ||')';
    End Loop;
    Close a_tab;
End;

Alex Poole Alex Poole · Accepted Answer · 2020-10-07T15:14:48

There is a clue in your cursor query:

... TABLE_NAME = 'SURVEY_2.0');

The period in that breaks the database object naming rules:

Nonquoted identifiers can only contain alphanumeric characters from your database character set and the underscore (_). Database links can contain periods (.) and "at" signs (@).

so that table name must be a quoted identifier. You therefore need to quote it in your statements:

EXECUTE IMMEDIATE 'delete "' || v_tab_name || '"';

and

EXECUTE IMMEDIATE 'insert into "' || v_tab_name
  || '"(select * from OFFERINGWORKSPACE."'||v_tab_name ||"')';

db<>fiddle showing the errors from those commands (simplified to one schema, and static SQL), and how adding the double quotes fixes them.

Error code ORA-00933 for my stored procedure

1 Answers