create table from procedure?

[Volker Hetzer]

Hi!
I've got this little testcase where I create a table from a procedure and
and using begin/end in sqlplus.
Why doesn't the create table work in the procedure too?

create or replace procedure testcase
is
begin
execute immediate 'create table volkerperftest.typeins_0 (indexcol integer, numcol integer,charcol char (200))';
end;
/
begin
testcase;
end;
/
begin
execute immediate 'create table volkerperftest.typeins_0 (indexcol integer, numcol integer,charcol char (200))';
end;
/
exit

Lots of Greetings and Thanks!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[Volker Hetzer]

Volker Hetzer wrote: Hi! I've got this little testcase where I create a table from a procedure and and using begin/end in sqlplus. Why doesn't the create table work in the procedure too?
Got it. Had to do with definer rights and roles.

Lots of Greetings!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[Daniel Morgan]

Volker Hetzer wrote:
Volker Hetzer wrote: Hi! I've got this little testcase where I create a table from a procedure and and using begin/end in sqlplus. Why doesn't the create table work in the procedure too? Got it. Had to do with definer rights and roles. Lots of Greetings! Volker -- While it is a known fact that programmers never make mistakes, it is still a good idea to humor the users by checking for errors at critical points in your program. -Robert D. Schneider, "Optimizing INFORMIX Applications"

If the point of creating the table was to use it as a temp table ... now
that you figured out how to do it ... don't.

There is on need in Oracle to do this. It kills performance; kills
scalability; and is totally unnecessary.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Volker Hetzer]

Daniel Morgan wrote: Volker Hetzer wrote: Daniel Morgan wrote: Volker Hetzer wrote: If the point of creating the table was to use it as a temp table ... now that you figured out how to do it ... don't. There is on need in Oracle to do this. It kills performance; kills scalability; and is totally unnecessary. Actually, the point is to do a performance test... :-) So, I create about 50 tables, insert a lot, update a lot and see how the i/o system reacts. The procedure is supposed to set up the environment. I've to use execute immediate anyway, since I use a random number generator to figure our which table to fill next with a new line. Lots of Greetings! Volker -- While it is a known fact that programmers never make mistakes, it is still a good idea to humor the users by checking for errors at critical points in your program. -Robert D. Schneider, "Optimizing INFORMIX Applications" Save yourself the trouble and read Tom Kyte's book or visit asktom.oracle.com.
Which one?
The fact that you can dynamically build objects in Oracle does not mean you should.
Oracle uses sqlplus to create tables. Don't see that much of a difference.
I mean it's not as if that procedure gets called more than once per
test run. Someone *has* to clean out the old tables abd prepare the
db for the new run.

Btw, has anybody else had problems with forall ... execute immediate?
I got an internal error first time I tried it.

Lots of Greetings!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[Daniel Morgan]

Volker Hetzer wrote:
Daniel Morgan wrote: Volker Hetzer wrote: Daniel Morgan wrote:> Volker Hetzer wrote:> If the point of creating the table was to use it as a temp table ...> now that you figured out how to do it ... don't.>> There is on need in Oracle to do this. It kills performance; kills> scalability; and is totally unnecessary. Actually, the point is to do a performance test... :-) So, I create about 50 tables, insert a lot, update a lot and see how the i/o system reacts. The procedure is supposed to set up the environment. I've to use execute immediate anyway, since I use a random number generator to figure our which table to fill next with a new line. Lots of Greetings! Volker -- While it is a known fact that programmers never make mistakes, it is still a good idea to humor the users by checking for errors at critical points in your program. -Robert D. Schneider, "Optimizing INFORMIX Applications" Save yourself the trouble and read Tom Kyte's book or visit asktom.oracle.com. Which one? The fact that you can dynamically build objects in Oracle does not mean you should. Oracle uses sqlplus to create tables. Don't see that much of a difference. I mean it's not as if that procedure gets called more than once per test run. Someone *has* to clean out the old tables abd prepare the db for the new run. Btw, has anybody else had problems with forall ... execute immediate? I got an internal error first time I tried it. Lots of Greetings! Volker -- While it is a known fact that programmers never make mistakes, it is still a good idea to humor the users by checking for errors at critical points in your program. -Robert D. Schneider, "Optimizing INFORMIX Applications"

If it is not part of the benchmark you are correct. But if your benchmark
includes creating them you are writing for Informix ... not Oracle.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[padderz]

True he has been involved in several. The one mentioned here is (forgive
assumption DM):

Expert One-On-One: Oracle (Thomas Kyte )

Paperback 1250 pages (1 June, 2001)
Publisher: Wrox Press Ltd; ISBN: 1861004826

--
Posted via http://dbforums.com

[Volker Hetzer]

padderz wrote: True he has been involved in several. The one mentioned here is (forgive assumption DM): Expert One-On-One: Oracle (Thomas Kyte ) Paperback 1250 pages (1 June, 2001) Publisher: Wrox Press Ltd; ISBN: 1861004826
Thanks alot!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[Daniel Morgan]

Volker Hetzer wrote:
Daniel Morgan wrote:> Save yourself the trouble and read Tom Kyte's book or visit> asktom.oracle.com. Which one? That guy wrote several books. I'd really like to know which one you would suggest. Lots of greetings! Volker -- While it is a known fact that programmers never make mistakes, it is still a good idea to humor the users by checking for errors at critical points in your program. -Robert D. Schneider, "Optimizing INFORMIX Applications"

Expert one-on-one Oracle.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Volker Hetzer]

Daniel Morgan wrote: Expert one-on-one Oracle.
Thank you!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"