Creating Table dynamically in Oracle 9i
-
SQL> create or replace procedure create_tablename(name varchar2) is 2 3 str varchar2(20) := name; 4 v_str varchar2(255); 5 Begin 6 v_str := 'Create table '||'AFR'||'_'||str||'(rollno NUMBER(2),name varchar2(10))'; 7 Execute immediate v_str; 8 End; 9 / Procedure created. SQL> variable abc varchar2(20) SQL> exec create_tablename(:abc); BEGIN create_tablename(:abc); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.CREATE_TABLENAME", line 7 ORA-06512: at line 1 hey can any1 suggest what exactly the problem is? The above code is for creating the table dynamically in Oracle 9i....
-
SQL> create or replace procedure create_tablename(name varchar2) is 2 3 str varchar2(20) := name; 4 v_str varchar2(255); 5 Begin 6 v_str := 'Create table '||'AFR'||'_'||str||'(rollno NUMBER(2),name varchar2(10))'; 7 Execute immediate v_str; 8 End; 9 / Procedure created. SQL> variable abc varchar2(20) SQL> exec create_tablename(:abc); BEGIN create_tablename(:abc); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.CREATE_TABLENAME", line 7 ORA-06512: at line 1 hey can any1 suggest what exactly the problem is? The above code is for creating the table dynamically in Oracle 9i....
That's as close to a clear text message as you get from Oracle. ORA-01031 means you're not having the privilege to do what you just tried to do. ORA-06512 tell you where the error happened. So it means that your user is not having Create Table rights in tablespace Scott.
-
That's as close to a clear text message as you get from Oracle. ORA-01031 means you're not having the privilege to do what you just tried to do. ORA-06512 tell you where the error happened. So it means that your user is not having Create Table rights in tablespace Scott.
-
Technically it's the SCOTT schema, not necessarily a SCOTT tablespace. Scott (coincidentally)
Quite correct.
-
Quite correct.
-
Firstly thnx for your quick reply. And could you suggest a way in which the above error could be corrected. Even tried logging in as user:system. But it did not help. Same error.
-
Firstly thnx for your quick reply. And could you suggest a way in which the above error could be corrected. Even tried logging in as user:system. But it did not help. Same error.
Login with sys and whatever password you have, and set the correct correct privileges for user scott. Or create a new user with the proper access rights for schema scott. Or (this is from memory and might not be correct) add "SQLNET.AUTHENTICATION_SERVICES = (NTS)" into sqlnet.ora to be able to use NT security
-
Login with sys and whatever password you have, and set the correct correct privileges for user scott. Or create a new user with the proper access rights for schema scott. Or (this is from memory and might not be correct) add "SQLNET.AUTHENTICATION_SERVICES = (NTS)" into sqlnet.ora to be able to use NT security
I suppose it is unkind to laugh at people who are fumbling around with problems in technologies they do not understand, but this is a funny little exchange. First: SQLNET.AUTHENTICATION_SERVICES has nothing to do with the person's problem. Second: The person's error is quite self-explanatory. Third: One wonders why he (or his helpers here) did not just Google the error message. The solution would have been quickly found. The problem is that the person is either 1) trying to create a table in his own schema without having the CREATE TABLE privilege, or 2) trying to create a table in another schema without the CREATE ANY TABLE privilege. In addition, the user's account must have a quota on its default tablespace or have the UNLIMITED TABLESPACE privilege without one of which the ORA-01536 error will occur. Kind regards, Russ