Re: Adding

a960807_at_ttu.ee (Ger) wrote in message news:<ceeff88c.0402042343.4a434483_at_posting.google.com>...
> > Hi all. Atm i am writing special system to store and restore
> > information. There were diffirent ideas how to do this, all have
> > pluses and minuses. Finally we desided to write system, what is
> > creating stored procedures and function by inputed architecture. Now i
> > have dilemma.
> > I need to store code on oracle from inside oracle package. Any ideas
> > how can i do this, exect using EXECUTE IMMEDIATE with whole
> > procedure-function-package? Any offers? Awating for them. Thanking
> > everyone.
> > Answer here, or send mail to me a960807_at_ttu.ee (sorry for my
> > univercity mail)...
>
> This is not univercity task, i just use my e-mail from there. :)
> Task is not placed by university, but is just for my job. Atm i am
> doing copy-paste system at application working with Oracle8i and
> Oracle 9i database. I need to store lot data(10-200 record) from
> different tables, with sertain heirarhy. Tehre are different ways to
> do this. In case of stable table system, i can write usual package.
> But current system can be extended and changed(client is goind to
> change it by own will), so copy-paste system must be easily. Usally we
> were using system, where table hierarhi and refereces are stoted in
> tables, and some "engine" is using it, to store data, and later
> restore it in needed form, with key generation. But this system is
> slow, because it is vase on dynamically called commands. Solid package
> is much faster. Current idea is to write system what is writing own
> copy-paste package by rules, so if smth changing, system can
> re-generate packages under new architecture.
> To do this i can create code and run it with EXECUTE IMMEDIATE command
> in oracle, but i dont linke this. Is there any system in Oracle, i can
> store commands and execute when they are finished, like dbms_output?

I will admit to not be completely sure the specifics of what you are trying to do (due in part to language and typo issues), but if you are going to re-generate code: pl/sql and sql to process data based on changes/parameters the customer changes then since you mentioned dbms_output perhaps utl_file is of interest.

See the Oracle Supplied packages manual. Basically utl_file will read and write files on the database server. If you are trying to generate scripts that are submitted from outside the database then this may be an option.

On the other hand if you need to generate and immediately execute your code them dbms_sql which I believe offers more flexibility than execute immediate might be of interest. Also if the generated code can be created in the form of stored procedures or packages then you could use either execute immediate or dbms_job to execute the stoed code. Potentailly generating the code as stored procedures would allow you to generate once and execute many.

Being that you are dealing with a hierarchy I take it you are familar with the Oracle connect by clause.

HTH -- Mark D Powell -- Received on Thu Feb 05 2004 - 15:26:28 CET