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 @@ 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...3.htm#i2696759
[2]
http://download-west.oracle.com/docs...3.htm#i2498515


--
-- 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
--

On Feb 9, 7:09*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
The @@ 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 @@ 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
@@script01.sql
@@script02.sql
spool off


Executing master.sql --

SQL> @@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> @@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

ddf escribió:
I've copied your example line by line (it works fine) and started
modifying it. The issue happened to be blank lines. It's okay if I have:

create table yakima(
snerg number,
florpal varchar2(40)
);

But it won't work with:

create table yakima(
snerg number,

florpal varchar2(40)
);


SQL> create table yakima(
2 snerg number,
3
SQL> florpal varchar2(40)
SP2-0734: inicio "florpal va..." de comando desconocido - resto de la
lÝnea ignorado.
SQL> );
SP2-0042: comando desconocido ")" - resto de la lÝnea ignorado.


Can't you have blank lines in script files?


--
-- 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
--

On 9 Feb, 15:34, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
SQL*Plus isn't great on blank lines, to be honest. I tend to use
"--" (comment) if I want to space out a script.

HTH

-g

On Feb 9, 9:34*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
You can, provided you set sqlblanklines ON --

SQL> set echo on sqlblanklines on
SQL> spool master.log
SQL> @@script01.sql
SQL> create table yakima(
2 snerg number,
3
4 florpal varchar2(40)
5 );

Table created.

SQL>
SQL>
SQL> alter table yakima
2 add constraint yakima_pk
3 primary key(snerg);

Table altered.

SQL>
SQL> @@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>


David Fitzjarrell

On 9 Feb, 15:58, ddf <orat... (AT) msn (DOT) com> wrote:
<snip>
That's a new one on me. Thanks David.

-g

ddf escribió:
Bingo!

Thank you very much, you've been terribly helpful. I'm so used to having
spaces ignored by default that I would have never thought there would be
an option for it.



--
-- 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
--

On Feb 9, 8:27*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:

The glogin.sql is very useful for setting things up the way you want.

Here's the sqlplus faq: http://www.orafaq.com/wiki/SQL*Plus_FAQ

jg
--
@home.com is bogus.
http://cdnll-7.liveleak.com/s/14/med...051&amp;rs=150