Why don't we use DDL statements with Static SQL in PL/SQL Blocks?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Nov 2005
    Posts
    1

    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

    Thanks

    Amar Kambam

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    Posts
    158
    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