VF=>user  ID=>    Login Feedback FAQ Blog
1,064,669 quizzes played | 2,953 active players

Actions


  On Executing DDL statements with EXECUTE_IMMEDIATE: Transaction impact of DDL execution (Oracle PL/SQL)      Add to Favorites

Summary

When you parse (and execute) a DDL statement in PL/SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE, the PL/SQL engine performs a commit both before and after the statement is executed.

Details

Assumes Oracle Database 10g Release 2 or higher
Intermediate Quiz, IDs: 18886/1815528/1184094

Reviewer(s): Vitaliy Lyanchevskiy

The Question

I execute these statements:

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;
/

Which of the choices display "Count=0" after execution?

The Choices [↑]

Explanation of Result Icons
Choice 1 (33743)
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;
/

I remove all rows, then try to create a table that's already been created. That raises an exception, but does not reverse the impact of the delete. In fact, that deletion has been committed.




68%
Choice 2 (33744)
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;
/

I remove all rows, then create a second table. All works according to plan, the delete is committed, the table is empty.




86%
Choice 3 (33745)
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;
/

I try to create a table that already exists, so an exception is raised. In the exception handler, I rollback all changes, but that doesn't matter because the PL/SQL engine issued a commit before it tried to execute the CREATE TABLE statement




38%
Choice 4 (33746)
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;
/

Now I rollback before showing the count, but that doesn't matter because the CREATE TABLE statement caused a commit.




53%

Answer [↑]

Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.

PL/SQL provides two ways to write dynamic SQL:

  • Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements

  • DBMS_SQL package, an API for building, running, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package.


Native dynamic SQL processes most dynamic SQL statements with the EXECUTE IMMEDIATE statement. The "native" in the name refers to the fact that you execute statements native to the PL/SQL language, rather than making calls to the DBMS_SQL supplied package.

If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you these choices:

  • Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.

  • Use the OPEN FOR, FETCH, and CLOSE statements.

The SQL cursor attributes work the same way after native dynamic SQL INSERT, UPDATE, DELETE, and single-row SELECT statements as they do for their static SQL counterparts.

Here is an example of using native dynamic SQL to create a table:

BEGIN
   EXECUTE IMMEDIATE 'create table my_data (n number)';
END;

When you parse (and execute) a DDL statement in PL/SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE, the PL/SQL engine performs a commit both before and after the statement is executed.


Statistics and Rankings [↑]

Rank Percentile Score Seconds% Correct
Median
78
50
253
741
100
Best
1
100
493
20
100

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation