ORACLE PL/SQL - SCHEDULE CREATE TABLE

Question

I need to create a table every morning based on overnight generated data from a massive table. This will then be accessed by a handful of users form Excel.

My initial approach was to use a materilazed view and when this was rejected (for political reasons) to used Managed XLL but this was rejected for other reasons. I don't want to get messed up with Temporary tables and so really just need to know how to schedule an Oracle Create Table statement as our DBA says it can't be done.

My faith in SO users sasy otherwise though!

Answer 1

I don't see why you have to create a new table every morning and not use an existing one?

---

This creates your table from PL/SQL. Is this what you want?

CREATE OR REPLACE PROCEDURE make_table AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE your_table ( column_1 INT PRIMARY KEY, column_2 VARCHAR2(10) )';
END make_table;
/

EXEC make_table;

Your user needs to have the necessary grants, grants given by role don't apply to compiled PL/SQL code.