Mixing DDL & PL/SQL

From: Tarby777 <nick_williamson_at_mentorg.com>
Date: 26 Sep 2005 03:22:34 -0700
Message-ID: <1127730154.664733.281120@z14g2000cwz.googlegroups.com>

Hi all,

I need to conditionally create a tablespace in a script. The idea is that the user will set a parameter in the script, and the tablespace will only get created if that parameter contains a given value.

I'm assuming that I need to use PL/SQL to get the necessary IF/THEN logic happening in the script. I don't have any PL/SQL experience and I've come unstuck - the script doesn't seem to like me embedding DDL inside a PL/SQL construct. Here's the relevant snippet:

declare create_it varchar2(8);

begin
  create_it := 'yes';
  if upper(create_it)='YES' then
    begin
    CREATE TABLESPACE "test" LOGGING
    SEGMENT SPACE MANAGEMENT MANUAL
    DATAFILE 'C:\ORACLE\ORADATA\TESTBED\test.dbf' SIZE 10M     AUTOEXTEND ON NEXT 1M MAXSIZE 1500M;     end;
  end if;
end;

I get one of those "Encountered the symbol 'CREATE' when expecting one of the following' errors. Is it possible to do what I want, or do I have to have two separate scripts?

TIA
Nick Received on Mon Sep 26 2005 - 05:22:34 CDT