oracle-databaseplsqlpartitioning

Within PL/SQL script create table based on Oracle Edition


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?


Solution

  • 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.