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
gilnariAsked:
Who is Participating?
 
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.
0
 
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]
[DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Is your customer testing the same in sqlplus or some other tool/user interface/application ?
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

 
SujithData ArchitectCommented:
@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
0
 
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.
0
 
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);
rollback;
select * from tab1;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.