ACCEPT SQL*PLUS not waiting for user response

Posted on 2012-09-17
Last Modified: 2012-09-18
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
Question by:gilnari
    LVL 76

    Accepted Solution

    >>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.
    LVL 23

    Expert Comment

    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.
    LVL 28

    Expert Comment

    Is your customer testing the same in sqlplus or some other tool/user interface/application ?
    LVL 27

    Expert Comment

    @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

    Author Comment

    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now