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:
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
/
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:
ReplyDeleteDECLARE
l_count INTEGER;
BEGIN
DELETE FROM plch_data;
select count(*) into l_count from plch_data;
END;
/
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?
ReplyDeletemade sense of course but the part that you're missing is those demos show us that ddls make an implicit commit.
ReplyDeleteHow do you observe the second commit?
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!
ReplyDeleteHi Steven,
ReplyDeleteA 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.
Thanks, Frank. That's very interesting and good to know. Perhaps it will even make it onto an Oracle Dev Gym quiz soon!
Delete