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

Solution

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

Comments

[Alex Poole]

How are you running this - are you already connected to the database by this point?

[Sarath Subramanian]

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

[Alex Poole]

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]

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