Re: Native Dynamic SQL

1 The ; in your first attempt is redundant. Commands are not finished by a ; in NDS
2 You have privilege assigned using a role. As roles are volatile, they are *ignored* running a procedure. You can resolve this by either
a) grant direct privilege to the user *owning* the procedure b) using the pragma invoker_rights on procedure definition, which *will* use the roles (8i only)

Hth,
Sybrand Bakker, Oracle DBA

"Sue Wi-Afedzi" <suewi_at_uoguelph.ca> wrote in message news:944ok0$1kr$1_at_testinfo.cs.uoguelph.ca...
> 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 - 16:24:10 CST