tag:blogger.com,1999:blog-7849367040589270673.post409316957051154688..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Execution of DDL in PL/SQL commits TWICE: before and after the statementSteven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-7849367040589270673.post-7865827351388478852017-03-27T13:12:46.637-07:002017-03-27T13:12:46.637-07:00Thanks, Frank. That's very interesting and goo...Thanks, Frank. That's very interesting and good to know. Perhaps it will even make it onto an Oracle Dev Gym quiz soon! Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-74853124363849422912017-03-27T12:14:09.982-07:002017-03-27T12:14:09.982-07:00Hi Steven,
A reader of your blog has posted an in...Hi Steven,<br /><br />A reader of your blog has posted an interresting case in <a href="https://www.developpez.net/forums/d1669894/bases-donnees/oracle/administration/commit-ddl-seul-commit" rel="nofollow">french forum</a> where commit didn't occur when the DDL has syntax error. <br />However, it occurs when syntax is ok but semantic parse raise error. I've put my test on <a href="https://livesql.oracle.com/apex/livesql/s/erouareph8sul76r0fya9pc2x" rel="nofollow">livesql</a><br /><br />First is syntax error (table name is a reserved word) -> no commit<br />Second is semantic error (table name already exists) -> commit<br /><br />Regards,<br />Franck.@FranckPachothttps://blog.dbi-services.com/author/franck-pachot/noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-52963619343612868562016-10-31T08:24:34.164-07:002016-10-31T08:24:34.164-07:00Huh. Good point. I suppose the impact of the secon...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!Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-11478209831719415142016-10-31T07:04:12.749-07:002016-10-31T07:04:12.749-07:00made sense of course but the part that you're ...made sense of course but the part that you're missing is those demos show us that ddls make an implicit commit.<br />How do you observe the second commit?<br />Mustafa DOĞANAYhttps://www.blogger.com/profile/05645488077841804038noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-73746950351972040692016-10-31T06:42:29.622-07:002016-10-31T06:42:29.622-07:00Mustafa, what you say is correct: you can immediat...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?Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-74185874961588982512016-10-31T05:09:10.281-07:002016-10-31T05:09:10.281-07:00Hi, is it ddl committing or you do not need to com...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:<br /><br /><br /><br />DECLARE<br /> l_count INTEGER;<br />BEGIN<br /> DELETE FROM plch_data;<br /> select count(*) into l_count from plch_data;<br />END;<br />/<br />Mustafa DOĞANAYhttps://www.blogger.com/profile/05645488077841804038noreply@blogger.com