Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Native Dynamic SQL
I have a question about NDS. I think I must be missing something very basic here.
I want to create a table using NDS. I log onto the server with a standard account (ie no DBA privileges) and try running:
execute immediate 'create table sw1 (tmp number);';
I got the message:
immediate 'create table sw1 (tmp number);';
*
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "create table sw1 (tmp number);" when
expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "create table sw1 (tmp number);" to
continue.
OK, so it thought I was trying to run a procedure called immediate. So I put it into a block of code:
declare
begin
execute immediate 'create table sw1 (tmp number)';
end;
It worked. Nice. Created the table and all. Then I put it into a procedure:
create or replace procedure sw_tsting
as
begin
execute immediate 'create table sw1 (tmp number)';
end;
It compiled fine. But when I ran it I got the error:
ORA-01031: insufficient privileges ORA-06512: at "FR.SW_TSTING", line 4 ORA-06512: at line 2
The DBMS_SQL package has PUBLIC executable privileges assigned to it. FR (the owner of the procedure) has CREATE TABLE privileges as well as CREATE PROCEDURE. So I tried the whole thing with an account with DBA privileges. Same results. I even tried SYSTEM with the same results. I tried again using the SYS logon. Everything worked fine. So what am I missing?
Sue W. Received on Wed Jan 17 2001 - 13:35:31 CST