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

ACCEPT SQL*PLUS not waiting for user response

Simple little bit of code that works for me in Oracle SQL PLUS but not for my customer.  Best I can figure its not waiting for a response

Can anyone help me in figuring out how I make it so the code does pause and waits for that response.  Here is the snippet of code.  There is a whole bunch of create tables ahead of this but this is where we seem to have an error.

 PROMPT Did the Startup scripts complete successfully (without errors)?

ACCEPT cmt_rlbk PROMPT "If yes, Enter COMMIT.  Otherwise, enter ROLLBACK (no semicolon): "
EXECUTE &cmt_rlbk
1 Solution
slightwv (䄆 Netminder) Commented:
>>in Oracle SQL PLUS but not for my customer.  Best I can figure its not waiting for a response

Are you sure this is where the error is?

The way you have it, it sort of has to wait for a response.  Are the database versions the same?

What version is the customer running?

>>There is a whole bunch of create tables ahead of this but this is where we seem to have an error.

You realize you cannot rollback DDL.
DavidSenior Oracle Database AdministratorCommented:
To debug, always deconstruct back to the basics. You and the user should be able to start a SQLPlus session and enter the identical statement, with identical results.  If the results differ, then correct the root cause.

The command syntax is:
ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FORMAT format]

So as an example:
ACC somevar CHAR PROMPT 'Enter Y'
DEFINE somevar

Notice I'm using single rather than double quotes; shouldn't make a difference.
Naveen KumarProduction Manager / Application Support ManagerCommented:
Is your customer testing the same in sqlplus or some other tool/user interface/application ?
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

@gilnari - is this code being run as a script in sqlplus?

i.e. using the "@scriptname" syntax?

If not, it has to be, in order for the prompt
Else - can you give the result of the following command from sqlplus?

sho all
gilnariAuthor Commented:
Both me and client are in the same enviroment.  It does run as a script  or it suppose to be unless the person executing is not telling me something.  The only exception to that is the client is executing on a different server as I am limited to just the development server.  I was able to execute the code with no issues and I have used this same code at other clients.

I am just wondering if the person executing is sitting there with the statement from the Accept line not realizing they have to type something. :(  and no message box is going to pop up.  

I think that in interest of time and per the statement "can't rollback DDL" we are going for the straight commit.
slightwv (䄆 Netminder) Commented:
>>we are going for the straight commit.

There is no need to commit DDL.  As soon as you execute a DDL statement Oracle does an implicit commit for you.

You can see this with a simple test:

create table tab1(col1 char(1));
insert into tab1 values('a');
create index tab1_idx on tab1(col1);
select * from tab1;

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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