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

pcpaascheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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

0
dbmullenCommented:
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---------------
/
0
johnsoneSenior Oracle DBACommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dbmullenCommented:
take a page out of oracle install scripts
create an empty file that just has EXIT
create an other file that has your stuff



COLUMN get_version new_value get_version
select  case when getCalcEngineBuildVersion = 285 then 
                'runme.sql'
        else    'something_empty.sql'
        end get_version
from dual
;
 
@@&get_version

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
What is wrong with the raise application error approach?  At least with that approach you can put an error message on the screen.
0
dbmullenCommented:
there is no EXIT, it will continue to run

0
johnsoneSenior Oracle DBACommented:
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




0
pcpaascheAuthor Commented:
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...:-)
0
pcpaascheAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.