Execute sql script in batch file with sqlplus command

Question

I want to execute a PL/SQL script from a batch file. After some research I wrote this:

sqlplus.exe %user%/%pwd%@%db% @c:\users\NDL\SQL\MHUHMS.sql %%l

where user, pwd and db are set before. But here I have to execute the script MHUHMS.sql with one parameter. And it seems that doesn't work; it's giving me an ORA-01017 error and then asking me the user and password. When I give the same values as I used in the batch file it doesn't execute my script.

I've based this on a sqlcmd version:

SQLCMD -S touoracst01 -U %user% -P %pwd% -d %db% -v l ="%%l" -i c:\users\NDL\SQL\MHUHMS.sql

But there are some questions:

Don't I have to pass the parameter -S touoracst01 ?
Is it the right conversion ? Or have I done something wrong ?

I dont' want to modify the SQL script, so I'm hoping the problem is only in the batch file.

EDIT : I modify my sqlplus command like this

sqlplus -L %user%/%pwd%@%db% < "c:\Hardis\NDL\SQL\MHUHMS.sql" %%l

I'm connect ! This part is ok, but it seems that my call of the pl/sql script is not good, with the parameters %%l. there is my loop : http://puu.sh/l1Kg2/f66d9c2aa7.png If someone can help me. Because the calling with parameters seems good....

Sounds like those % variables aren't set, or aren't valid; how are you checking? Do you get an error before it prompts you? Do you still get prompted if you use the -l flag? — Alex Poole
– Alex Poole, Commented Oct 28, 2015 at 16:25
I set this %variable before, at the beginning of the script. Yes it say to me that the logon was not recognize. but after i write the same user and password and it's works. I don't understand your last question, sorry i'm not an expert in batch file. Where i have to put the -l option ? — S. Guillaume
– S. Guillaume, Commented Oct 28, 2015 at 16:27
What is the exact error message? ORA-01017, ORA-12154, something else? Are you including the @db part when you try the second time? Would be useful to add exactly what you see and do to the question. — Alex Poole
– Alex Poole, Commented Oct 28, 2015 at 16:29
I add text and pictures.I've got the ORA-01017 error like you said — S. Guillaume
– S. Guillaume, Commented Oct 28, 2015 at 16:33

S. Guillaume · Accepted Answer · 2015-11-02 08:57:17Z

1

after a lot of research i found this :

sqlplus -L %user%/%pwd%@%db% @C:\Hardis\NDL\SQL\MHUHMS.sql "%%l"

And the %%l is a variable of my loop. Thank's all for your help !

edited Nov 2, 2015 at 8:57

answered Oct 30, 2015 at 8:40

S. Guillaume

671 gold badge3 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Execute sql script in batch file with sqlplus command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related