When creating a table dynamically using Plsql, the user needs to have the Create any table permission
For example:
Create or replace procedure Create_table_test istmpstr varchar2 ( -); V_cursor number;j Number;begin forIinch Ten.. -Loop begin TMPSTR:='CREATE TABLE Dpc_test'|| I | |'As select * from Dpc_test WHERE 1=2'; Dbms_output.put_line (TMPSTR); --v_cursor: =Dbms_sql.open_cursor; --Dbms_sql.parse (v_cursor,tmpstr,dbms_sql.native); --j:=Dbms_sql.execute (v_cursor); Execute immediate tmpstr; Exception when others then Dbms_output.put_line ('Error Create table!'); Dbms_output.put_line ('Sqlcode:'||Sqlcode); Dbms_output.put_line ('SQLERRM:'||SQLERRM); End End Loop;end Create_table_test;
When you execute EXEC create_table_test on the command line, you encounter an insufficient permissions error!
The grant create any table to user needs to be executed after logging in with the SYS user.
"Insufficient permissions" issue in call to execute immediate in stored procedures