ORA-01031 Insufficient priveledges et al.

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 - 00:25:56 CST