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

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 02 Nov 1998 23:03:13 GMT
Message-ID: <3642305c.34686286@dcsun4.us.oracle.com>

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;
>/

You can not do DDL in PL/SQL directly. You must use dynamic sql. Look at the package dbms_sql.

eg.

create or replace
procedure create_whtable as
  l_cursor number;
  l_status number;
begin
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse( l_cursor,

                  'DROP TABLE FA_DBA.FARS',
                  dbms_sql.native );

  l_status := dbms_sql.execute( l_cursor );   dbms_sql.parse( l_cursor,

                  'CREATE TABLE whtable TABLESPACE warehouse_data ' ||
                  'as SELECT * FROM sourceview',
                  dbms_sql.native );

  l_status := dbms_sql.execute( l_cursor );   dbms_sql.close_cursor( l_cursor );
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.

from sql*plus

SQL> declare
  2 job_id number;
  3 begin
  4 dbms_job.submit(

  5       job => job_id,
  6       what => 'create_whtable',
  7       next_date => sysdate,
  8      interval => 'sysdate+7' );

  9 end;
 10 /

This will schedule the job to run now and every seven days.

To use job make sure the following are defined in the init<sid>.ora and restart the database if you have to add them.

  job_queue_interval=                 60
  job_queue_keep_connections=         FALSE
  job_queue_processes=                1

This tells the database to wake up every 60 seconds and see if there are any jobs to run and to use one process to execute them.

>
>
>Any help would be much appreciated
>

Hope this helps.

chris.

>
>
>TIA,
>
>Steve Keider
Received on Mon Nov 02 1998 - 17:03:13 CST