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!

ISSUE with DDL statement

Discussion in 'SQL PL/SQL' started by venu57, Sep 29, 2014.

  1. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi All,

    Hope you guys doing well!!!

    I have a question on database transaction.

    First, issued a DML statement as

    INSERT INTO XYZ (number, name) VALUES (12,’TEST’);

    Then I issued the following DDL statement as

    CREATE TABE ABC
    AS
    SELECT * FROM DUAL;

    The above statement gives an error due to the syntax error.

    As per my knowledge, Oracle server issues an implicit commit before and after any DDL statement.

    So, even if your DDL statement does not execute successfully, you cannot roll back the previous statement because the server issued a commit. So, the above insert will be committed successfully.

    But here is the problem:

    I tried to disconnect the session after issuing the DDL statement, but why again it’s asking for commit or rollback the cahnges??

    Please tell me why it’s asking for commit or rollback the changes when the server issues an implicit commit for any DDL statements (even though the DDL fails)?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

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

    if the syntax error, commit doesn't occur since the kernel can't carry out the instruction ddl

    Here example :
    Code (SQL):

    prompt CREATE TABLE IS normal
    DROP TABLE test_ins purge;
    DROP TABLE test_ins2 purge;
    CREATE TABLE test_ins AS SELECT *  FROM dual;
    INSERT INTO test_ins SELECT * FROM dual CONNECT BY level <= 7;
    SELECT COUNT(*) FROM test_ins;
    prompt ora-00923
    CREATE TABLE test_ins AS SELECT *1  FROM dual;
    SELECT COUNT(*) FROM test_ins;
    INSERT INTO test_ins SELECT * FROM dual CONNECT BY level <= 7;
    prompt ora-00955
    CREATE TABLE test_ins AS SELECT *  FROM dual;
    SELECT COUNT(*) FROM test_ins;
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your first error is naming a column after a valid data type:


    Code (SQL):

    SQL> CREATE TABLE xyz(
      2          "NUMBER"        NUMBER,
      3          name            varchar2(12));
    TABLE created.
    SQL>
    SQL> INSERT INTO XYZ (NUMBER, name) VALUES (12,'TEST');
    INSERT INTO XYZ (NUMBER, name) VALUES (12,'TEST')
                     *
    ERROR at line 1:
    ORA-00928: missing SELECT keyword

    SQL>
     

    Originally the create table statement failed; enclosing the column name in "" fixed that 'problem'. Fixing the insert statement we see:


    Code (SQL):

    SQL> CREATE TABLE xyz(
      2          "NUMBER"        NUMBER,
      3          name            varchar2(12));


    TABLE created.


    SQL>
    SQL> INSERT INTO XYZ ("NUMBER", name) VALUES (12,'TEST');


    1 ROW created.


    SQL>
    SQL> CREATE TABE ABC
      2  AS
      3  SELECT * FROM DUAL;
    CREATE TABE ABC
           *
    ERROR at line 1:
    ORA-00901: invalid CREATE command

    SQL>
    SQL> EXEC dbms_lock.sleep(20);


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> CREATE TABLE abc
      2  AS
      3  SELECT * FROM dual;


    TABLE created.


    SQL>
     

    Now, from session #2, selecting from the XYZ table:


    Code (SQL):

    SQL> @cr_tbl_after_ins_ex2
    Connected.
    SQL> SELECT * FROM xyz;


    no ROWS selected


    SQL>
    SQL> EXEC dbms_lock.sleep(20)

    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM xyz;

         NUMBER NAME
    ----------- ------------
             12 TEST
    SQL>
     

    If the statement you provided was the actual statement your syntax error of using TABE rather than TABLE was the cause.
     
  4. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi David Fitzjarrell,

    Thanks for the response. Just for the Explanation purpose, i took the number as a column. Given snopshots were correct. But my question is after inserting a row into a table then if i issue a ddl statement the row gets commited successfully. Beacuse oracle implicitly provides the commit for any DDL statement.

    Suppose even if i give the incorrect DDL (syntactically wrong) then does oracle implicitly perform the commit or not is my question??

    Once again thanks for your valuable answer...
     
  5. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Sergey,

    Thanks for your quick response...

    If the DDL statement is having any syntax errors, server doesn't give any implicit commits. Am i right???
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Yes.
    Because, the instruction or the unit (pl/sql) having errors of syntax isn't executed ( and not there will be no implicit commit )
     
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I suspect the reason the first implicit commit never happens is because the statement must be parsed first before the execution steps of the DDL can take place. As the statement has a syntax error the parse errors out so the implicit commit never fires.

    CJ
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That was answered in the output for session #2; after the incorrect DDL no commit was issued resulting in 'no rows selected'.