?
Solved

Call Oracle sql-scripts from master script

Posted on 2008-11-14
9
Medium Priority
?
1,815 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:pcpaasche
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 22959334
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
 
LVL 10

Expert Comment

by:dbmullen
ID: 22960005
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
 
LVL 35

Expert Comment

by:johnsone
ID: 22960198
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 10

Accepted Solution

by:
dbmullen earned 1500 total points
ID: 22961729
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
 
LVL 35

Expert Comment

by:johnsone
ID: 22961810
What is wrong with the raise application error approach?  At least with that approach you can put an error message on the screen.
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22961914
there is no EXIT, it will continue to run

0
 
LVL 35

Expert Comment

by:johnsone
ID: 22962129
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
 

Author Comment

by:pcpaasche
ID: 22963949
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
 

Author Comment

by:pcpaasche
ID: 23027258
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question