How to create multiple sqlplus tables using one SQL script file

Question

I am creating a whole bunch of tables in SQL*Plus and using PsPad to write SQL script that running the script through sqlplus in putty.

Currently I have to create an individual SQL script file for each table but I would like to create all of my tables in one SQL script file. Is this possible?

I have tried

CREATE TABLE TESTTABLE4(
ID NUMBER(2),NAME VARCHAR2 (50));
CREATE TABLE TESTTABLE2(
ID_NO NUMBER (2), SIZE NUMBER (4));

And also leaving a line between the tables

What you posted should work. Do you get an error? Is that the complete script? You need provide far more detail than that. — user330315
– user330315, Commented Oct 12, 2012 at 11:50
It makes the first table but not the second. I get the error "ERROR at line 2: ORA-00904: : invalid identifier" — whatahitson
– whatahitson, Commented Oct 12, 2012 at 11:53

BulentB · Accepted Answer · 2012-10-12 12:21:35Z

2

SIZE is a keyword if you want to create a column named SIZE you should put it between "".

CREATE TABLE TESTTABLE2(ID_NO NUMBER (2), "SIZE" NUMBER (4));

As you can see in the comments i should have said that you can do this but it doesn't mean that you should.Thanks for for clarification fellas.

edited Oct 12, 2012 at 12:21

answered Oct 12, 2012 at 12:07

BulentB

3161 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Poole Over a year ago

Yes, but don't do that anyway, since you'll have to always quote it wherever it's referenced in the future. There's a list of reserved words; note the first paragraph, and avoid using any of them for object names if you can.

Rob van Laarhoven Over a year ago

SIZE is the problem but please don't use quoted identifiers for your column / table names. A lot of tools don't fully support quoted identifiers. Call the column <entity name>size or something. create table test (" " varchar2(10),"ORA-00942 Table or view does not exist" integer);

Diego · Accepted Answer · 2012-10-12 11:43:03Z

0

Take a look at anonymous PL/SQL blocks which can contain multiple statements.

answered Oct 12, 2012 at 11:43

Diego

36.5k21 gold badges94 silver badges138 bronze badges

1 Comment

Alex Poole Over a year ago

But not DDL unless you use dynamic SQL.

Collectives™ on Stack Overflow

How to create multiple sqlplus tables using one SQL script file

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related