Home » SQL & PL/SQL » SQL & PL/SQL » including DDL within a procedure, why? (10g)
including DDL within a procedure, why? [message #383964] Sun, 01 February 2009 02:28 Go to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi
what is the benefit or use of including DDL statement (ex: create table. ...) within a procedure to be used at runtime
create or replace procedure test
is
begin
execute immediate('create table my_name as select * from employees'); 
end;
thanks
Re: including DDL within a procedure, why? [message #383966 is a reply to message #383964] Sun, 01 February 2009 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None and it is a bad practice.

Regards
Michel
Re: including DDL within a procedure, why? [message #384006 is a reply to message #383964] Sun, 01 February 2009 12:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no benefit, but - except EXECUTE IMMEDIATE - you can't run DDL from PL/SQL. I believe that Michel's "bad practice" means that - in Oracle - you don't create tables on-the-fly and later drop them (such as people do in MS SQL Server, for example). In Oracle, you precreate all objects you need, use them and let them be. Though, you might consider using a Global Temporary Table (instead of an "ordinary" table), if it better suits your needs.

Actually, what are your needs?
Re: including DDL within a procedure, why? [message #384020 is a reply to message #383964] Sun, 01 February 2009 20:47 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Well, the only benefit I can think of is for creating a procedure that creates an external table whose input data are 'dynamic' (ex: different input data every week or everyday, yet the same data format), and the whole procedure does a look up from its fields; based from its fields, I can compare/join/bash etc. them with the other tables intitially stored in the DB and probably generate a file or results.

Besides that, its really a good practice to precreate all the objects or tables you need.

(Well thats just my idea. Probably someone here can suggest something better.)

Regards,
Wilbert
Re: including DDL within a procedure, why? [message #384060 is a reply to message #384020] Mon, 02 February 2009 01:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If the data structure is a constant, why recreate the external table?
Worked with Oracle for quite some years now on quite some projects, and I have NEVER ever found it necessary to create any object at runtime.
The only time I use PL/SQL to create objects is to make installation-scripts rerunnable.
Re: including DDL within a procedure, why? [message #384064 is a reply to message #383964] Mon, 02 February 2009 01:22 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi Frank,

Well its because I dont want to mix new and old data in my external table, so I drop my external table when my procedure is finished 'reading' and do comparisons with other tables stored in my DB, and generate results etc. Same data format (ex: file1.csv, file2.csv) yet different data contents.

If my external table has old data in it, and after a day or two, a new one comes (different filenames, same data format, same number of columns etc., but different data contents), what will be a better option for this? I dont want to mix my old and new data in my external table because new data must only be processed for that week/day/hour. So I find it beneficial to recreate the external table at runtime with its new data and then proceed again with my procedure.

Regards,
Wilbert
Re: including DDL within a procedure, why? [message #384066 is a reply to message #383964] Mon, 02 February 2009 01:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
structure vs. content
The content can change while structure remains the same!

Re: including DDL within a procedure, why? [message #384068 is a reply to message #383964] Mon, 02 February 2009 01:40 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi,

Should I initially create my external table? But how about if a new bunch of data came, how can I delete its old contents and insert the new bunch of data in it?

Regards,
Wilbert
Re: including DDL within a procedure, why? [message #384069 is a reply to message #384068] Mon, 02 February 2009 01:45 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
use delete from
Re: including DDL within a procedure, why? [message #384070 is a reply to message #383964] Mon, 02 February 2009 01:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Should I initially create my external table? But how about if a new bunch of data came, how can I delete its old contents and insert the new bunch of data in it?
Yes, no, maybe; it depends.
What answer is acceptable to you?
If you do not know what answer is " correct", why should we know?
Re: including DDL within a procedure, why? [message #384071 is a reply to message #384068] Mon, 02 February 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how can I delete its old contents and insert the new bunch of data in it?

You miss the main point of an external table: it does not contain any data. It is just a pointer to a file. A pointer that allows you to read the file in a specific way you define in the parameters of the table.

Regards
Michel

[Updated on: Mon, 02 February 2009 01:57]

Report message to a moderator

Re: including DDL within a procedure, why? [message #384094 is a reply to message #384068] Mon, 02 February 2009 03:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel is right. You don't delete data from external tables, you simply replace the underlying file on the OS.
Re: including DDL within a procedure, why? [message #384211 is a reply to message #383964] Mon, 02 February 2009 18:46 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi,

Well its doing what it supposed to do, and processes and generates results like it should, and handles the changing data inputs, so probably i can define that as 'correct'. But I should look at other alternative approach to my process because its unconventional. I guess I missed the point that its a pointer not a table, I don't want to be hard headed in listening to your expert advises, I appreciate it, I really really do, of course I want to write my codes as correct and efficient as possible, but I'm just clueless right now for a different approach. (Probably write a perl or java script to insert the changing new data to a initially defined table in my DB and my PL/SQL script will just take data from that table, just an idea..)

Will still look into this. I appreciate all of your comments.

Regards,
Wilbert
Re: including DDL within a procedure, why? [message #384212 is a reply to message #383964] Mon, 02 February 2009 19:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> (ex: different input data every week or everyday, yet the same data format)

You manually create the External Table once (points to a specific file in a specific directory.

The "switch" from one file to the next file it is done at the Operating System level;
either by copying or renaming files via OS commands.
Oracle will only ever access a single file (name).
Oracle will not know or care that the actual contents of the file was changed automagically by the OS.

Why is this not acceptable?

[Updated on: Mon, 02 February 2009 19:09]

Report message to a moderator

Re: including DDL within a procedure, why? [message #384227 is a reply to message #384212] Mon, 02 February 2009 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
> (ex: different input data every week or everyday, yet the same data format)

You can also change the file that is pointed by the external table with a simple:
alter table ... location (...)

Regards
Michel
Re: including DDL within a procedure, why? [message #384257 is a reply to message #383964] Tue, 03 February 2009 00:47 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Wow, missed that... Big Thanks for the suggestions!! I'll modify my codes asap.

Thanks,
Wilbert
Re: including DDL within a procedure, why? [message #384292 is a reply to message #383964] Tue, 03 February 2009 03:02 Go to previous message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
thanks all
As it seems the end of discussion
----
for wmgonzalbo
I've the situation you mentioned, need deferential accumulated data (of inventory movement)
simply I have only one created table , and use " Merging" within a procedure to get my data through a form's button , then export them into Excel worksheet or XML file through an ordinary Report.
----------

I found the previous code within a website concerning with Oracle.
As I'm not expert enough,
I tried to get benefits and uses of this mechanism.
Now
I think I'm fully agreed with Michel Cadot & Littlefoot
Quote:
There's no benefit & it is bad practice.
and exactly with Frank
Quote:
....and I have NEVER ever found it necessary to create any object at runtime.
specially in most cases, working firms purchase Software Applications, and have not their own Developers & DBA's.

may I'm mistake, but I think that is right.
thanks agian

[Updated on: Tue, 03 February 2009 03:05]

Report message to a moderator

Previous Topic: Temporary tables
Next Topic: how to display null rows using oracle query
Goto Forum:
  


Current Time: Thu Dec 08 14:43:05 CST 2016

Total time taken to generate the page: 0.12305 seconds