1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ROLL in HELP INDEX SQL 10g

Discussion in 'SQL PL/SQL' started by darshan30589, Oct 29, 2012.

  1. darshan30589

    darshan30589 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have found Problem in ROLL....
    b'coz all SQL*PLUS commands are works WITHOUT semicolon';',
    but in HELP INDEX command ROLL is not listed....
    so what exactly ROLL does...and WHY IT IT SUPPORTED WITH OUT SEMICOLON';'...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    ROLL is a shortcut for ROLLBACK; apparently it's written to include the ';' for ease of use.
     
    darshan30589 likes this.
  3. darshan30589

    darshan30589 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Difference between rollbac and rollback
    Posted on August 24, 2008

    What is the difference between rollbac and rollback?


    SQL> create table t as select 1 x from dual;

    Table created.

    SQL> update t set x=2;

    1 row updated.

    SQL> savepoint a;

    Savepoint created.

    SQL> update t set x=3;

    1 row updated.

    SQL> rollbac to savepoint a;
    Rollback complete.
    SQL> select * from t;
    X
    ----------
    1

    WTF! rollbac does not seem to work correctly ;)

    Actually, ROLL, ROLLB, ROLLBA and ROLLBAC are not valid SQL statements. However, SQLPLUS recognizes it a sqlplus statement but this behavior is not documented.


    SQL> del *
    SQL> roll
    Rollback complete.
    SQL> list
    SP2-0223: No lines in SQL buffer.

    The statement does not end with semi-column and is not stored in the SQLPLUS buffer.

    So it is not a SQL command.
     
  4. darshan30589

    darshan30589 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Every one...i got the right answer ...i have posted thanks...
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I never said it was. Also your example is outdated as rollback works just fine:

    Code (SQL):

    SQL> CREATE TABLE t AS SELECT 1 x FROM dual;
     
    TABLE created.
     
    SQL> UPDATE t SET x=2;
     
    1 ROW updated.
     
    SQL> SAVEPOINT a;
     
    SAVEPOINT created.
     
    SQL> UPDATE t SET x=3;
     
    1 ROW updated.
     
    SQL> ROLLBACK TO SAVEPOINT a;
     
    ROLLBACK complete.
     
    SQL> SELECT * FROM t;
     
    X
    ----------
    2
     
    SQL>
    I noticed you used 'rollbac' instead of 'rollback' -- 'roll' and 'rollbac' are, as I mentioned previously, shortcuts that SQL*Plus recognozes and they operate wthout additional parameters so the 'rollbac to setpoint a;' only executed 'rollbac' and rolled back the entre set of statements. Had ou used 'rollback', as I did, you would have observed the behaviour I posted.
     
    darshan30589 likes this.