Conditionally executing ddl in pl/sql

[coubs]

I work for a software company that is developing a new(er) application and wanted to try to make the database portion of our upgrades more efficient. I came in after the initial development and release to customers and have found out that schemas from one customer to another are different!
So, in order to get all our customers' schemas in sync, I wanted to conditionally execute scripts that upgrade the database.
One of the things they did right was to insert a row into a table that recorded the name of the script that ran. So if script 30.01 which created a table called contracts was run, it would have inserted a row into the db_uprgade_history table with a row that said 30.01
Problem is that there were hundreds of scripts and will be more, so in order to make sure all scripts have been run I wanted to A) first check the db_upgrade_history table to see if a script has been run and B) if it has not been run, run it.

I guess to make it clear say I have 200 .txt files each containing ddl and dml statements to create tables, add domain data, etc. and have learned that some scripts are missing on some customers. I also want it so that the scripts are not run more than once to prevent errors (such as table already exists, duplicate key errors, etc.).
I was wanting to have a .bat file that starts a sqlplus session and pipe in all 200 .txt script files and have each file check the db_upgrade_history table to see if it has been run or not and if not run.

I have dabbled in putting a pl/sql wrapper around the original ddl/dml statements using and IF statement to see if there is a row returned from db_upgrade_history but I am running into a ton of limitaions like a create table works but a subsequent insert into that table fails becuase the table doesn't exist.

I was wondering if there are any Oracle application DBA's that have a similar experience - in releasing automated schema upgrades and how they have done it?

I have done this in prior jobs using Korn shell scripts but this place uses NT/2000 so all I have is DOS. To make things a wee bit more interesting, all our software runs on either Oracle or MS SQL. I have this problem solved by using an IF statement wrapper on each script and would like to be consistent and find a way to do it in Oracle. Here is a sample of the MS SQL script:

if not exists (select * from db_upgrade_history where script_name = '41_la_17.sql')
begin


INSERT INTO system_option
(option_category_id, option_keyword, option_value, encrypted, option_encrypted)
VALUES
(6, 'Single UOM for Volumes', 'Yes', 'No', NULL)

insert into db_upgrade_history (application_module, script_name, db_version)
values ('LA', '41_la_17.sql', '41.17')

update db_version set db_version = '41.17' where application_module = 'LA'

end
go


Am I missing something obvious???? Oh man is that an inviation to flame or what?

Any info, suggestions would be much appreciated.

Thanks,

Bill

[stecal]

Conditionally executing DDL is no problem. Basically a two-step process for you: check for existence of the item of interest, if it does not exist, update the schema. The major problem has to do with determining where in the update cycle or chain a particular database/customer application happens to be.

A simple outline you can implement in PL/SQL can be like this:

DECLARE
v_cnt number;
BEGIN
select count(*) into v_cnt
from whatever_table
where whatever_condition_you_are_looking_for;
-- v_cnt=0 means you need to apply an update
IF v_cnt = 0 THEN
call a procedure, run an update script, do what needs to be done
END IF;
END;

This outline can be more sophisticated, where you pass in a table name in a procedure (then you're using dynamic SQL), and each table is checked in a separate script. Or, you can grind through each table that needs to be checked and perform the updates in a series of sequential IF-THEN-ELSE blocks.

[coubs]

Thanks but I have tried this already. But it looks like I cannot create a table and then immediately insert into it, I also tried a script which modifies a column to not null and a subsequent update statement on that column fails. Both scenarios give me this error:

insert into billtest (col1) values (12345);
*
ERROR at line 10:
ORA-06550: line 10, column 13:
PLS-00201: identifier 'BILLTEST' must be declared
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored



Here is the code I try to execute:

DECLARE
v_cnt number;
BEGIN
select count(*) into v_cnt
from db_upgrade_history
where script_name = '32a_ba_01.sql';
-- v_cnt=0 means you need to apply an update
IF v_cnt = 0 THEN
execute immediate 'create table billtest ( col1 int)';
insert into billtest (col1) values (12345);
END IF;
end;


I am very VERY sure I am missing something, and it is mostly likely going to be something obvious, and perhaps I am letting my past experience in other dbms's cloud my vision...I came from the dark side (Sybase/MS SQL for 10 years) and of course there is no time for me to even go on a pl/sql course at this place.

Any other suggestions?

Thanks again.

[jmodic]

When you run your PL/SQL block, Oracle have first to parse it, to check if you have the neccessary privileges, if the used objects exist etc... In your example, during the compšile time the table BILLTEST does not yet exist, hence the error.

So you either have to do it:

a) In two sepparate blocks, so that when it compiles the second block with the insert statement, the table BILLTEST will allready exist (created in the first PL/SQL block)

or
b) Use native dynamic SQL for INSERT statement, the same way as you are doing for the CREATE statement:

DECLARE
v_cnt number;
BEGIN
select count(*) into v_cnt
from db_upgrade_history
where script_name = '32a_ba_01.sql';
-- v_cnt=0 means you need to apply an update
IF v_cnt = 0 THEN
execute immediate 'create table billtest ( col1 int)';
EXECUTE IMMEDIATE 'insert into billtest (col1) values (12345)';
END IF;
end;