DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005

    Why don't we use DDL statements with Static SQL in PL/SQL Blocks?

    I have a query regarding 'why don't we use DDL statements in
    PL/SQL block?', I know it is possible by using dynamic SQL( for example EXECUTE IMMEDIATE statement , DBMS_SQL package functions) , but I would like to know the detailed reason about 'why don't we .........?, For example
    If we use this statement in Pl/Sql block 'Create table
    Sample(col number(10))', when we compile this pl/sql block then Oracle starts parsing first(as per of parsing concept what ever object (here the example is table 'Sample') we used that object must exist in the Database, So the table 'Sample' has not yet created, So this is wrong
    My doubt is, just assume that the table 'employee' is already existed in the Database, then why don't we use 'Drop table employee' statement in pl/sql block, like this why don't we use 'Alter table ...........' statement in pl/sql block.
    Could you please give a detailed explanation about this query, I
    referred many books, but they said we can't use DDL statements in pl/sql block, to do this we must use Dynamic sql, But I would like to know that What is the reason why DDL using with Static SQL not work in pl/sql block and what will happen when Parsing has done

    I am expecting the answer from You, Reply soon please


    Amar Kambam

  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    I don't think that it is neccessarily true that DDL statements don't appear in PL/SQL blocks. Where they represent an efficient mechanism for performing some task then you'll see them. For example, truncating a table, or adding or dropping a table partition.

    So there are circumstances where DDL is appropriate, it's just that they are not common.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2005
    The simple answer is that is how Oracle has decreed that it works.
    One reason why might be what level of error checking would be implied.
    For example, if the DDL added a column to a table, or dropped an index, should the PL/SQL compile if the column already exists or if the index didn't exist ?
    Extending on from that, a PL/SQL procedure has dependencies on object referred to in static SQL. If those objects are altered, the PL/SQL needs to be recompiled. If DDL was allowed in static SQL, then executing the DDL would require the executing PL/SQL procedure to be invalidated and recompiled while it was executing. It would lock itself.

    In short, to work, static DDL would have to be treated differently from static DML (ie not result in a depencency) which is the major benefit from making it static. All you'd achieve is a simpler syntax, and they got 99% of that when they added EXECUTE IMMEDIATE in addition to DBMS_SQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.