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
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.
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.