Running a Command File in SQL*Plus from the command line

I'm trying to run a command file via SQL*plus from the command line - it almost works seamlessly but when sql*Plus loads it sits and waits - if I then press enter it executes the commands in the command file - can someone please tell me how to prevent it waiting for enter to be pressed?

command line is plus.exe username/password@connectString @commandfile.sql

contents of commandfile.sql =

SELECT per_id
        ,supplied_surname
        ,surname
        ,forenames
    FROM  tablename ;
GaetorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Do you have this script in a batch file? OR are you running it directly from command line?

Try to include the following line in your sql file before the SELECT

set pause off
0
GaetorAuthor Commented:
Thanks for your reply - I intend to put it in a batch file but at present am running it directly from command line. Tried SET PAUSE OFF - but this did not help.
0
GaetorAuthor Commented:
it's something to do with the oracle user account I'm using, if I connect using another user account it does not pause for a carriage return ?!? - Presumably a privilege of some sort. The user account that pauses for a carriage return has privs on all the relevant tables, I'm trying to track down the operative issue now.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GaetorAuthor Commented:
it was the 'connect' priv, the user account I was using had the 'create session' priv but not the connect priv.  That is what appears to cause this - not exactly intuitive - one is 'connected' and one can query the db. Granting the connect priv means no more pause for a carriage return.
I don't have time to research the details of this now but would like to know why lack of the connect priv causes this behavior ie the user can connect and query the db but with 'odd behavior' from SQL*plus.
I'd be happy to award the points for this question to anyone who can explain this.
I tried logging into SQL*Plus interactively with a user account that has no connect priv. this helps explains what is happening in batch mode ... the SQL*plus prompt does not appear, but any attempt to query data from tables to which the user has privs will work.
0
GaetorAuthor Commented:
just realised my prev posts are wrong - Connect is a role not a priv. I'll post updated info as & when for info  but the problem is solved.
0
GaetorAuthor Commented:
With some digging I located that the Customer had added an entry to login.sql to display the instance name as the PL*SQL prompt, this had the effect of stalling the login of any user account that did not have select privs on the v$database view! This is what was causing the script to stall until enter was pressed! Total red herring!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SujithData ArchitectCommented:
good catch.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.