Executing a SQL file by running a batch file

Question

I am trying to put together a batch file to simply execute a .sql file I have. The code I'm using in the batch is:

REM JOB.BAT

SQLPLUS -S username/password@db @C:\Users\username\Desktop\testsql.sql


EXIT

I am getting the error:

"ERROR: ORA-12154: TNS:could not resolve the connect identifier specified"

I think the issue is that my password contains the @ symbol, so it starts reading the remote database name in the middle of the password instead of just reading 'db'. So if my password were "p@ssword", it's looking for a database called "ssword@db" which does not exist.

Is there a short way around this or is changing my password the only way to do it?

Thanks!

If you TNS isn't setup correctly you can simply call the database explicitly: — Fudztown
– Fudztown, Commented Jul 30, 2015 at 15:38
you could try putting quotes round username/password@db, but to be honest i think your best bet is to change the password — davegreen100
– davegreen100, Commented Jul 30, 2015 at 15:39
Documentation could hardly be more vague. It suggests you can quote the second part (what's aftet the @ sign) but says nothing about passwords... — Álvaro González
– Álvaro González, Commented Jul 30, 2015 at 17:34

Matthew McPeak · Accepted Answer · 2015-08-02 14:28:57Z

1

You need to quote the password string, using whatever is appropriate for your operating system. It looks like you're using Windows, so, try this:

SQLPLUS -S username/'password'@db @C:\Users\username\Desktop\testsql.sql

edited Aug 2, 2015 at 14:28

answered Jul 30, 2015 at 15:42

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

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

1 Comment

Álvaro González Over a year ago

This article seems to confirm this. I've browsed Oracle documentation many times and I've never been able to find official reference.

Fudztown · Accepted Answer · 2015-07-30 15:39:08Z

0

If you TNS isn't setup correctly you can simply call the database explicitly:

sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

answered Jul 30, 2015 at 15:39

Fudztown

3812 silver badges12 bronze badges

Collectives™ on Stack Overflow

Executing a SQL file by running a batch file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related