Java code in oracle DBMS_JOB and permissions

[PMCS]

Hello,
We have some java code which is published as a package in oracle. We have this piece of code:

Statement createTempTable = getConnection().createStatement();
createTempTable.execute("create table store_list_dups as select * from store_list where rownum < 0");
createTempTable.close();
When we execute the published package, it is able to create this table. But the same package if its executed as a DBMS_JOB it throws this message.

oracle.jdbc.driver.OracleSQLException: ORA-01031: insufficient privileges.

Could someone shed some light over this issue:

[slimdave]

Not a direct answer to the question, but why are you creating this table? Have you considered the lower overhead of using temporary tables? Also there is generally a problem with creating tables on the fly like this because you cannot validate a procedure that references it before it is created.

[PMCS]

Well , this table is used as a temp table and will be deleted as well when the job completes.

[DaPi]

Originally posted by PMCS
Well , this table is used as a temp table and will be deleted as well when the job completes.

That's exactly what a GTT would do nicely. Create it once and the data's cleaned out automatically at the end of the session, only the definition remains. OK, there are some limitations and the CBO can have probs with them (no stats, unless you fudge them): http://download-west.oracle.com/docs...3a.htm#2061433