Re: ORA-01031 Insufficient priveledges et al.

Hi Paul,
  did you grant create any table to the owner of the procedure? This must be a direct grant and not one granted through roles.

hope that helps,
cindy

Paul LeDuc wrote:

> Hello,
> I am hoping someone can help me out with a nagging problem. I developed a
> package that uses DBMS_SQL against an Oracle 8.0 database. Since the code
> was originally developed we have rebuilt the database on a new server with
> the same version of Oracle on both servers.
>
> Now when the code is run on the new box I receive the following errors:
> ORA-01031 Insufficient priviledges
> followed by
> ORA-06512 .... four times
>
> The offending code follows:
>
> CREATE OR REPLACE PACKAGE BODY TEST IS
> PROCEDURE MakeTempTbl IS
> lCurID INTEGER; -- Cursor ID for processed statement
> lSQLstr VARCHAR2(500); -- SQL string to be executed
>
> BEGIN
> -- Initialize the SQL statement to process
> lSQLstr := 'CREATE TABLE test (FileRow VARCHAR2(50) NULL)';
>
> -- Open cursor using previously initialized statement
> lCurID := DBMS_SQL.OPEN_CURSOR;
> -- Parse the statement and execute it
> DBMS_SQL.PARSE(lCurID, lSQLstr, DBMS_SQL.NATIVE);
> -- Close the cursor
> DBMS_SQL.CLOSE_CURSOR(lCurID);
>
> END; -- MakeTempTbl
>
> END;
> /
>
> I have granted EXECUTE on DBMS_SQL to the user executing the package.
>
> Also the user can create the same table manually that the code attempts to
> create without any trouble.
>
> Any assistance would be greatly appreciated.
>
> Paul LeDuc
> pleduc_at_lightspeed.net
> AND
> ledupa_at_priorityhealth.com
Received on Tue Nov 09 1999 - 01:09:15 CST