On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,620 Users
  • 2,269,768 Discussions
  • 7,916,797 Comments

Discussions

DDL execution from PL/SQL directly

Nimish Garg
Nimish Garg Sr. Database Developer @ GartnerNoida, IndiaMember Posts: 3,185 Gold Trophy
edited Jan 11, 2016 5:41PM in Database Ideas - Ideas

Executing DDLs from PL/SQL is always been pain and need to use execute immediate for that.

I wish if we can directly execute ddls from PL/SQL that would be a helpful feature.

Nimish GargUser_6XD9J
9 votes

Active · Last Updated

Comments

  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    Seriously, any DDL at all?

  • Nimish Garg
    Nimish Garg Sr. Database Developer @ Gartner Noida, IndiaMember Posts: 3,185 Gold Trophy
    edited Jan 10, 2016 12:03AM

    Seriously, any DDL at all?

    What ddls should be allowed is the question. I think CTAS and way to copy index and constraints are some basic ones. If not ddl, there should be some direct way.

    One basic scenario I can think of is On Oracle SE (partitions are not available) we sometimes need to archive old data in a dynamically created tables with same indexes and constraints.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Is execute immediate really so painful?

    So for example:

    BEGIN

      FOR item IN ( SELECT OWNER, table_name FROM dba_tables

      where tablespace_name='USERS' AND table_name='REDO') LOOP

        stmnt := 'alter table ' || item.owner || '.' || item.table_name ||' move tablespace DATA4PIMPS';

        execute immediate stmnt;

      END LOOP;

    END;

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    I would like to do TRUNCATE directly in plsql. All other DDL? No need for that.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    You can already do this.    Use EXECUTE IMMEDIATE. 

  • MKJ10930279
    MKJ10930279 Kolkata, IndiaMember Posts: 244

    not convinced with the idea. you need only a single statement to dynamically perform DDL's.

  • Jon Theriault
    Jon Theriault Raleigh, NC, USAMember Posts: 16 Blue Ribbon
    edited May 31, 2017 10:37AM

    You're asking a lot from the compiler.

    For instance consider the following function that the compiler will actually mark as invalid as it's running (after the name column is dropped).  How do you want to handle this?  Should it give an error since it should "know" the column isn't going to be there?

    create function dummy_count  

         return integer

         as  

              dummy_count_l integer;

         begin  

              alter table users drop column name;    

              select count ( * )   into dummy_count_l  

              from users  

              where users.name = 'Bob';    

              return dummy_count_l;

    end dummy_count;

    The tricky thing is most things with DDL are about creating/removing things the compiler will actually need to verify.  For instance you are hard coding drop a column but once it's dropped what are the odds we ever need to run that line of code again?  If we're constantly adding a removing columns there's a design issue in the application.

    Sven W.