Storing oracle procedures on server from inside.

**Volker Hetzer** · Jun 27 '08, 05:55 PM

Re: Storing oracle procedures on server from inside.

"Ger" <a960807@ttu.ee schrieb im Newsbeitrag news:ceeff88c.0 402040713.73f6c 781@posting.goo gle.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@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

**Haximus** · Jun 27 '08, 05:55 PM

Re: Storing oracle procedures on server from inside.

There is no comp.databases. oracle

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

**Volker Hetzer** · Jun 27 '08, 05:55 PM

Re: Storing oracle procedures on server from inside.

"Haximus" <14356256546@sp am.orgschrieb im Newsbeitrag news:DU8Ub.3998 33$X%5.292912@p d7tw2no...

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.c om.

Lots of Greetings!
Volker

**Haximus** · Jun 27 '08, 05:55 PM

Re: Storing oracle procedures on server from inside.

"Volker Hetzer" <volker.hetzer@ ieee.orgwrote in message
news:bvr645$u84 $1@nntp.fujitsu-siemens.com...

>
"Haximus" <14356256546@sp am.orgschrieb im Newsbeitrag

news:DU8Ub.3998 33$X%5.292912@p d7tw2no...

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.c om.

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

**Volker Hetzer** · Jun 27 '08, 05:55 PM

Re: Storing oracle procedures on server from inside.

"Haximus" <14356256546@sp am.orgschrieb im Newsbeitrag news:V99Ub.3999 73$X%5.157062@p d7tw2no...

"Volker Hetzer" <volker.hetzer@ ieee.orgwrote in message
news:bvr645$u84 $1@nntp.fujitsu-siemens.com...

"Haximus" <14356256546@sp am.orgschrieb im Newsbeitrag

news:DU8Ub.3998 33$X%5.292912@p d7tw2no...

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.c om.

>
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.or acle tomorrow, there is no legal
comp.databases. oracle.

Lots of Greetings!
Volker

**Haximus** · Jun 27 '08, 05:55 PM

Re: Storing oracle procedures on server from inside.

"Volker Hetzer" <volker.hetzer@ ieee.orgwrote in message
news:bvrak9$lhq $1@nntp.fujitsu-siemens.com...

>
"Haximus" <14356256546@sp am.orgschrieb im Newsbeitrag

news:V99Ub.3999 73$X%5.157062@p d7tw2no...

"Volker Hetzer" <volker.hetzer@ ieee.orgwrote in message
news:bvr645$u84 $1@nntp.fujitsu-siemens.com...

>
"Haximus" <14356256546@sp am.orgschrieb im Newsbeitrag

news:DU8Ub.3998 33$X%5.292912@p d7tw2no...

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.c om.

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.or acle 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.

**Ger** · Jun 27 '08, 05:55 PM

Adding

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@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?

**Mark C. Stock** · Jun 27 '08, 05:55 PM

Re: Adding

"Ger" <a960807@ttu.ee wrote in message
news:ceeff88c.0 402042343.4a434 483@posting.goo gle.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@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

**Mark D Powell** · Jun 27 '08, 05:55 PM

Re: Adding

a960807@ttu.ee (Ger) wrote in message news:<ceeff88c. 0402042343.4a43 4483@posting.go ogle.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@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 --

**Sybrand Bakker** · Jun 27 '08, 05:56 PM

Re: Storing oracle procedures on server from inside.

On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
<volker.hetzer@ ieee.orgwrote:

>It also isn't carried by groups.google.c om.

Regrettably it still is.

--
Sybrand Bakker, Senior Oracle DBA

**Volker Hetzer** · Jun 27 '08, 05:56 PM

Re: Storing oracle procedures on server from inside.

"Sybrand Bakker" <gooiditweg@syb randb.demon.nls chrieb im Newsbeitrag news:8cv620hlcl f9pdg1aspj32ot8 9vguilvbt@4ax.c om...

On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
<volker.hetzer@ ieee.orgwrote:
>

It also isn't carried by groups.google.c om.

>
Regrettably it still is.

Maybe I'm blind but I had a look at

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases

and it should be right above "comp.databases .oracle.* (4 groups)" right?

Lots of Greetings!
Volker

**Daniel Morgan** · Jun 27 '08, 05:56 PM

Re: Storing oracle procedures on server from inside.

Volker Hetzer wrote:

"Sybrand Bakker" <gooiditweg@syb randb.demon.nls chrieb im Newsbeitrag news:8cv620hlcl f9pdg1aspj32ot8 9vguilvbt@4ax.c om...
>

>>On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
>><volker.hetze r@ieee.orgwrote :
>>
>>

>>>It also isn't carried by groups.google.c om.

>>
>>Regrettably it still is.

>
Maybe I'm blind but I had a look at

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases

and it should be right above "comp.databases .oracle.* (4 groups)" right?
>
Lots of Greetings!
Volker

Google

http://www.google.com

Search the world's information, including webpages, images, videos and more. Google has many special features to help you find exactly what you're looking for.

click on groups
click on comp
click on comp.database -- no 's' at the end
click on comp.database.o racle

--
Daniel Morgan

Home - UW Professional & Continuing Education

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

We make it possible for you to keep learning at the University of Washington, even if you work full time or live outside of the Seattle area.

Home - UW Professional & Continuing Education

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

We make it possible for you to keep learning at the University of Washington, even if you work full time or live outside of the Seattle area.

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

**Folke Larsson** · Jun 27 '08, 05:56 PM

Re: Adding

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

**Volker Hetzer** · Jun 27 '08, 05:56 PM

Re: Storing oracle procedures on server from inside.

"Daniel Morgan" <damorgan@x.was hington.eduschr ieb im Newsbeitrag news:1076081578 .875494@yasure. ..

Volker Hetzer wrote:
>

"Sybrand Bakker" <gooiditweg@syb randb.demon.nls chrieb im Newsbeitrag news:8cv620hlcl f9pdg1aspj32ot8 9vguilvbt@4ax.c om...

>On Wed, 4 Feb 2004 17:20:20 +0100, "Volker Hetzer"
><volker.hetzer @ieee.orgwrote:
>
>
>>It also isn't carried by groups.google.c om.
>
>Regrettably it still is.

Maybe I'm blind but I had a look at

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases

and it should be right above "comp.databases .oracle.* (4 groups)" right?

Lots of Greetings!
Volker

>

Google

http://www.google.com

Search the world's information, including webpages, images, videos and more. Google has many special features to help you find exactly what you're looking for.

click on groups
click on comp
click on comp.database -- no 's' at the end
click on comp.database.o racle

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.c om. :-(
Both, comp.database.o racle 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

Storing oracle procedures on server from inside.

Storing oracle procedures on server from inside.

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment