By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

Storing oracle procedures on server from inside.

P: n/a
Ger
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 a9*****@ttu.ee (sorry for my
univercity mail)...
Jul 19 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a

"Ger" <a9*****@ttu.ee> schrieb im Newsbeitrag news:ce**************************@posting.google.c om...
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 a9*****@ttu.ee (sorry for my
univercity mail)...

With an assignment like that your university will
want to test your understanding of the course you've attended. So, the
idea is that *you* work out the solution, right?
Also, you ought to learn how to figure out the proper newsgroups.
There is no comp.databases.oracle.

Volker
Jul 19 '05 #2

P: n/a
> There is no comp.databases.oracle

There is, it may not be carried by your provider though.
Jul 19 '05 #3

P: n/a

"Haximus" <14*********@spam.org> schrieb im Newsbeitrag news:DU8Ub.399833$X%5.292912@pd7tw2no...
There is no comp.databases.oracle


There is, it may not be carried by your provider though.

I've been told that there is one definitive list of all newsgroups created by the proper voting
process.
Some groups, like the postgres group are "illegal" in that sense. I suspect comp.databases.oracle
belongs to those.
It also isn't carried by groups.google.com.

Lots of Greetings!
Volker
Jul 19 '05 #4

P: n/a
"Volker Hetzer" <vo***********@ieee.org> wrote in message
news:bv**********@nntp.fujitsu-siemens.com...

"Haximus" <14*********@spam.org> schrieb im Newsbeitrag news:DU8Ub.399833$X%5.292912@pd7tw2no...
There is no comp.databases.oracle


There is, it may not be carried by your provider though.

I've been told that there is one definitive list of all newsgroups created

by the proper voting process.
Some groups, like the postgres group are "illegal" in that sense. I suspect comp.databases.oracle belongs to those.
It also isn't carried by groups.google.com.


There is no newsgroup authority or definitive list. Anyone can start one.
All one needs to do is convince other providers to carry it.
Jul 19 '05 #5

P: n/a

"Haximus" <14*********@spam.org> schrieb im Newsbeitrag news:V99Ub.399973$X%5.157062@pd7tw2no...
"Volker Hetzer" <vo***********@ieee.org> wrote in message
news:bv**********@nntp.fujitsu-siemens.com...

"Haximus" <14*********@spam.org> schrieb im Newsbeitrag

news:DU8Ub.399833$X%5.292912@pd7tw2no...
> There is no comp.databases.oracle

There is, it may not be carried by your provider though.

I've been told that there is one definitive list of all newsgroups created

by the proper voting
process.
Some groups, like the postgres group are "illegal" in that sense. I

suspect comp.databases.oracle
belongs to those.
It also isn't carried by groups.google.com.


There is no newsgroup authority or definitive list. Anyone can start one.
All one needs to do is convince other providers to carry it.

Of course, technically, you may create a group, even with a failed votte, but
nevertheless, that creates illegal groups. Have a look at the faq in news.groups
if you want to know more.
There *is* a list of all non-illegal groups, at least for the groups comp, humanities,
misc, news, rec, sci, soc and talk.
So, while there might be an alt.database.oracle tomorrow, there is no legal
comp.databases.oracle.

Lots of Greetings!
Volker
Jul 19 '05 #6

P: n/a
"Volker Hetzer" <vo***********@ieee.org> wrote in message
news:bv**********@nntp.fujitsu-siemens.com...

"Haximus" <14*********@spam.org> schrieb im Newsbeitrag news:V99Ub.399973$X%5.157062@pd7tw2no...
"Volker Hetzer" <vo***********@ieee.org> wrote in message
news:bv**********@nntp.fujitsu-siemens.com...

"Haximus" <14*********@spam.org> schrieb im Newsbeitrag

news:DU8Ub.399833$X%5.292912@pd7tw2no...
> > There is no comp.databases.oracle
>
> There is, it may not be carried by your provider though.
I've been told that there is one definitive list of all newsgroups
created by the proper voting
process.
Some groups, like the postgres group are "illegal" in that sense. I

suspect comp.databases.oracle
belongs to those.
It also isn't carried by groups.google.com.


There is no newsgroup authority or definitive list. Anyone can start one. All one needs to do is convince other providers to carry it.

Of course, technically, you may create a group, even with a failed votte,

