Re: stored procs executing DDL

A copy of this was sent to "C." <c_ferguson_at_rationalconcepts.com> (if that email address didn't require changing) On Wed, 16 Jun 1999 22:59:00 -0700, you wrote:

>Hi everyone, I need some explanation as to what's going on.
>The following, when executed throws ORA-1031, and the question is why.
>It's created in the system account and run as the system user, and still
>throws the exception on the DBMS_SQL.PARSE statement.
>

roles are never enabled during the execution of a procedure (except in Oracle8i, release 8.1 in some cases of Invokers rights routines).

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

grant create table to <OWNER>;

>CREATE OR REPLACE PROCEDURE test1 AS
> v_Cursor INTEGER;
> v_SQLStatement VARCHAR2(4000);
>BEGIN
> v_Cursor := DBMS_SQL.OPEN_CURSOR;
> v_SQLStatement := 'create table test1table(name varchar2(45) not
>null)';
>
> DBMS_SQL.PARSE(v_Cursor, v_SQLStatement, DBMS_SQL.V7);
> DBMS_SQL.CLOSE_CURSOR(v_Cursor);
>EXCEPTION
> WHEN OTHERS THEN
> DBMS_SQL.CLOSE_CURSOR(v_Cursor);
> DBMS_OUTPUT.PUT_LINE(SQLERRM);
> END test1;
>/
>
>GRANT EXECUTE ON test1 to PUBLIC;
>COMMIT;
>
>I am using Oracle 8.0.4 on Solaris 2.6.
>If you respond via email, please email me at
>c_ferguson_at_rationalconcepts.com
>Thanks in advance,
>Cindy Ferguson
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 20 1999 - 08:52:05 CDT