Skip to main content

Execution of DDL in PL/SQL commits TWICE: before and after the statement

You'd think that after working with Oracle Database and PL/SQL since 1990, I'd know everything.

Ha. Not so. :-)

Of course, there are always the new features, such as those coming in 12.2 (I will be blogging about those extensively in the coming months).

But even for features that have been in the language for decades, I still encounter small gaps in my knowledge.

For example, I had long known that when you execute a DDL (data definition language) statement in PL/SQL (which must be done as dynamic SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE /EXECUTE) a commit is executed implicitly after the statement.

What I'd somehow missed was that a commit is also performed before the DDL statement is executed. So that is the point of this post:
Oracle Database issues a commit before a DDL statement is executed, and then afterwards as well.
You can see this behavior in action in the script below, which is generated from a recent PL/SQL Challenge quiz and can be run directly in LiveSQL.  All four blocks result in "Count=0" being displayed, because the effect of the DELETE is committed before the DDL statement is executed. Therefore, even when an error is raised when the DDL statement is run, the data's gone.

CREATE TABLE plch_data (n NUMBER)
/

BEGIN
   INSERT INTO plch_data
        VALUES (100);

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE plch_show_count
IS
   l_count   INTEGER;
BEGIN
   SELECT COUNT (*) INTO l_count FROM plch_data;

   DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data (n number)';

   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      plch_show_count;
END;
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data2 (n number)';

   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      plch_show_count;
END;
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data (n number)';

   ROLLBACK;
   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      plch_show_count;
END;
/

DROP TABLE plch_data2
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data2 (n number)';

   ROLLBACK;
   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      plch_show_count;
END;
/

DROP TABLE plch_data2
/

DROP TABLE plch_data
/

DROP PROCEDURE plch_show_count
/

Comments

  1. Hi, is it ddl committing or you do not need to commit to see that you have no rows, because you're in the same session:



    DECLARE
    l_count INTEGER;
    BEGIN
    DELETE FROM plch_data;
    select count(*) into l_count from plch_data;
    END;
    /

    ReplyDelete
  2. Mustafa, what you say is correct: you can immediately see the effects of your changes within your own session. No one ELSE can see those changes until you commit. So the interesting part of the script in this post is that even after you rollback in your session, the changes have been saved - if a DDL statement was executed "in between". Make sense?

    ReplyDelete
  3. made sense of course but the part that you're missing is those demos show us that ddls make an implicit commit.
    How do you observe the second commit?

    ReplyDelete
  4. Huh. Good point. I suppose the impact of the second commit is seen by the results of your DDL statement being visible in the data dictionary!

    ReplyDelete
  5. Hi Steven,

    A reader of your blog has posted an interresting case in french forum where commit didn't occur when the DDL has syntax error.
    However, it occurs when syntax is ok but semantic parse raise error. I've put my test on livesql

    First is syntax error (table name is a reserved word) -> no commit
    Second is semantic error (table name already exists) -> commit

    Regards,
    Franck.

    ReplyDelete
    Replies
    1. Thanks, Frank. That's very interesting and good to know. Perhaps it will even make it onto an Oracle Dev Gym quiz soon!

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel