Re: SQL*PLus script for running external scripts

From: ddf <oratune_at_msn.com>
Date: Mon, 9 Feb 2009 05:58:08 -0800 (PST)
Message-ID: <edd44cee-9562-4b0c-946e-5be9a3653c2b_at_r13g2000vbp.googlegroups.com>

On Feb 9, 7:09 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> I have a collection of text files where I've been writing down the code
> to create and populate my app's database objects. A typical file looks
> like this:
>
> /*
>   * Test table - test.sql
>   */
> CREATE TABLE TEST (
>         TEST_ID NUMBER NOT NULL ENABLE,
>         NAME VARCHAR2(50),
>
>         CONSTRAINT TEST_PK PRIMARY KEY (TEST_ID)
> );
>
> COMMENT ON TABLE TEST IS 'Test table';
>
> -- Name is unique
> ALTER TABLE TEST
> ADD CONSTRAINT TEST_UK1 UNIQUE (NAME)
> ENABLE;
>
> There're a lot of files and they need to be run in a specific order so
> I'm trying to compose a SQL*Plus script to run them all. But I've never
> worked with SQL*Plus before and I can't grasp the basics from the
> official manuals.
>
> Apparently, I need to use the _at_@ operator [1]. It does indeed run the
> external files, but I get a syntax error on every line: it seems to be
> expecting single-line commands. The manual mentions the need to run "SET
> SQLTERMINATOR" [2] but I can't figure out how.
>
> I'd appreciate some help to get started. I'm running these tests in a
> local installation of Oracle Database 10g Express Edition Release
> 10.2.0.1.0.
>
> [1]http://download-west.oracle.com/docs/cd/B12037_01/server.101/b12170/c...
> [2]http://download-west.oracle.com/docs/cd/B12037_01/server.101/b12170/c...
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web:http://bits.demogracia.com
> -- Mi web de humor al baño María:http://www.demogracia.com
> --

The _at_@ syntax says 'run this script from the same location as the source script', so if you do, indeed, locate all of these in one directory the _at_@ syntax will be what you need. As an example:

The directory contents --

c:\sql\orcl\examples\tst>dir
 Volume in drive C has no label.
 Volume Serial Number is A08D-1EEA

 Directory of c:\sql\orcl\examples\tst

02/09/2009  07:55 AM    <DIR>          .
02/09/2009  07:55 AM    <DIR>          ..
02/09/2009  07:55 AM                76 master.sql
02/09/2009  07:51 AM               138 script01.sql
02/09/2009  07:54 AM               239 script02.sql
               3 File(s)            453 bytes
               2 Dir(s)  21,441,359,872 bytes free

c:\sql\orcl\examples\tst>

The master.sql script --

set echo on
spool master.log
_at__at_script01.sql
_at__at_script02.sql
spool off

Executing master.sql --

SQL> _at__at_script01.sql
SQL> create table yakima(

  2          snerg   number,
  3          florpal varchar2(40)

  4 );

Table created.

SQL>
SQL>
SQL> alter table yakima

  2 add constraint yakima_pk
  3 primary key(snerg);

Table altered.

SQL>
SQL> _at__at_script02.sql
SQL> create table terpal(
  2          sming varchar2(80),
  3          yertz   number,
  4          oplama  number

  5 );

Table created.

SQL>
SQL> alter table terpal
  2 add constraint terpal_pk
  3 primary key(sming);

Table altered.

SQL>
SQL> alter table terpal
  2 add constraint terpal_fk
  3 foreign key(yertz)
  4 references yakima;

Table altered.

SQL> I hope this helps.

David Fitzjarrell Received on Mon Feb 09 2009 - 07:58:08 CST