Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> -> Re: Adding

Re: Adding

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 5 Feb 2004 09:19:41 -0500
Message-ID: <>

"Ger" <> wrote in message
| > 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 (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 am sorry but it is very difficult to understand your description of your requirements.
however, here are some observations:

it is not standard practice to have a system regenerate packages

execute immediate is only required if SQL (or PL/SQL) statements are being generated at runtime

10 to 200 records is not a lot of data for an oracle system -- 10g to 200g is a lot

it sounds like you might have a rules-based system that allows the user to change the rules at will, and you are looking for design alternatives, which will be very difficult to provide without a clearer understanding of your project requirements and more directly involvement.

your best alternative is probably to seek out a local expert that can help you analyze the application

Received on Thu Feb 05 2004 - 08:19:41 CST

Original text of this message