Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Native Dynamic SQL

Native Dynamic SQL

From: Sue Wi-Afedzi <suewi_at_uoguelph.ca>
Date: Wed, 17 Jan 2001 13:35:31 -0600
Message-ID: <944ok0$1kr$1@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 - 13:35:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US