Re: PL/SQL function to create a table in alternate tablespace

From: Gerard M. Averill <gaverill_at_chsra.wisc.edu>
Date: Thu, 05 Nov 98 19:27:13 GMT
Message-ID: <71su21$12g2$2@news.doit.wisc.edu>

>Steve Keider wrote:
>
>> Hi.
>>
>> I am pretty new to Oracle and PL/SQL, and am having a hard time
>> finding documentation with good examples.
>>
>> 1. I would like to create a Procedure which drops a table in my
>> warehouse tablespace and then recreates it from the source tables.
>> I think the code should be something like this.
>>
>> CREATE OR REPLACE FUNCTION create_whtable
>> IS
>> BEGIN
>> DROP TABLE FA_DBA.FARS;
>> CREATE TABLE whtable TABLESPACE warehouse_data SELECT * FROM
>> sourceview;
>> END;
>> /
>>
>> 2. I then would like to schedule this procedure to be run once a week
>> using the DBMS_JOB package, but I have no idea how to use this
>> package.

If it's not really necessary to have this all take place in a PL/SQL procedure or function, I'd suggest using a job scheduled at the OS level using something like (WIN)AT (NT) or CRON (Unix). Schedule a job to startup SQL-Plus and run a command file containing your DDL (e.g.):

        ...
        DROP TABLE FA_DBA.FARS;
        /
        CREATE TABLE whtable TABLESPACE warehouse_data AS ...;
        /
        ...

With this approach you don't have to worry about writing your PL/SQL procedure/function using DBMS_SQL package (these DDL statements could only be coded directly in a stored procedure as you indicated above) or about using DBMS_JOB to schedule a job in Oracle.

If it has to be an all-PL/SQL solution, then read up on DBMS_SQL and DBMS_JOB packages (there is documentation and examples in the Oracle documentation, as well as all over these newgroups) -- they're not difficult to use.

G.

---

Gerard M. Averill, Associate Researcher CHSRA, University of Wisconsin - Madison GAverill_at_chsra.wisc.edu Received on Thu Nov 05 1998 - 13:27:13 CST