Usually I create Oracle tables by means of a script and then run that script from the SQL*Plus command line by means of SQL> @script_name
.
Now we have a table that needs to be partitioned if we're using the Oracle Enterprise Edition. Within our development environment we have the Standard Edtion and the table can therefore not be partitioned.
I therefore created 2 different scripts:
foo_partitioned.sql
CREATE TABLE foo
(
x NUMBER NOT NULL
)
PARTITION BY RANGE (x)
(
PARTITION part_1 VALUES LESS THAN (10),
PARTITION part_2 VALUES LESS THAN (20),
PARTITION part_3 VALUES LESS THAN (30)
);
ALTER TABLE foo ADD CONSTRAINT pk_foo PRIMARY KEY (x);
foo_not_partitioned.sql
CREATE TABLE foo
(
x NUMBER NOT NULL
);
ALTER TABLE foo ADD CONSTRAINT pk_foo PRIMARY KEY (x);
Then, within a separate script, I call one of the above based on the Oracle Edition:
create_foo.sql
DECLARE
vEnterprise NUMBER;
BEGIN
SELECT COUNT(*)
INTO vEnterprise
FROM v$version
WHERE banner LIKE 'Oracle%Enterprise Edition%';
IF (vEnterprise = 0) THEN
@foo_not_partitioned.sql;
ELSE
@foo_partitioned.sql;
END IF;
END;
/
This gives me error:
ORA-06550: line 11, column 1:
PLS-00103: Encountered the symbol "CREATE" ......
This happens of course because "CREATE TABLE" is not valid within PL/SQL.
I could compose a string with the create statement and then call EXECUTE IMMEDIATE
but I was wondering if this is possible without changing the 2 original scripts.
I mean, is this possible modifying only the wrapping script create_foo.sql?
column script_name new_value script_name
select case count(*)
when 0 then 'foo_not_partitioned.sql'
else 'foo_partitioned.sql'
end script_name
from v$version
where banner like 'Oracle%Enterprise Edition%';
@&script_name
This should work. It dynamically puts the right script name in a SQL*Plus variable and then executes the script with that name.