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!

transaction- add column to existing table

Discussion in 'SQL PL/SQL' started by monkey, Apr 24, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    hello!

    Tried this as transaction:
    Code (Text):


    begin
    savepoint sp1;

    alter table m
    add ( nick varchar2(10));

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
     ROLLBACK TO SP1;

    raise_application_error(-20100, SQLERRM);
    END;
    /
     

    and got errror:
    Error report:
    ORA-06550: line 4, column 1:
    PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

    ( begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << continue close current delete fetch lock
    insert open rollback savepoint set sql execute commit forall
    merge pipe purge
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:


    But here is the template I tzried to follow:

    http://www.java2s.com/Tutorial/Oracle/0120__Table/Filldataintonewaddedtablecolumn.htm

    I really do not see any mistake.
    (in my case I need no restrictionsl like male/female in the example on net).

    If someone sees what is the point , woudl be grafetull!

    many thanks!!!
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    use this....

    begin
    savepoint sp1;

    EXECUTE IMMEDIATE 'alter table m add ( nick varchar2(10))';

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK TO SP1;

    raise_application_error(-20100, SQLERRM);
    END;
     
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Did this:
    Code (SQL):
    BEGIN
    SAVEPOINT sp1;

    ALTER TABLE m
    EXECUTE immediate ADD (age INTEGER);

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
     ROLLBACK TO SP1;

    raise_application_error(-20100, SQLERRM);
    END;
    /
     
    and got this:
    Code (SQL):


    Error starting at line 1 IN command:
    BEGIN
    SAVEPOINT sp1;

    ALTER TABLE m
    EXECUTE immediate ADD (age INTEGER);

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
     ROLLBACK TO SP1;

    raise_application_error(-20100, SQLERRM);
    END;
    Error report:
    ORA-06550: line 4, COLUMN 1:
    PLS-00103: Encountered the symbol "ALTER" WHEN expecting one OF the following:

       ( BEGIN CASE DECLARE END exception exit FOR GOTO IF loop MOD
       NULL pragma raise RETURN SELECT UPDATE while WITH
       <an identifier> <a double-quoted delimited-identifier>
       <a bind variable> << continue close CURRENT DELETE fetch LOCK
       INSERT OPEN ROLLBACK SAVEPOINT SET SQL EXECUTE commit forall
       MERGE pipe purge
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
    Really confused now...
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Why don't you copy and paste my query?
     
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    if I only saw " ' " sign...
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    No,

    You wrote

    But I wrote


    EXECUTE immediate 'ALTER TABLE m ADD (age INTEGER)';
     
  7. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Now, I surely wished to see this error:

    "raise_application_error(-20100, SQLERRM); to be written...

    I tried with
    Code (SQL):

    UPDATE m
    SET age=10
    WHERE idm=7;
    in another sql file, but got only:" 0 rows updated"!!!

    What do I have to do to achieve error -20100 to be written on the screen???

    Many thanks!!!
     
  8. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    p.s. my table is:

    idm mname msurname age
    1 aa AAA -
    2 bb BBB -
    3 cc CCC -


    and i tried so there would be no option to imput any age where idme is 7...but I see "ordinary" error, not recognising the transaction, not the error that is porbably ment to be written ...-20100...
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    I don't know what u r dng?

    I didn't get ur exact requirement
     
  10. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I want that error form exception (sqlerrm9 to be activated== written.

    So, I try to acieve TO MAKE AN EROR!!!

    As there are just (idm)s from 1 to 3, tryint to update age in row 7 should cause an error then...shoulnt't it???

    (It is my first contactz with this stuff, no tempaltes, no examples, that is because I ask)
    Many thanks folks!!!

    (EXCEPTION
    WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    END;)-> THAT is what I want to achieve, SQLERRM to be outwriten
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    There is no error in ur update stmt. there is no data in ur table for that idm.
     
  12. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Table m is still existing!
    When I slick on tables I can open it.

    I made it before I made this transaction.

    idm mname msurname
    1 aa AAA
    2 bb BBB
    3 cc CCC

    transactions we made together are adding nick(first one) and age column (second one). Both of them are working. I get new column(s) in the table.

    Then I dropped the both columns and tried to make it INCORRECT, so that it writes error:
    SQLERRM

    Not the probeom is I am not able to make an error:(

    I made a transaction which works.
    Now I still want to make one which WOULD NOT...
    (everything on this world...;) )
     
  13. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    If there is no desire to start working with oracle documentation, then look here for these books and start studying, i.e. construct the training activity - effectively:

    Beginning Oracle SQL
    Oracle PL/SQL Programming ( 5TH Edition ) Steven Feuerstein
    Oracle PL/SQL Programming ( 6TH Edition ) Steven Feuerstein for 12c
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If Oracle can't find the key requested it returns '0 rows updated.'; it is not an error.


    You need to understand what IS an error so you can get your error text (which is not the best error text to generate) to display. I have written several blog posts on generating error messages from PL/SQL:


    http://dfitzjarrell.wordpress.com/2008/03/04/what-was-that-masked-message/
    http://dfitzjarrell.wordpress.com/2008/05/02/ive-never-seen-that-error-before/
    http://dfitzjarrell.wordpress.com/2012/06/26/nullified-remains/


    You should read them to see what errors CAN fall into the 'when others then' umbrella and how to handle them and produce a usable error message.