Link to home
Start Free TrialLog in
Avatar of pcpaasche
pcpaascheFlag for Norway

asked on

Call Oracle sql-scripts from master script

Hi Experts!

I have a series of upgrade scripts for my database, one script in each sql-file. I would like to run these scripts either one-by-one, or from a master script that calls the other scripts.

Sample master script (in file master.sql):
@CE_UpgradeBuild272_285.sql
@CE_UpgradeBuild285_286.sql

Sample script (from CE_UpgradeBuild285_286.sql) is found in the code snippet below.

Running these scripts one-by-one works fine, but the if the scripts are initiated from master.sql, it fails. Why is that?

Also, the if statement described in words below the select-statement in line 1 in the code snippet below, I need some help to implement.

select  getCalcEngineBuildVersion from dual;
--If return value from select above is different from 285 I would like to exit execution of this script, and the master.sql-script. How should that be implemented?
 
---START UPGRADE SCRIPT (simplyfied for this question)-------------------
	ALTER TABLE VARIABLELOGSTATEMENT ADD (value NUMBER);
---END UPGRADE SCRIPT---------------------
 
---START RESET VERSION NUMBER-------------
 
	CREATE OR REPLACE FUNCTION getCalcEngineBuildVersion RETURN NUMBER IS
	BEGIN
	   RETURN 286;
	END getCalcEngineBuildVersion;
 
---END RESET VERSION NUMBER---------------
/

Open in new window

Avatar of johnsone
johnsone
Flag of United States of America image

What is the error when you put then all together?

As far as version checking, this should be a good strating point.  NOTE:  the whenever clause would need to be part of the master.sql as well.
whenever sqlerror exit;
declare
  vnum number;
begin
  select getCalcEngineBuildVersion
    into vnum
    from dual;
  if vnum != 285 then
    raise_application_error(-20100, 'Version mismatch');
  end if;
end;
/

Open in new window

WHENEVER SQLERROR EXIT SQL.SQLCODE

select  getCalcEngineBuildVersion
from dual
where getCalcEngineBuildVersion = 285
;

        ALTER TABLE VARIABLELOGSTATEMENT ADD (value NUMBER);
---END UPGRADE SCRIPT---------------------
 
---START RESET VERSION NUMBER-------------
 
        CREATE OR REPLACE FUNCTION getCalcEngineBuildVersion RETURN NUMBER IS
        BEGIN
           RETURN 286;
        END getCalcEngineBuildVersion;
 
---END RESET VERSION NUMBER---------------
/
I do not believe that will work.

This statement:

select  getCalcEngineBuildVersion
from dual
where getCalcEngineBuildVersion = 285
;


Will result in a no rows selected.  That is not an error and will not cause the script to exit.
ASKER CERTIFIED SOLUTION
Avatar of dbmullen
dbmullen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What is wrong with the raise application error approach?  At least with that approach you can put an error message on the screen.
there is no EXIT, it will continue to run

No it will not.  The whenever causes the exit.  I tested it.

Take 2 scripts:

------------------------------------------------------
script1.sql
------------------------------------------------------
whenever sqlerror exit
@script2
select sysdate from dual;


------------------------------------------------------
script2.sql
------------------------------------------------------
whenever sqlerror exit
declare
   cnt pls_integer;
begin
   select count(1)
     into cnt
    from dual;
  if cnt = 1 then
    raise_application_error(-20100, 'Error test');
  end if;
end;
/


Run script 1 and you will get this:


SQL> @script1
declare
*
ERROR at line 1:
ORA-20100: Error test
ORA-06512: at line 8


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production



Change script2.sql to this:


whenever sqlerror exit
declare
   cnt pls_integer;
begin
   select count(1)
     into cnt
    from dual;
  if cnt = 0 then
    raise_application_error(-20100, 'Error test');
  end if;
end;
/


And you get this when you run script1:

SQL> @script1

PL/SQL procedure successfully completed.

SYSDATE
-----------
14-NOV-2008




Avatar of pcpaasche

ASKER

Hi all!

Thank's for your answers so far.

There is a small error in the first line of the script in the code snippet. It should read:
select  getCalcEngineBuildVersion() from dual;  -- Note the parenthesis

I haven't had the time to read through your answers well enough yet, but will do so tomorrow.

I will be back...:-)
Hi again!

I have been working on your suggestions and have found dbmullen's to suit my needs best.

dbmullen:
Would you please explain the meaning of these two statements or point me to relevant documentation on the web:
  1. COLUMN get_version new_value get_version
  2. @@&get_version
Also, I would like the exitscript.sql, which now contains the word exit, not to exit the whole execution, but rather continue with the next upgrade script. That way my master.sql can be run at any time without modification, regardless of the current version of the database.

Do you know how to achieve that?
--======start master.sql==============================
@CE_UpgradeBuild272_285.sql
@CE_UpgradeBuild285_286.sql
--======end master.sql================================
 
 
--========================================
--
--Upgrading from version 272 to 285
--
 
COLUMN get_version new_value get_version
 
  select
    case
      when getCalcEngineBuildVersion <> 272 then 'exitscript.sql'
    end get_version
  from dual;
 
    @@&get_version
 
---START UPGRADE SCRIPT-------------
ALTER TABLE FORMULATYPEVERSION ADD (UIVERSIONNUMBER VARCHAR2(255 BYTE));
ALTER TABLE FORMULAVERSION ADD (UIVERSIONNUMBER VARCHAR2(255 BYTE));
---END UPGRADE SCRIPT----------------
 
 
---START RESET VERSION NUMBER-------------
    CREATE OR REPLACE FUNCTION getCalcEngineBuildVersion RETURN NUMBER IS
    BEGIN
       RETURN 285;
    END getCalcEngineBuildVersion;
---END RESET VERSION NUMBER---------------
 
end;
/
--========================================

Open in new window