but nevertheless, that creates illegal groups. Have a look at the faq in news.groups if you want to know more.
There *is* a list of all non-illegal groups, at least for the groups comp, humanities, misc, news, rec, sci, soc and talk.
So, while there might be an alt.database.oracle tomorrow, there is no legal comp.databases.oracle.


It is not illegal, just obsolete. comp.databases.oracle was the original
"catch all" for the c.d.o.* groups and a place to post topics that did not
apply to specific subgroups. It was renamed comp.databases.oracle.misc,
though it looks like the original group is still being propagated. There is
no such thing as an illegal group, just groups that contain illegal content.
Jul 19 '05 #7

P: n/a
Ger
> 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 a9*****@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?
Jul 19 '05 #8

P: n/a

"Ger" <a9*****@ttu.ee> wrote in message
news:ce**************************@posting.google.c om...
| > 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 a9*****@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?

ger,

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

-- mcs
Jul 19 '05 #9

P: n/a
a9*****@ttu.ee (Ger) wrote in message news:<ce**************************@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 a9*****@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 --
Jul 19 '05 #10

P: n/a
On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
<vo***********@ieee.org> wrote:
It also isn't carried by groups.google.com.


Regrettably it still is.
--
Sybrand Bakker, Senior Oracle DBA
Jul 19 '05 #11

P: n/a

"Sybrand Bakker" <go********@sybrandb.demon.nl> schrieb im Newsbeitrag news:8c********************************@4ax.com...
On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
<vo***********@ieee.org> wrote:
It also isn't carried by groups.google.com.


Regrettably it still is.

Maybe I'm blind but I had a look at
http://groups.google.com/groups?hl=e...comp.databases
and it should be right above "comp.databases.oracle.* (4 groups)" right?

Lots of Greetings!
Volker
Jul 19 '05 #12

P: n/a
Volker Hetzer wrote:
"Sybrand Bakker" <go********@sybrandb.demon.nl> schrieb im Newsbeitrag news:8c********************************@4ax.com...
On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
<vo***********@ieee.org> wrote:

It also isn't carried by groups.google.com.


Regrettably it still is.


Maybe I'm blind but I had a look at
http://groups.google.com/groups?hl=e...comp.databases
and it should be right above "comp.databases.oracle.* (4 groups)" right?

Lots of Greetings!
Volker


http://www.google.com
click on groups
click on comp
click on comp.database -- no 's' at the end
click on comp.database.oracle

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Jul 19 '05 #13

P: n/a
> > 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.


Hello Ger

I don't understand your question either and really don't recognice the
terminology, particulary that hierarchy things. I thinking on the
EXECUTE IMMEDIATE you are using.
I think that the first time the block in the EXECUTE IMMEDIATE is
used it has to be compiled and that takes some time. After that
it will be in RAM and executes mush faster. Are you by the way getting
information out from EXECUTE IMMEDIATE? If Yes, then how ?
Tables or package variables ? Stored code will certainly speed up
the time, and probably reusing the EXECUTE IMMEDIATE blocks. If your
code works today, it could be a tuning issue as someone else pointed
out.

Regards

Folke Larsson
Jul 19 '05 #14

P: n/a

"Daniel Morgan" <da******@x.washington.edu> schrieb im Newsbeitrag news:1076081578.875494@yasure...
Volker Hetzer wrote:
"Sybrand Bakker" <go********@sybrandb.demon.nl> schrieb im Newsbeitrag news:8c********************************@4ax.com...
On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
<vo***********@ieee.org> wrote:
It also isn't carried by groups.google.com.

Regrettably it still is.


Maybe I'm blind but I had a look at
http://groups.google.com/groups?hl=e...comp.databases
and it should be right above "comp.databases.oracle.* (4 groups)" right?

Lots of Greetings!
Volker


http://www.google.com
click on groups
click on comp
click on comp.database -- no 's' at the end
click on comp.database.oracle

1) That's a different group, I was talking about the one with the s on it.
2) You and Sybrand are right regarding my skill with groups.google.com. :-(
Both, comp.database.oracle and comp.databases.oracle are carried by
google. I was always only looking at the upper part of the groups window
without realizing that the base group is not explicitly named if subgroups
exist.
3) However, they are not legal groups. The big-8 groups are maintained by the isc
(www.isc.org), need to go through a vote and the authoritative list is at
ftp://ftp.isc.org/pub/usenet/CONFIG .

Lots of Greetings!
Volker
Jul 19 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.