| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL function to create a table in alternate tablespace
On Mon, 02 Nov 1998 19:19:14 GMT, smk3_at_mead.com (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.
>
>
>Any help would be much appreciated
Seems you'll have to read some documentation about at least two supplied packages: DBMS_SQL and DBMS_JOB.
The first one will enable you to use DDL statements (like DROP/CREATE TABLE). The second one will enable you to schedule the procedure's periodical execution.
For start, you can look into Oracle Server Application Developer's Guide for an introduction of the DBMS_SQL and into Oracle Server Administartor's Guide for use of DBMS_JOB. Also, an excelent source of information are the package's deffinitions themselfs. Look for "dbmssql.sql" and "dbmsjob.sql" in your rdbms/admin directory. Both packages are too complex to be explained in a single usenet message if you wan't to use them correctly and efficiently.
>TIA,
>
>Steve Keider
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |