pcpaasche
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.sq l
@CE_UpgradeBuild285_286.sq l
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.
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.sq
@CE_UpgradeBuild285_286.sq
Sample script (from CE_UpgradeBuild285_286.sql
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---------------
/
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---------------
/
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(-2 0100, '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(-2 0100, 'Error test');
end if;
end;
/
And you get this when you run script1:
SQL> @script1
PL/SQL procedure successfully completed.
SYSDATE
-----------
14-NOV-2008
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(-2
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(-2
end if;
end;
/
And you get this when you run script1:
SQL> @script1
PL/SQL procedure successfully completed.
SYSDATE
-----------
14-NOV-2008
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...:-)
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(
I haven't had the time to read through your answers well enough yet, but will do so tomorrow.
I will be back...:-)
ASKER
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:
Do you know how to achieve that?
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:
- COLUMN get_version new_value get_version
- @@&get_version
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;
/
--========================================
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.
Open in new window