Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've initially posted this question on serverfault.com, but because lack of answers decided to post it here...

I'm trying to use bat file/CLI to run SQL script against certain Oracle database with following syntax:

sqlplus -L user_name/database_name@Password @d:\my_bat_file\sql_script.sql

If I run this following error is returned:

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

SP2-0751: Unable to connect to Oracle. Exitig SQL*Plus

At the same time tnsping database_name works fine & corresponding connection configuration strings are present in tnsnames.ora

Moreover if I type commands manually in a following way:

sqlplus /nolog
connect user_name@databasename
Passowrd

I can connect to the database too. Any idea why I'm getting error when trying to use command I mentioned in beginning & how to fix it?

P.S. I'm doing this from Windows Server 2003 SP2 box with Oracle client v10 installed.

share|improve this question

closed as off-topic by Max Vernon, Paul White, Jack Douglas Jun 3 at 7:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Paul White, Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

3  
I hope you mean user_name/password@tnsalias instead of user_name/database_name@Password –  Munchi Oct 29 '13 at 11:54
    
Well - you draw my attention to the right thing... Kind of a stupid mistake. Indeed problem was because I've used username/thsalias@password instead of username/password@tnsalias. Just copied this syntax from previously used batch file - and because it is test environment with the same values for password & tnsalias in most cases except this new database I tried to connect to this tame it was overlooked by me. –  Mikhail Oct 29 '13 at 12:09
    
Can happen to everyone ;-), that's why we are here :D –  Munchi Oct 29 '13 at 12:31

2 Answers 2

As Munchi pointed out the issue was caused by wrong syntax. I used:

sqlplus -L user_name/tns_alias@Password @d:\my_bat_file\sql_script.sql

Instead of:

sqlplus -L user_name/Password@tns_alias @d:\my_bat_file\sql_script.sql
share|improve this answer

Try to set ORACLE_SID inside of a script, and then try with sqlplus command.

set ORACLE_SID=testdb

sqlplus -l....
share|improve this answer
2  
Wouldn't matter since he is using a tnsalias. –  Munchi Oct 29 '13 at 11:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.