Home » SQL & PL/SQL » SQL & PL/SQL » SQL script: loop through a dynamic list of sql scripts?
SQL script: loop through a dynamic list of sql scripts? [message #190663] Thu, 31 August 2006 10:45 Go to next message
ptap
Messages: 4
Registered: August 2006
Location: Dublin
Junior Member
I have a directory with a certain no of sql scripts:
upgrade1.sql, upgrade2.sql, ....., upgradeN.sql

I want my main script (upgrade.sql) to:

1. find from a user table in the schema what version V the schema is on
2. then run all the scripts after that version: upgradeV+1.sql,...upgradeN.sql

1. is easy but I am strugling with 2.

I can't use a PL-SQL loop between V+1 and N as SQL scripts cannot be called from PL-SQL

I need some way of running upgradeV+1.sql and continuing running the files until I have none left. In my script extract below, how can I loop through my start command?

column schema_version new_val DB_VERSION
select max(schema_version_no) schema_version
from schema_changes;

define UPGRADE_TO_RUN = &&DB_VERSION + 1

prompt Start upgrade at version: &&UPGRADE_TO_RUN
start upgrade/oracle/upgrade&&UPGRADE_TO_RUN

One solution would be to get each of my upgarde scripts to call the next but I think there a limit on the depth of sqlplus calls (starts)

Can anyone help, please?
Re: SQL script: loop through a dynamic list of sql scripts? [message #190665 is a reply to message #190663] Thu, 31 August 2006 11:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
generate a run-script and call that
by using utl_file, or dbms_output plus spool it is easy to generate the calls necessary
Re: SQL script: loop through a dynamic list of sql scripts? [message #190668 is a reply to message #190663] Thu, 31 August 2006 12:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This is a lot easier to do in Unix becuase you can manipulate the text in the temp file containing the scripts to run to include the required @, @@ or START, but it should give you some ideas...
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlplus scott/tiger@mydb

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 31 09:52:07 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> create table t1 (col1 varchar2(10));

Table created.

SQL> insert into t1 values ('10');

1 row created.

SQL> COLUMN col1  NEW_VAL AppVer format 9999 noprint;
SQL> SELECT col1 FROM t1;




SQL> -- For DOS, I don't know how to prefix the file with "@".
SQL> -- you can create 2 files upgrade10_1.sql and @upgrade10_1.sql. The @ one is just a dummy.
SQL> host dir /O /B c:\@upgrade&AppVer.*.sql > c:\files.sql

-- c:\files.sql will now contain a list of files to execute
-- starting with @...
SQL> host pause
Press any key to continue . . .

SQL> -- to give output file time to close
SQL> set echo on
SQL> @files.sql
-- This next line is the first line in files.sql
SQL> @upgrade10_1.sql
-- And this is the content of upgrade10_1.sql being executed
SQL> prompt this is upgrade10_1.sql
this is upgrade10_1.sql
SQL>

[Updated on: Thu, 31 August 2006 13:34]

Report message to a moderator

Re: SQL script: loop through a dynamic list of sql scripts? [message #190789 is a reply to message #190663] Fri, 01 September 2006 04:33 Go to previous message
ptap
Messages: 4
Registered: August 2006
Location: Dublin
Junior Member
Thanks guys for the interesting advice, I will look into it...
Previous Topic: How can i find Oracle Procedure File
Next Topic: Query
Goto Forum:
  


Current Time: Tue Dec 06 04:11:31 CST 2016

Total time taken to generate the page: 0.10275 seconds