Dynamic Table [message #38546] |
Thu, 25 April 2002 20:33 |
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 #38601 is a reply to message #38546] |
Mon, 29 April 2002 20:56 |
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
|
|
|