1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can we use DDL operation in PL/SQL block

Discussion in 'SQL PL/SQL' started by Puru, Oct 25, 2010.

  1. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Dear All,

    Can we create table or drop table by using PL/SQL block like

    Begin

    create table student (Stuno number(20), stuname varchar2(20));

    End
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Yes Puru,
    we can achieve that by using dynamic SQL. DDL commands are not allowed as PL/SQL constructs in PL/SQL blocks. Using DBMS_SQL or EXECUTE IMMEDIATE, we can execute create table, drop, alter, analyze, truncate and other DDL's too.

    Check the below example

    Code (SQL):
    SQL> SELECT * FROM DDL_DEMO;
    SELECT * FROM DDL_DEMO
                  *
    ERROR at line 1:
    ORA-00942: TABLE OR VIEW does NOT exist


    SQL>  DECLARE
      2   L_SQL VARCHAR2(4000) := 'CREATE TABLE DDL_DEMO (A NUMBER, B VARCHAR2(10))';
      3   BEGIN
      4   EXECUTE IMMEDIATE L_SQL;
      5   END;
      6  /

    PL/SQL PROCEDURE successfully completed.

    SQL> SELECT * FROM DDL_DEMO;

    no ROWS selected
     
  3. balu22777

    balu22777 Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    65
    you can create/drop tables in PL/SQL block with Execute immediate command
    actually any programming language follows pre/post validations for host variable
    here pre checking wl be done,so at the time of compilation we have to submit all host variables,but we can't with ddl stms,so where ever we keep Execute immediate stmt that stmt wl be validate at runtime only.
    this is nothing but Dynamic SQL.previously we are using in-built pakage ,now execute immediate command .
     
    Sadik likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is the same thing SBH posted without the nicely coded example.
     
  5. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Zargon,
    can u plz let me know mistakes in my example in the above post !! Plz show me with an example too

    thnx
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why would you think there are mistakes in your example? It's compact and proves the point quite nicely.
     
  7. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Zargon, as you commented on my last post, "without the nicely coded example", thats why I thought that you found any mistake !! Its oK anyways :)
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You'd have found an error long before I would have (hopefully). The comment was genuine and had no hint of sarcasm in it. It's a nicely coded example.