Home » SQL & PL/SQL » SQL & PL/SQL » SQL Script
SQL Script [message #198520] Tue, 17 October 2006 08:41 Go to next message
collis
Messages: 11
Registered: October 2006
Junior Member
Hello all,

I apologize if this is a stupid question or if this has been asked / answered before - I've run through the forum and didn't see this addressed.

My question should be fairly simple: how do you have a script for multiple SQL statements?

I've got lots of PL/SQL scripts, but I just need one to create a number of tables

create table example1 (
	id   number primary key,
   	name varchar2( 30 ) null
);

create table example2 (
	id      number primary key,
	abbr    varchar2(  6 ) null,
	name    varchar2( 30 ) null
);


If I run the code exactly as it is above, I get the following error:

ORA-00911: invalid character


which is complaining about the ;'s at then end of each statement. If I remove them, I get:

ORA-00922: missing or invalid option


The sql statements are in a file 'init.sql' and I am trying to run them from SQL*Plus with 'get init.sql' and 'run;'.

Thanks for any help in advance
Re: SQL Script [message #198525 is a reply to message #198520] Tue, 17 October 2006 09:00 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The sql file should only contain one SQL statement. Why not just run it as @init.sql.
Re: SQL Script [message #198529 is a reply to message #198520] Tue, 17 October 2006 09:09 Go to previous messageGo to next message
renafi
Messages: 42
Registered: October 2006
Member
Try replacing the ; for /

Like this:

create table example1 (
id number primary key,
name varchar2( 30 ) null
)
/
create table example2 (
id number primary key,
abbr varchar2( 6 ) null,
name varchar2( 30 ) null
)
/
Re: SQL Script [message #198531 is a reply to message #198525] Tue, 17 October 2006 09:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
How do you call the script?

We have hundreds of scripts with multiple commands which run without problems.

Your Example :

test.sql :
create table example1 (
	id   number primary key,
   	name varchar2( 30 ) null
);

create table example2 (
	id      number primary key,
	abbr    varchar2(  6 ) null,
	name    varchar2( 30 ) null
);


can be called and runs without problems :

U:\>sqlplus test/test@test @test.sql

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Oct 17 16:05:02 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production


Table created.


Table created.

SQL>


So is there some additional stuff in your real script ?
Re: SQL Script [message #198532 is a reply to message #198531] Tue, 17 October 2006 09:14 Go to previous messageGo to next message
collis
Messages: 11
Registered: October 2006
Junior Member
Okay thanks everyone. Yeah I just didn't realize I could run
@init.sql

Just had to add the /'s as suggested.
Thanks a lot, I appreciate it.
Re: SQL Script [message #198533 is a reply to message #198532] Tue, 17 October 2006 09:18 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Just for future reference, the ';' do not impose a problem for this particular script. You could have left those in place.

The problem was you were issuing 'get init.sql' first and then 'run'.

[Updated on: Tue, 17 October 2006 09:27]

Report message to a moderator

Re: SQL Script [message #198540 is a reply to message #198533] Tue, 17 October 2006 09:37 Go to previous messageGo to next message
collis
Messages: 11
Registered: October 2006
Junior Member
If I leave the ;'s in, and also the /'s, I get one of the following errors for each create table statement:

ORA-00955: name is already used by an existing objec


But if I remove one or the other, it works fine - is that right?

Also, you said my problem was that I was doing get and then run. Is that syntax still okay for my pl/sql scripts ( thats how I normally run them )?

[Updated on: Tue, 17 October 2006 09:38]

Report message to a moderator

Re: SQL Script [message #198542 is a reply to message #198540] Tue, 17 October 2006 09:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Use one or the other. Either use ';' or '/', but not both. I was just informing you that you could have left the script as you originally had it with the ';' and NOT changed them to '/'.

[Updated on: Tue, 17 October 2006 09:41]

Report message to a moderator

Re: SQL Script [message #198811 is a reply to message #198542] Wed, 18 October 2006 11:27 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just an addition to the whole thing: when you ran the script using "@init.sql" and it had both semicolon and a slash sign in it, this is what happened: CREATE TABLE statement was executed (at the moment it reached semicolon sign which terminates the statement), while slash re-executed it once again. It, of course, caused an error message to appear as the table was already created.

And yes, it means that slash can be used anytime to re-run previous SQL statement. Try it and see for yourself; can be useful sometimes (especially in SQL*Plus which won't allow you to recall the whole multi-line statement by pressing the UP key on the keyboard).
Previous Topic: Comparing variables vs table records
Next Topic: data uploading problem
Goto Forum:
  


Current Time: Sat Dec 03 04:12:05 CST 2016

Total time taken to generate the page: 0.09133 seconds