Executing sql file from another sql file

Question

I have a .sql file with name Alter_table.sql which have the following code.

alter table mytable add newcolumn VARCHAR2(1);

I don't want to edit this file and add a spool command. However I need to execute Alter_table.sql by writing spool in another file (execute_sql.sql) which should look like the below. I am not sure of the correct syntax. Can anyone please help here?

SET SERVEROUTPUT ON
SET DEFINE OFF
SPOOL Alter_Table_STD_SOURCE.log

EXEC username/password@database `Alter_table.sql`

SPOOL OFF;
SET DEFINE ON
SET SERVEROUTPUT OFF

How are you running this - are you already connected to the database by this point? — Alex Poole
– Alex Poole, Commented Aug 5, 2016 at 17:34
I use command prompt, by which I first direct to that path (for eg c:\Scripts). Then I will give the commend sqlplus username/password@database. Then @execute_sql.sql — Sarath Subramanian
– Sarath Subramanian, Commented Aug 6, 2016 at 8:36
OK, then you are already connected, and you just need the @Alter_table.sql part of Gary's answer (instead of exec), without the explicit connect. — Alex Poole
– Alex Poole, Commented Aug 6, 2016 at 8:58

Gary_W · Accepted Answer · 2016-08-05 17:37:03Z

3

(Thanks to Alex Poole) :-)

You need to connect first, then run your .sql file in the wrapper script using the '@' sign or 'START' commands:

...
-- Connect if not already connected.
CONNECT username/password@database

@Alter_table.sql

...

I'm not sure its a good idea to keep login/password in a file but you need to take security into account.

edited Aug 5, 2016 at 17:37

answered Aug 5, 2016 at 17:29

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

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

1 Comment

Alex Poole Over a year ago

@Alter_table.sql, or start; not exec, which is shorthand for an anonymous PL/SQL block.

Collectives™ on Stack Overflow

Executing sql file from another sql file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related