| Author |
Mixing DDL & PL/SQL
|
|
| Tarby777 2005-09-26, 7:23 am |
| 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)='YE
S' then
begin
CREATE TABLESPACE "test" LOGGING
SEGMENT SPACE MANAGEMENT MANUAL
DATAFILE 'C:\ORACLE\ORADATA\T
ESTBED\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
| |
| Tarby777 2005-09-26, 7:23 am |
| I played around some more, and this works for me:
declare create_it varchar2(8);
begin
create_it := 'yes';
if upper(create_it)='YE
S' then
execute immediate
'CREATE TABLESPACE "test" LOGGING SEGMENT SPACE MANAGEMENT MANUAL
DATAFILE ''C:\ORACLE\ORADATA\
TESTBED\test.dbf'' SIZE 10M AUTOEXTEND
ON NEXT 1M MAXSIZE 1500M';
end if;
end;
Regards,
Nick
| |
| sybrandb@yahoo.com 2005-09-26, 7:23 am |
| It is possible? Definitely!
Is it bad practice, to create a tablespace in a stored procedure?
DEFINITELY.
Has it anything to do with *design*? ABSOLUTELY NOT!!!!!!
I would kick everyone out creating anything on the fly.
--
Sybrand Bakker
Senior Oracle DBA
| |
| Tarby777 2005-09-26, 9:23 am |
| It's not a stored procedure - it's just a SQL script that gets run
during the course of an upgrade.
|
|
|
|