Hi Tom,
I've tried looking on your answers but couldn't find a good solution to my case.
In my company we use EJBs and plain java and connect to the database using oracle's thin JDBC. Basically what I have to do is an end-of-day procedure that does the following:
1) From the n tables that exist with daily values, drop the oldest,
2) Create a new empty table exactly like the n-1 tables (down to the indexes)
based on the newest table before the created table.
So if I have:
TBL_IDX_TESTER_20081017
TBL_IDX_TESTER_20081018
TBL_IDX_TESTER_20081019
after running the procedure I should have
TBL_IDX_TESTER_20081018
TBL_IDX_TESTER_20081019
TBL_IDX_TESTER_20081020
TBL_IDX_TESTER_20081017 is dropped, and an empty TBL_IDX_TESTER_20081020 is created with everything (down to indexes) imported from the last available table (TBL_IDX_TESTER_20081019).
Now, the problem is that this can't be done directly inside the java code because of a client's constraint - NO DDL INSIDE THE JAVA APPLICATION. The user that connects to db from the java code has no right to do anything else then select, update, delete and insert.
So I've thought of creating stored procedures that had the right of doing the required work (i.e., dropping and creating the tables) and then GRANTing EXECUTE to the user inside the java application to make the work. That would safeguard that no DDL code is inside the java application, but on the other hand I could do the required work.
My inquiries here on Ask Tom show that you strongly disapprove of that approach.
Is there any way of doing that? I don't mind using anything else, I just don't seem to find another solution.
Here's the table creation commands:
create tablespace testind
logging
datafile '/export/home/oracle/databases/ora10/testind.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
CREATE TABLE TBL_IDX_TESTER_20081017(
FOREIGN_ID CHAR(16) NOT NULL,
KEYWRD VARCHAR2(40) NOT NULL,
INDEX_NO INTEGER NOT NULL,
VALUE VARCHAR2(200) NOT NULL
) TABLESPACE TESTIND;
ALTER TABLE TBL_IDX_TESTER_20081017 ADD CONSTRAINT TBL_IDX_TESTER_20081017_PK PRIMARY
KEY(INDEX_NO);
INSERT INTO TBL_IDX_TESTER_20081017 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('ABCDE', 'KEYWRD1', 1, 'TESTVALUE1');
INSERT INTO TBL_IDX_TESTER_20081017 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('BCDEF', 'KEYWRD2', 2, 'TESTVALUE2');
INSERT INTO TBL_IDX_TESTER_20081017 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('CDEFG', 'KEYWRD3', 3, 'TESTVALUE3');
CREATE INDEX TBL_IDX_TESTER_20081017_IDX1 ON TBL_IDX_TESTER_20081017 (KEYWRD,
FOREIGN_ID);
CREATE TABLE TBL_IDX_TESTER_20081018(
FOREIGN_ID CHAR(16) NOT NULL,
KEYWRD VARCHAR2(40) NOT NULL,
INDEX_NO INTEGER NOT NULL,
VALUE VARCHAR2(200) NOT NULL
) TABLESPACE TESTIND;
ALTER TABLE TBL_IDX_TESTER_20081018 ADD CONSTRAINT TBL_IDX_TESTER_20081018_PK PRIMARY
KEY(INDEX_NO);
INSERT INTO TBL_IDX_TESTER_20081018 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('ABCDE', 'KEYWRD1', 1, 'TESTVALUE1');
INSERT INTO TBL_IDX_TESTER_20081018 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('BCDEF', 'KEYWRD2', 2, 'TESTVALUE2');
INSERT INTO TBL_IDX_TESTER_20081018 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('CDEFG', 'KEYWRD3', 3, 'TESTVALUE3');
CREATE INDEX TBL_IDX_TESTER_20081018_IDX1 ON TBL_IDX_TESTER_20081018 (KEYWRD,
FOREIGN_ID);
CREATE TABLE TBL_IDX_TESTER_20081019(
FOREIGN_ID CHAR(16) NOT NULL,
KEYWRD VARCHAR2(40) NOT NULL,
INDEX_NO INTEGER NOT NULL,
VALUE VARCHAR2(200) NOT NULL
) TABLESPACE TESTIND;
ALTER TABLE TBL_IDX_TESTER_20081019 ADD CONSTRAINT TBL_IDX_TESTER_20081019_PK PRIMARY
KEY(INDEX_NO);
INSERT INTO TBL_IDX_TESTER_20081019 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('ABCDE', 'KEYWRD1', 1, 'TESTVALUE1');
INSERT INTO TBL_IDX_TESTER_20081019 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('BCDEF', 'KEYWRD2', 2, 'TESTVALUE2');
INSERT INTO TBL_IDX_TESTER_20081019 (FOREIGN_ID, KEYWRD, INDEX_NO, VALUE) VALUES
('CDEFG', 'KEYWRD3', 3, 'TESTVALUE3');
CREATE INDEX TBL_IDX_TESTER_20081019_IDX1 ON TBL_IDX_TESTER_20081019 (INDEX_NO,
FOREIGN_ID);
Note that I've changed the index TEST_INDEX_20081019_IDX1 to stress the point that one (or more) indexes may be different on the tables even though they are the same structure. On a real case, the last day table may have been changed to have more or less indexes than the previous ones, and we need it replicated to the new table to be created.
Thanks a lot for your help :)
...
My inquiries here on Ask Tom show that you strongly disapprove of that approach.
.....
correct, it is a very very bad, horrible idea to do DDL like this on a production system in an ongoing fashion. One wonders what "design" (anti-design if you ask me) could have gotten us to this.
Basically, you are saying the java application dynamically generates all of it's sql based on the day?!?!?!?!
....
So I've thought of creating stored procedures that had the right of doing the required work (i.e., dropping and creating the tables) and then GRANTing EXECUTE to the user inside the java application to make the work. That would safeguard that no DDL code is inside the java application, but on the other hand I could do the required work.
......
it sounds like your client had a reasonable request - NO DDL. So they said it wrong by stating "in a java program", but only probably because you said 'we will develop in java'. You are missing their point - they know how bad DDL in an ongoing system would be.
do not drop and create.
If you must go with this really bad "table of the day" idea - forcing all sql to be 'new' every day, day in day out - just
a) truncate oldest
b) alter table oldest rename to newest
that way, you don't have to worry about copying indexes, grants, data types, any new things added to it over time.
but seriously, rethink this "design", give the "design" a bit more thought - I cannot imagine why you would desire this sort of schema, it would be nightmarish to code to. What does your code do around midnight?