Reputation: 3
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;
Upvotes: 0
Views: 118
Reputation: 191570
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.
Upvotes: 2