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

You can not do DDL type stuff within a PLSQL procedure/function except via dynamic SQL.
But, there is an in - built package called DBMS_SQL that allows you to do this sort of thing.

Here is a bit of an example for you:

create or replace procedure create_a_table is

 vCreate varchar2(1000) := null;
 I_CURSOR integer;
 I_VOID integer;
 vtable varchar2(30) := null;
begin
vtable := 'A_table';

vCreate := 'create table '||vtable||

   '(user_id VARCHAR2(10) NOT NULL,    module_name VARCHAR2(60) NOT NULL,

  record_id     VARCHAR2(50)   NULL,
  operation     VARCHAR2(10)   NOT NULL,
  date_time     DATE           NOT NULL,
  notes         VARCHAR2(2000) NULL)';

 I_CURSOR := dbms_sql.open_cursor;
 dbms_sql.parse(I_CURSOR, vCreate, dbms_sql.v7);  I_VOID := dbms_sql.execute(I_CURSOR);
 dbms_sql.close_cursor(I_CURSOR);

end;

A good book on PLSQL etc is Oracle PL/SQL Programming by Steven Feuerstein with Bill Pribyl published by O'Reilly.

Hope this helps

Rod J. Stewart

Steve Keider wrote in message <363dc217.494171460_at_news.mindspring.com>...
>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
>
>
>
>TIA,
>
>Steve Keider
Received on Mon Nov 02 1998 - 17:54:10 CST