Re: dynamic SQL question

In article <369AC1EA.16AF320F_at_NOT.com>,
  snowden_at_NOT.com wrote:
> Looks like a permission problem. You have to grant execute privs on this
> package to yourself or to public.
>
> Roger Snowden
> Sr. Systems Engineering Specialist
> Oracle Corporation
> rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious
>
> steve_young_at_my-dejanews.com wrote:
> >
> > I have the following procedure built to use the dynamic SQL:
> >
> > CREATE OR REPLACE PROCEDURE exec(string IN varchar2) AS
> > cursor_name INTEGER;
> > ret INTEGER;
> > BEGIN
> > cursor_name:= DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);
> > ret := DBMS_SQL.EXECUTE(cursor_name);
> > DBMS_SQL.CLOSE_CURSOR(cursor_name);
> > END;
> > .
> > /
> >
> > After I compile it, I got an error when I run it:
> > SQL> execute exec('create table t(f integer)');
> > ERROR at line 1:
> > ORA-01031: insufficient privileges
> > ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> > ORA-06512: at "SYS.DBMS_SQL", line 32
> > ORA-06512: at "USER_NAME.EXEC", line 6
> > ORA-06512: at line 1
> >
> > I have the dbmssql.sql and prvtsql.plb compiled on SYS already. Anyone can
> > tell me what happen? Thanks!!
> >
> > Steve
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

You should grant 'CREATE TABLE' to user, who executes this stored procedure.

Igor

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 12 1999 - 00:00:00 CST