3

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
Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
  • How are you running this - are you already connected to the database by this point? – Alex Poole Aug 05 '16 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 Aug 06 '16 at 08: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 Aug 06 '16 at 08:58

1 Answers1

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.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • 1
    `@Alter_table.sql`, or [`start`](https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#i1210918); not `exec`, which is shorthand for an anonymous PL/SQL block. – Alex Poole Aug 05 '16 at 17:31