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 -> Whose privileges are missing?

Whose privileges are missing?

From: Robert Wagner <RobertWagner_at_alum.mit.edu>
Date: 2000/06/07
Message-ID: <ziA%4.1868$9E6.22009@newsr1.maine.rr.com>#1/1

I'm developing a db for eventual installation on my customer's server. For development, I'm using Oracle8 Personal on my development machine and my test target machine. Both are running Windows NT SP 6a. I exported my db from the development machine to the test machine.

Most things work fine on both machines, including many procedures in my packages. However, on the test machine (only) a procedure to create a view using DBMS_SQL gives me the message
>>

Error Number 5: ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "RWDEVELOPER.PKMAIN_SUBS", line 330
ORA-06512: at line 1

<<
Line 330 in my PKMAIN_SUBS IS the point where I try to made the DBMS_SQL call to create the view. Here's the code for the procedure (with line 330 marked):
PROCEDURE CreateTopView(testname IN VARCHAR2, main IN VARCHAR2, Sub IN VARCHAR2,
Samplename IN VARCHAR2)
IS
intResult INTEGER; -- In this case, a meaningless number cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR; strSQL VARCHAR2(200);
BEGIN
/* NOTE: Cannot use bind variables in DDL dynamic SQL statements */ StrSQL := 'CREATE OR REPLACE VIEW RWDEVELOPER.TOP_VIEW AS SELECT * FROM ' || ' TBLTEST' || testname || '_DATA ' || ' WHERE FLDMAINJOB= ''' || main || ''' AND FLDSUBJOB= ''' || Sub ; IF Samplename IS NOT NULL THEN
strSQL := strSQL || ''' AND SAMPLE = ''' || Samplename ; END IF;
StrSQL := strSQL || '''';
-- DBMS_OUTPUT.PUT_LINE (strSQL); -- Debugging DBMS_SQL.PARSE ( cur, strSQL , DBMS_SQL.NATIVE) ; -- Line 330 intResult := DBMS_SQL.EXECUTE ( cur );
DBMS_SQL.CLOSE_CURSOR ( cur );
END CreateTopView;

As far as I can see privileges are set up the same on the two machines. Logged on as RWDEVELOPER in SQL Plus, I can create the view on either machine using a CREATE OR REPLACE VIEW. However, logged on as another user (with the same roles and privileges on both macines) I can only do it on the development machine.

Any help would be greatly appreciated.
--Robert Received on Wed Jun 07 2000 - 00:00:00 CDT

Original text of this message

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