Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Table
Dynamic Table [message #38546] Thu, 25 April 2002 20:33 Go to next message
Jamie Mack
Messages: 8
Registered: January 2002
Junior Member
Hey,
I need to make a table on the fly as access can not go dynamically right ugg the age old problem. Anyway i am using DBMS_SQL commands and i get
ORA-01031: insufficient privileges
Which suggests that the User does not have create table rights but if i output the string and run it in a SQL session it works fine. I am dropping the table first manually so it isnt that. I used this code before to do other stuff but it doesnt work in this situation.
Here is my dump

PROCEDURE BUILD_CSPMAS
IS

vaCREATE_TABLE VARCHAR2(1000);

CURSOR cuLOOP IS
SELECT PADMAS.PARCOD,
PADMAS.PARNAM,
PADMAS.PARLEN,
PADMAS.PARTYP
FROM IMMPROD.PADMAS PADMAS
ORDER BY PADMAS.PARCOD,
PADMAS.PARNAM;
rowLOOP cuLOOP%rowtype;

cid NUMBER:= dbms_sql.open_cursor;

BEGIN

vaCREATE_TABLE:= ' CREATE TABLE APP_BLU.BLU_CSPMAS1 (' ;
vaCREATE_TABLE:= vaCREATE_TABLE || ' CSPMAS1_ID NUMBER NOT NULL, ';
vaCREATE_TABLE:= vaCREATE_TABLE || ' TASK_ID NUMBER NOT NULL,';
vaCREATE_TABLE:= vaCREATE_TABLE || ' EQPNUM VARCHAR2 (24) NOT NULL, ';
vaCREATE_TABLE:= vaCREATE_TABLE || ' SEQNUM NUMBER (4) NOT NULL, ' ;
vaCREATE_TABLE:= vaCREATE_TABLE || ' PARCOD VARCHAR2 (4), ' ;
vaCREATE_TABLE:= vaCREATE_TABLE || ' PARNAM VARCHAR2 (6), ' ;
vaCREATE_TABLE:= vaCREATE_TABLE || ' PARVAL VARCHAR2 (20), ' ;
vaCREATE_TABLE:= vaCREATE_TABLE || ' U_VERSION VARCHAR2 (1))' ;


dbms_sql.parse(cid, vaCREATE_TABLE, dbms_sql.native);

dbms_sql.close_cursor(cid);

Thanks in advance
Jamie Mack
Re: Dynamic Table [message #38547 is a reply to message #38546] Thu, 25 April 2002 20:35 Go to previous messageGo to next message
Jamie Mack
Messages: 8
Registered: January 2002
Junior Member
Forgot to mention running oracle V8.0.5
Re: Dynamic Table [message #38601 is a reply to message #38546] Mon, 29 April 2002 20:56 Go to previous message
Jamie Mack
Messages: 8
Registered: January 2002
Junior Member
Logged in as DBA and granted create table priv. run procedure and it worked fine. So i am guessing that i had enherited create table from connect priv or something as

select * from SESSION_PRIVS
where rownum < 12

showed that i had CREATE TABLE previously.

Cheers jamie mack
Previous Topic: Re: Need Help bad
Next Topic: VERY urgent please help me -Trigger
Goto Forum:
  


Current Time: Thu Mar 28 07:05:55 CDT 2024