Home » SQL & PL/SQL » SQL & PL/SQL » Why oracle doesn’t allow direct DDL inside the proc?
Why oracle doesn’t allow direct DDL inside the proc? [message #184335] Wed, 26 July 2006 03:48 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
Can anyone tell me

why we can not use DDL operation directly inside the procedure?
But we can use the same with the help of execute immediate.

see the example below

procedure proc1
is
begin
create table emp( empid number);
end;

the above proc is invalid.

But…
procedure proc1
is
begin
execute immediate 'create table emp( empid number)';
end;

this is valid.

as we know ddl statement involve implicit commit, so we cannot give inside the procedure. however, why it is accepting the same ddl statement while using inside the execute statement. To be more simple why does oracle need such difference??

I hope the question is clear…
Re: Why oracle doesn’t allow direct DDL inside the proc? [message #184343 is a reply to message #184335] Wed, 26 July 2006 04:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's nothing to do with the implicit commit - otherwise the commit statement would be a bit of a no-no too.

At the end of the day, the answer is 'Because Oracle chose not to'.
There are very few situations when you should do DDL from inside Pl/Sql. It happens a lot, but its usually down to poor design, or just a desire to use Execute Immediate to avoid having to think too hard.

Execute Immediate is a relatively recent addition to pl/Sql to provide more flexibility in certain circumstances.

[edited to remove psycological insight Cool ]

[Updated on: Wed, 26 July 2006 06:12]

Report message to a moderator

Re: Why oracle doesn’t allow direct DDL inside the proc? [message #184359 is a reply to message #184343] Wed, 26 July 2006 06:00 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

poos design

Freudian slip?
Re: Why oracle doesn’t allow direct DDL inside the proc? [message #184769 is a reply to message #184335] Thu, 27 July 2006 20:31 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member


Procedures in Oracle are designed to perform desired operations related to data in the database or database objects.

As DDL commands are used to create or alter the database objects and these database objects exist to store data, so, procedure is nothing but a pl/sql block with set of commands use to perform data related operations not database object related operations.

Procedure in Oracle does not allow you to directly alter or create a database object it is done through a command.
Re: Why oracle doesn’t allow direct DDL inside the proc? [message #185066 is a reply to message #184769] Sun, 30 July 2006 18:19 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
For one thing, PL/SQL is required to compile against a known set of database objects. If stored code modifies that set of objects at runtime it will invalidate the compilation, requiring recompilation, which is resource-intensive and not very robust (it could fail recompilation at runtime even though it was previously valid).

Dynamic DDL has its place in batch processing, where you might reasonably exchange a partition and disable indexes during a bulk load. It would be quite rare to create a table, however.
Previous Topic: NOT EXISTS
Next Topic: Using C++ DLL
Goto Forum:
  


Current Time: Fri Dec 02 12:04:58 CST 2016

Total time taken to generate the page: 0.06739 seconds