can't create table from package

#11

andized (AT) gmx (DOT) net wrote:

Quote:

i am wondering whats so wrong about performing a truncate.
what would the correct solution look like in your opinion?

Thanks,
AndiZed

As others have asked please do not top post. Scroll to the
bottom and post there.

First TRUNCATE is DDL thus it issues implicit commits.

Second it locks the table forcing serialization. This can
potentially slow things down substantially and, on a RAC
cluster, can bring it to its knees.

The correct solution? Depends on the why the table is being
truncated, how often, and the nature of the environment.
Most of the time when I find this what I find is that the
developers and DBAs do not know how to properly implement
global temporary tables.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#12

On Dec 13, 2:03 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

Quote:

And with 10g and later releases there is CREATE SCHEMA, which would
preclude the use of PL/SQL to create database objects on product
installation, which creates all or nothing should an error occur.

CREATE SCHEMA has been around since at least 7.3:
<http://download.oracle.com/docs/cd/A...ver/doc/SQL73/
ch4a.htm#toc098>

However it is arguably of limited usefulness:
<http://radiofreetooting.blogspot.com/search?q=create+schema>

#13

"William Robertson" <williamr2019 (AT) googlemail (DOT) com> schreef in bericht
news:34f0ee7d-affc-4881-8068-90f51af222e6 (AT) s19g2000prg (DOT) googlegroups.com...

Quote:

On Dec 13, 2:03 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

And with 10g and later releases there is CREATE SCHEMA, which would
preclude the use of PL/SQL to create database objects on product
installation, which creates all or nothing should an error occur.

CREATE SCHEMA has been around since at least 7.3:
http://download.oracle.com/docs/cd/A...ver/doc/SQL73/
ch4a.htm#toc098

However it is arguably of limited usefulness:
http://radiofreetooting.blogspot.com...=create+schema

No, this is not
CREATE SCHEMA
but
CREATE PROCEDURE "schema.procname"

Shakespeare

#14

Quote:

First TRUNCATE is DDL thus it issues implicit commits.

Second it locks the table forcing serialization. This can
potentially slow things down substantially and, on a RAC
cluster, can bring it to its knees.

The correct solution? Depends on the why the table is being
truncated, how often, and the nature of the environment.
Most of the time when I find this what I find is that the
developers and DBAs do not know how to properly implement
global temporary tables.

thanks for pointing this out.
the environment im working in is not a RAC, but it has to run some
batch jobs with large amounts of data very quickly, so i did not want
to use DELETE to empty my tables. global temporary tables sound like a
good idea, i am going to close this gap in my oracle knowledge next.

AndiZed

#15

On Dec 15, 7:52 pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

No, this is not
CREATE SCHEMA
but
CREATE PROCEDURE "schema.procname"

I was responding to David Fitzjarrell's comment that "CREATE SCHEMA"
is available from 10g onwards.

#16

William Robertson wrote:

Quote:

On Dec 15, 7:52 pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

No, this is not
CREATE SCHEMA
but
CREATE PROCEDURE "schema.procname"

I was responding to David Fitzjarrell's comment that "CREATE SCHEMA"
is available from 10g onwards.

CREATE SCHEMA is listed in the Oracle 7 Server SQL Language Quick
Reference (1987).
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#17

On Dec 16, 7:56 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

William Robertson wrote:
On Dec 15, 7:52 pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

No, this is not
CREATE SCHEMA
but
CREATE PROCEDURE "schema.procname"

I was responding to David Fitzjarrell's comment that "CREATE SCHEMA"
is available from 10g onwards.

CREATE SCHEMA is listed in the Oracle 7 Server SQL Language Quick
Reference (1987).

Indeed, it's on page 4-221 of http://download.oracle.com/docs/pdf/A32538_1.pdf
(And check out the typo on page A-18)

The bit about being able to roll back multiple DDL has long been a
source of wonder for me, begging the question of implicit commits on
other DDL.

jg
--
@home.com is bogus.
"Some somehow observe the day,but are vexed, I believe, that the body
of people profane it, and blessed be God no authority yet compels them
to keep it." - Boston Puritan Samuel Sewall, Christmas Day 1685 - when
celebrating Christmas was illegal in New England

#18

joel garry wrote:

Quote:

On Dec 16, 7:56 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
William Robertson wrote:
On Dec 15, 7:52 pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote:
No, this is not
CREATE SCHEMA
but
CREATE PROCEDURE "schema.procname"
I was responding to David Fitzjarrell's comment that "CREATE SCHEMA"
is available from 10g onwards.
CREATE SCHEMA is listed in the Oracle 7 Server SQL Language Quick
Reference (1987).

Indeed, it's on page 4-221 of http://download.oracle.com/docs/pdf/A32538_1.pdf
(And check out the typo on page A-18)

The bit about being able to roll back multiple DDL has long been a
source of wonder for me, begging the question of implicit commits on
other DDL.

jg
--
@home.com is bogus.
"Some somehow observe the day,but are vexed, I believe, that the body
of people profane it, and blessed be God no authority yet compels them
to keep it." - Boston Puritan Samuel Sewall, Christmas Day 1685 - when
celebrating Christmas was illegal in New England

We should perhaps note that these are the same writers that gave us the
nightmare of BHCR monitoring and so many other gems. <g>
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#19

"William Robertson" <williamr2019 (AT) googlemail (DOT) com> schreef in bericht
news:a42098f9-88d9-46dc-ae02-a3a36e38fc4a (AT) t1g2000pra (DOT) googlegroups.com...

Quote:

On Dec 15, 7:52 pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

No, this is not
CREATE SCHEMA
but
CREATE PROCEDURE "schema.procname"

I was responding to David Fitzjarrell's comment that "CREATE SCHEMA"
is available from 10g onwards.

You're right, I was wrong, The link was broken and took me somehow to the
wrong page in the document.

Apologies!

Shakespeare

#20

On Dec 13, 5:35 pm, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:

Quote:

On Dec 13, 2:03 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

And with 10g and later releases there is CREATE SCHEMA, which would
preclude the use of PL/SQL to create database objects on product
installation, which creates all or nothing should an error occur.

CREATE SCHEMA has been around since at least 7.3:
http://download.oracle.com/docs/cd/A...ver/doc/SQL73/
ch4a.htm#toc098

However it is arguably of limited usefulness:
http://radiofreetooting.blogspot.com...=create+schema

I appreciate the correction.

I shall file it under 'Useful Uselessness from Oracle'. <g>

David Fitzjarrell