• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 996
  • Last Modified:

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 ;
0
Gaetor
Asked:
Gaetor
  • 5
  • 2
1 Solution
 
sujith80Commented:
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
sujith80Commented:
good catch.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now