Mahendra -- Thanks for the question regarding "Oracle's Object Relational Model", version 8.1.7

Thanks for your fast response   December 30, 2001 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Mahendra Jadav from INDIA

Thanks a lot for your fast response.

I am not getting your statement

"I tend not to use the physical structures -- I 
use plain old tables with plain old columns.  I will use OR views to give me easier to use data 
structures in my code however "

Here what do you mean by plain tables? In fact, I am talking about Object tables through out the 
database design. 

Thank You.

Mahendra Jadav.


 

given

create type scalarType as object ( x  int, y int );
create type tableType as table of scalarType;
create type objectType as object ( a int, b date, c tableType, d scalarType );

I would not:

create table T of objecttype nested table c store as t_c;      

I would instead create regular "old fashioned" (use my own physical structures) tables with primary 
keys, foreign keys and then create an OBJECT RELATIONAL VIEW over them.
I prefer to have total control over the physical structures, I do not want an extra 16byte raw 
added to the parent table with a unique constraint and a 16 byte raw added to the child table when 
there most likely exists a perfect NATURAL way to link these tables.... 

Good response   December 31, 2001 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: Anu Kohli from India

Tom, 
That was a good response.
What I understand from your response is that use of objects results in ease of usage but results in 
performance depreciation, Right???
Are there any cases where use of Object Oriented and Object relational technology can lead to 
performance improvement or better space management? 

Well, if you use OR views, you are using OR technologies and you can get good space managment as 
well as performance.  Once of the cool things is the ability to one ONE trip to the server -- get a 
"jagged" result set.  Usually result sets are square -- rows/columns.  With objects, they can be 
jagged.  I can materialize an object that has a single "row" for the DEPARTMENT, an ARRAY of 
employees, an ARRAY of projects the department is working on and so on.  A result set that would 
require AT LEAST 2 queries in a pure relational model and would typically bring back the DEPARTMENT 
information for every employee (not just once).  So yes, there can be savings in that fashion. 

Little OO problem...   May 22, 2003 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: Piotr Jarmuz from Braunschweig, Germany

How can I call an overridden method inside the overriding method in PL/SQL?

e.g.
in java I'd use:

void method(int x) {
  super.method(x);
  ...
}

in C++:
virtual void method(int x) {
  BaseClass::method(x);
  ...
}

How to achieve this in PL/SQL?

Regards 

you cannot currently. 

Hmmm...   May 23, 2003 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Piotr Jarmuz from Braunschweig, Germany

So it really means that OO extensions in PL/SQL are of VERY, VERY limited use (at least to me and 
at least now)...

Sure one can use them but anything requiring non-trivial class hierarchy where one could slightly 
reimplement the functionality of a base class in a derived class is virtually impossible (or maybe 
there are some tricks to achieve this?)

I believe that lack of possibility to make such calls is a language design flaw (omission?) not a 
pre-meditated feature ;) Cause technically it should not be that hard to fix it basically in ~ 100 
lines of source code for the PL/SQL compiler. Do you think if the next release will have this 
fundamental feature properly implemented?

Thank you anyway, bad information sometimes is better than no information at all...

Regards 

Dont Shoot the Messenger   May 30, 2003 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Piotr, do you mean bad *news* is better than no news? I agree, because knowing the bad news at 
least lets you prepare for alternatives... 

Never meant to shoot anyone :-)   May 31, 2003 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Piotr Jarmuz from Braunschweig, Germany

But yes I meant that I can at least stop scratching my head 
and stop looking for non-existent solution...

Hey! But seems like I have found an akward, ugly and dirty solution to my problem... Small sample 
of what and how I do:


CREATE OR REPLACE type person_ot
is object
(
   firstname varchar2(100),
   lastname varchar2(100),

   member procedure print,
   member procedure do_print,
   static function make return person_ot
) not final
/
CREATE OR REPLACE type body person_ot
is
   member procedure do_print
   is
   begin
        p('Firstname=' || firstname || ' Lastname=' || lastname);
   end;

   member procedure print
   is
   begin
           do_print;
   end;

   static function make return person_ot
   is
   begin
           return person_ot(null,null);
   end;
end;
/


CREATE OR REPLACE type user_ot under person_ot (
   name varchar2(30),
   password varchar2(30),
   overriding member procedure print
);
/
CREATE OR REPLACE type body user_ot
is
   overriding member procedure print
   is
   begin
    self.do_print;
    p('Name=' || name || ' Password=' || password);
   end;
end;
/


Ugly? Sure but it works :-)))
Right now it is a matter of habit I must simply remember to call do_XXX() instead of XXX() directly 
whenever I want to override XXX()... But anyway I hope that Oracle development team will fix it in 
the next release.

Regards 

table design question   July 23, 2003 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Hi Tom
Which is better and why?
1. 

CREATE TYPE type_object AS OBJECT (x integer );
CREATE TABLE type_object_table OF type_object;

2. CREATE TABLE normal_relational_table( x integer );

In the first alternative, I have an object type and I am
creating a table of that object type. in the second
one, I am creating a normal relational table with
effectively the same information.

What are the pros and cons of each approach? (assume
thay in the object we are not using any methods etc.)

Thank you - hope you are having a lovely vacation!! 

#2 seems to have less code doesn't it.


I never use object types for persistant stored objects - in plsql, all over the place.  as a 
storage mechanism -- no. 

object table versus relational table   July 23, 2003 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

one disadvantage could be I cant create primary key
or fk or any other constraints on the object table (
at least I dont of a method)
You also mention in your book that object tables
(actually nested tables which are not exactly object 
tables) get stored as relational underneath - with
lots of magic that can surprise you (e.g. hidden primary
keys in nested table etc) That could also be a disadvantage.
 

you can have primary/foreign/etc etc on an object table. 

  September 14, 2003 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

if you have my book expert one on one oracle -- read the chapter on object stuff.  

the answer is easy -- i never use object types to create persistently stored objects.  I always use 
relational tables.  I use the object extensions only when programming (in plsql) 

thanx Tom   September 14, 2003 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Menon 

Yes, I do have both your books and I know your philosophy
related to how you use object tables:)

I thought I would just run these results by you since
you have a sharp eye for mistakes and also it may be
useful for others.

Thanx for a great web site!

Menon:) 

Limitations of objects in Oracle   March 20, 2004 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Oracle objects have come a long way since their
introduction - the most important improvement perhaps 
being that of type evolution.
However, it still has some major limitations.
For example, it seems that encapsulation of data 
in object types is weak in Oracle since it does not support
private attributes. In Java if you have a "set" method
that sets an attribute(declared private), 
you are guaranteed that the method is the *only* mechanism of changing the attribute - this is a 
major feature of the object orientedness of Java (or for that matter, any OO language.)

In Oracle objects (even in 10g), the attributes
are public. So you can change them directly 
without using any "set" methods. Perhaps
a mechanism by which you can declare object attributes
within the body (instad of the specification) may
be useful (kind of like packages.) currently
this is not supported (even in 10g)

Following are the reasons I think Oracle does not 
support this yet (assuming you are using objects in
tables instead of object views/as extensions to pl/sql.)

1. Object data is stored in tables in the end. If 
   the only way to get at the data is through setter
   and getter then the performance of any dmls
   that retrieve/modify data would suffer due to the
   extra function calls required. 
2. There may be ways of modifying the table data themselves
   thus overcoming the "private" data in objects.
   This could perhaps be handled by some privileges
   (e.g. give privilege to execute the object methods
   but dont give privilege to modify the underlying 
   table directly?
Even though (as your book states), objects give us
a way of binding data to methods, they do not seem to 
guarantee that the methods are the only way to change/see the data - which defeats to a large 
purpose the encapsulation mechanism.

Your thoughts?
Thank you!
Menon:) 

my thoughts are that I use them as a useful programming technique (in plsql code) but I've never 
used them to actually store data.

They are a tool.  I'm not even going to compare them to java or any other language really.  they 
are what they are.... 

even in extending pl/sql   March 22, 2004 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

it may be useful only for small utilities perhaps. Any
serious OO programming is hampered by the lack
of true encapsulation mechanism. Of course on this
site and in your books, you have demonstrated lot of times how useful these features are for 
utilities..
I do feel that if Oracle completes the OO ness
of its implementation, its range as a programming
language would increase tremendously...

Thanx!
Menon:) 

I guess that assumes that you have bought into "OO" is the "RW" (right way) -- i myself find you 
can write very good code with little effort with in C, you can write really bad code with lots of 
work in C.

You can write very good code with little effort in Java|Smalltalk|Lisp|Eifel|C++.
You can write really bad code with lots of work in the same.

they are just programming languages.

Objects, Types, etc -- syntatic sugar, and sometimes sugar is bad for your health.  

OO is just one way.  It's funny how to me OO is just a codification of what we used to call 
"modular programming".  Just as a rose smells just as sweet by any other name -- a Subroutine is 
still a subroutine (even if you call it a method) 

well..   March 22, 2004 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Menon 

"I guess that assumes that you have bought into "OO" is the "RW" (right way)"

I never said that:)
All I am saying is that Oracle claims it has support
for Object Oriented programming but this claim is hampered
by the shortcomings (some of which I pointed above.)

OO in my humble opinion, is definitely not what I would
call the "right way" all the time. There is no right way for all situations. There are times when 
OO is a more powerful programming paradigm that its procedural counterparts - other times it only 
complicates
code and procedural language is better off.

Just because most J2EE developers abuse the database
developement by their lack of knowledge of db, does
not make Java or OO any less or more powerful paradigm than it is.
And in my opinion, OO is perhaps a bit more than mere
syntactic sugar...

Thanx!:)
Menon:)


 

Object Relational is all we've claimed....

;) 

Help   May 2, 2004 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

Hi Tom,

I have a set of types:

CREATE OR REPLACE 
TYPE person_type AS OBJECT (
   first_name                    VARCHAR2 (50),
   last_name                     VARCHAR2 (50),
   per_id                        NUMBER);

CREATE OR REPLACE 
TYPE person_ref_ntabtype AS TABLE OF REF person_type;


CREATE OR REPLACE 
TYPE project_type AS OBJECT (
   prj_id                        VARCHAR2 (10),
   description                   VARCHAR2 (100),
   prj_per                       person_ref_ntabtype);


I'm have created tables for these type;

CREATE TABLE person
OF PERSON_TYPE
    (per_id      NOT NULL
  ,
  PRIMARY KEY (per_id));

CREATE TABLE PROJECT OF PROJECT_TYPE
NESTED TABLE PRJ_PER STORE AS PRJ_PER_TAB;

I have a record in PERSON as 

insert into person
values(person_type('p8','p8',008));

Now I want to innsert a record into project;
I have tried many ways but with no success.

1.

insert into project
values ( 'PM1',
         'test',
         person_ref_ntabtype(SELECT ref(p) FROM person_type p WHERE per_id = '008'));

2.
insert into project
values ( 'PM1',
         'test',
         person_ref_ntabtype());
         
insert into table(select p.prj_per
                  from project p
                  where prj_id='PM1')
values (person_ref_ntabtype(SELECT ref(p) FROM person_type p WHERE per_id = '008'));

3.

insert into project
values ( 'PM1'
        ,'test'
        ,person_type('p9','p9',009));

I always get ORA-00932, inconsistent data types error. Please help to figure out this.

Thanks in advance. 

ops$tkyte@ORA9IR2> insert into person values ( 'tom', 'kyte', 111 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into person values ( 'frank', 'smith', 222 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into project values
  2  ( 1, 'first project',
  3    cast( multiset( select ref(p)
  4                      from person p) as person_ref_ntabtype ) );
 
1 row created.
 
ops$tkyte@ORA9IR2> select * from project;
 
PRJ_ID
----------
DESCRIPTION
------------------------------------------------------------------------
PRJ_PER
------------------------------------------------------------------------
1
first project
PERSON_REF_NTABTYPE(0000220208D971AF5F498C9312E030A8C003006A02D971AF5F49
8A9312E030A8C003006A02, 0000220208D971AF5F498D9312E030A8C003006A02D971AF
5F498A9312E030A8C003006A02)
 
 

  May 6, 2004 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Venu 

Tom

We have a need to add attributes to an object type. What is
the best practice for doing this? Appreciate if you can outline the
steps to be taken and the precautions to be taken. The table that has
one of the columns as an object type has lots of dependencies.
Also will 9.2 version allow us to add/modify attributes. 

in 8i, pretty much "not going to happen" if you have persistently stored objects.  there was no 
type evolution.

You'll need to basically migrate all of the data out, rebuild the object types/tables, and migrate 
it back in.

In 9i, it is possible to evolve a type -- add attributes even if you have persistently stored 
objects that use it, they'll be altered as well.  Might be an excellent reason to get up to date... 

Just a clarification on tables/objects!   May 6, 2004 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Muhammad Ibrahim from Hongkong

Dear Tom,

   If i want to only manipulate the data (in plsql) and finally store the result(after so many 
process) in permanant table which way of using is better ie either object type tables or physical 
table or global temp tables? i mean for manipulating purpose. If i use physical tables i need to 
delete/update/insert several times on the same table to play with the data so i am confused whether 
to use normal table/global temp table/object type for manupilating the data.

eg: i get  a set of dates i need to find slice the dates and do some process then finally store(for 
this ofcourse we will use physical tables) it in the database.

Thanks and Regards,
Ibrahim. 

everything depends on scale.

answers would be different for a couple of hundred pieces of data
vs
a couple thousand or millions


 

  May 7, 2004 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Tom,

Yes we are on 9.2 and I could get the type altered with cascade option. But when I try to describe 
the type, it gives out error saying "object has been evolved". I exited out and reconnected and it 
worked fine. Is this some sort of a bug, as I have to take care of this when we roll it out to 
prodn. 
By the way Tom, I have to appreciate the work you do selflessly. Hope there are few more experts in 
other fields too who can do this type of work what you do. 

ops$tkyte@ORA9IR2> !oerr ora 22337
22337, 00000, "the type of accessed object has been evolved"
// *Cause:  The type of the accessed object has been altered and the client's
//          object is based on an earlier type definition.
// *Action: The user needs to exit application and modify application to
//          accommodate the type change.
//          From SQL/PLUS, reconnect and resubmit statement.
 
"expected" behavior. 

q on iot in nested table   May 8, 2004 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Hi tom
Please look at the following example
where I compare nested table as IOT versus
as heap for storage saving. I dont see any
storage saving even though I used compress
option. Can you take a look? (both cases use
256 blocks)

Thanx!
------------
ttalent@ORA10G> drop table parent_nt_heap_child;

Table dropped.

talent@ORA10G> drop table parent_nt_iot_child;

Table dropped.

talent@ORA10G> drop type child_tab;

Type dropped.

talent@ORA10G> drop type child;

Type dropped.

talent@ORA10G> create or replace type child as object
  2  (
  3    parent_id number,
  4    child_id number,
  5    child_name varchar2(50),
  6    child_desc varchar2(500)
  7  );
  8  /

Type created.

talent@ORA10G> /* create the nested table type */
talent@ORA10G> create or replace type child_tab as table of child;
  2  /

Type created.

talent@ORA10G>
talent@ORA10G> create table parent_nt_iot_child
  2  (
  3    parent_id number primary key,
  4    parent_name varchar2(50),
  5    children child_tab
  6  )
  7  nested table children store as children_nt_iot(
  8    (primary key(nested_table_id, child_name))
  9   organization index compress 1);

Table created.

talent@ORA10G>
talent@ORA10G> create table parent_nt_heap_child
  2  (
  3    parent_id number primary key,
  4    parent_name varchar2(50),
  5    children child_tab
  6  )
  7  nested table children store as children_nt_heap
  8  ( (primary key(nested_table_id, child_name) ) );

Table created.

talent@ORA10G> begin
  2    for i in 1 .. 10000
  3    loop
  4      insert into parent_nt_heap_child values
  5      ( i, 'parent '||i,
  6        child_tab(
  7          (child(i, i, 'child'||i, 'child'||i||' description')),
  8          (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
  9          )
 10      );
 11      insert into parent_nt_iot_child  values
 12      ( i, 'parent '||i,
 13        child_tab(
 14          (child(i, i, 'child'||i, 'child'||i||' description')),
 15          (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
 16          )
 17      );
 18    end loop;
 19  end;
 20  /

PL/SQL procedure successfully completed.

talent@ORA10G> exec show_iot_space( 'CHILDREN_NT_IOT' )
IOT used 256

PL/SQL procedure successfully completed.

talent@ORA10G> exec show_space ( 'CHILDREN_NT_HEAP' )
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               6
Full Blocks        .....................             173
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          10,632
Last Used Block.........................              64

 

I added:

nested table children store as children_nt_iot(
  (constraint nt_iot primary key(nested_table_id, child_name))
 organization index compress 1);


and show space shows:

ops$tkyte@ORA9IR2> exec show_space( 'CHILDREN_NT_HEAP' );
Free Blocks.............................               4
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              78
Unused Bytes............................         638,976
Last Used Ext FileId....................               6
Last Used Ext BlockId...................             649
Last Used Block.........................              50
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec show_space( 'NT_IOT', user, 'INDEX' );
Free Blocks.............................               3
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................             103
Unused Bytes............................         843,776
Last Used Ext FileId....................               6
Last Used Ext BlockId...................             905
Last Used Block.........................              25
 
PL/SQL procedure successfully completed.
 


so, the heap table took 178 blocks (allocated 256, didn't use 78 of them) and the IOT took 153 
(allocated 256, didn't use 103 of them)


with only two repeated values on the leading edge, i would not expect HUGE differences.  
 

hmmmm...   May 10, 2004 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Menon:) 

I tried the same test as above (with your modifications 
though it should  not have any effect on the
results)
However, I get different results - the compressed
IOT seems to take more space!:)

IOT = 384-64 = 320
HEAP = 256-64 = 192

Menon:)
-----------
scott@ORA10G> drop table parent_nt_heap_child;

Table dropped.

scott@ORA10G> drop table parent_nt_iot_child;

Table dropped.

scott@ORA10G> drop type child_tab;

Type dropped.

scott@ORA10G> drop type child;

Type dropped.

scott@ORA10G> create or replace type child as object
  2  (
  3    parent_id number,
  4    child_id number,
  5    child_name varchar2(50),
  6    child_desc varchar2(500)
  7  );
  8  /

Type created.

scott@ORA10G> /* create the nested table type */
scott@ORA10G> create or replace type child_tab as table of child;
  2  /

Type created.

scott@ORA10G> 
scott@ORA10G> create table parent_nt_iot_child
  2  (
  3    parent_id number primary key,
  4    parent_name varchar2(50),
  5    children child_tab
  6  )
  7  nested table children store as children_nt_iot(
  8    (constraint nt_iot_pk primary key(nested_table_id, child_name))
  9   organization index compress 1);

Table created.

scott@ORA10G> 
scott@ORA10G> create table parent_nt_heap_child
  2  (
  3    parent_id number primary key,
  4    parent_name varchar2(50),
  5    children child_tab
  6  )
  7  nested table children store as children_nt_heap
  8  ( (primary key(nested_table_id, child_name) ) );

Table created.

scott@ORA10G> begin
  2    for i in 1 .. 10000
  3    loop
  4       insert into parent_nt_heap_child values
  5       ( i, 'parent '||i,
  6         child_tab(
  7           (child(i, i, 'child'||i, 'child'||i||' description')),
  8           (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
  9           )
 10       );
 11       insert into parent_nt_iot_child  values
 12       ( i, 'parent '||i,
 13         child_tab(
 14           (child(i, i, 'child'||i, 'child'||i||' description')),
 15           (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
 16           )
 17       );
 18    end loop;
 19  end;
 20  /

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space( 'PARENT_NT_HEAP_CHILD')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               6
Full Blocks        .....................              52
Total Blocks............................              64
Total Bytes.............................         524,288
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           9,472
Last Used Block.........................               8

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space( 'PARENT_NT_IOT_CHILD')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               6
Full Blocks        .....................              52
Total Blocks............................              64
Total Bytes.............................         524,288
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          16,136
Last Used Block.........................               8

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space( 'NT_IOT_PK', user, 'INDEX' )
Unformatted Blocks .....................              80
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................              79
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             147
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          13,832
Last Used Block.........................              64

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space ( 'CHILDREN_NT_HEAP' )
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               6
Full Blocks        .....................             173
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          10,248
Last Used Block.........................              64

PL/SQL procedure successfully completed.

---------- 

you are using ASSM, totally different space allocation algorithms.

add up the fs1..fs4 and full blocks.

NT_IOT_PK has 147 full blocks, and 79 blocks that are 25-50% full.
CHILDREN_NT_HEAP has 173 full blocks and 6 almost full and 1 half full.


So, say the 79 blocks are half way (37.5% full), thats 176 blocks in the IOT.
Using the same "half way", the heap table has 179 blocks.


smaller.  but again, 2 repeats = big deal.

Also, please add in the primary key of the heap.
 

thanx Tom!   May 10, 2004 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Menon :) 

Yes - learnt something again - always wondered
what those "FS1" etc. meant:)
btw, not sure what you meant by 
"Also, please add in the primary key of the heap."?

There is a primary key in the heap as far as I can
tell..

Thanx a lot!!!
 

add in its storage.

If you want to compare

a) heap
vs
b) iot

you need to compare

a) heap + primary key index
vs
b) iot

if you want to compare apples to apples. 

good point!!   May 10, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Menon:) 

Thanx - that is definitely correct:)

Please keep up the great work - I am your fan
as most of my co workers know (sometimes to their
"annoyance")!:)

 

Naming the columns in a object type   May 16, 2004 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

From reading your site and in my own experience, I find that scalar object types are most useful in 
getting multiple fields back in a scalar subquery, part of a larger query. Something like

select a,b,(cast(select c,d,e from ...) as object_t)
from ...

If I define the type as 

create or replace type name_t as object 
(  last_name varchar2(50),
   first_name varchar2(50)
)

and so on i.e. with properly defined column names.

Now, when  I want to get 2 varchar2(50) columns in a unrelated query, I cant really use name_t 
because it has nothing to do with names.

Instead, if I define a generic type like
create or replace type varchar2_t as object
( col1 varchar2(2000),
  col2 varchar2(2000)
),

I can use this varchar2_t in any query where I need a maximum of 2 varchar2 columns back from a 
scalar subquery.

In other words, I would define object types for the basic types like number, varchar2, date, etc 
with a maximum of N columns and then I can use them in any scalar subquery where I need a maximum 
of N columns of that type.

[Of course, if I need a mixture of columns of different types in my subquery, I cannot use this 
approach and then I would create a properly named type for this purpose]

Comments? Thanks 

well, you could also just pick a number, say "N" and use that as the basis for the object type:




scott@ORA9IR2> create or replace type myType as object
  2  ( c1 varchar2(4000), c2 varchar2(4000), c3 varchar2(4000), c4 varchar2(4000)
  3  )
  4  /
 
Type created.
 
scott@ORA9IR2>
scott@ORA9IR2> select ename, substr(t.dept.c1,1,15) dname, substr(t.dept.c2,1,13) loc
  2    from (
  3  select ename, (select myType(dname,loc,null,null) from dept
  4                  where dept.deptno = emp.deptno ) dept
  5    from emp
  6         ) t
  7  /
 
ENAME      DNAME           LOC
---------- --------------- -------------
SMITH      RESEARCH        DALLAS
ALLEN      SALES           CHICAGO
WARD       SALES           CHICAGO
JONES      RESEARCH        DALLAS
MARTIN     SALES           CHICAGO
BLAKE      SALES           CHICAGO
CLARK      ACCOUNTING      NEW YORK
SCOTT      RESEARCH        DALLAS
KING       ACCOUNTING      NEW YORK
TURNER     SALES           CHICAGO
ADAMS      RESEARCH        DALLAS
JAMES      SALES           CHICAGO
FORD       RESEARCH        DALLAS
MILLER     ACCOUNTING      NEW YORK
 
14 rows selected.


and just use to_number, to_date as appropriate (as you would have to come up with 'hybrid' types 
otherwise, some vc's, some dates, some numbers and so on) 

Thanks   May 16, 2004 - 7pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Good idea. Thanks. A couple of questions

1. Suppose I define a object type like
create or replace type mytype as object (
 c1 varchar2(4000)
 c2 varchar2(4000),
 c3 varchar2(4000)
)

And I use it to select out a varchar2, date and number column from a table. Where exactly do I put 
the to_date() and to_number()?

select a,b,t.emp_t.ename,to_date(t.emp_t.hire_date,'dd-mon-yyyy'),
to_number(t.emp_t.salary) from (
select a,b,(select mytype(ename,hire_date,salary) from emp) emp_t
from ...) t

Or do I put the functions in the mytype(...) constructor itself? If both are ok, is there any 
difference between them? Do I have to worry about any implicit type conversions in each case?

2. Since my scalar type contains N attributes and if I need less than N in a query, I need to pass 
NULL for the rest of the parameters.
select mytype(c1,c2,null,null,...) 

Is there a way to have the type's constructor function or something automagically default the 
parameters so that I can just pass in as many as I need and the rest are assumed to be null?

I tried defining the type as

create or replace type mytype as object (
 c1 varchar2(4000),
 c2 varchar2(4000) DEFAULT NULL
)

The type was created, but when I tried to use it I got a 'incomplete type' error. 

Thanks 

scott@ORA9IR2> select deptno,
  2         substr( t.data.c1, 1, 10 ) ename,
  3             to_date(t.data.c2,'yyyymmddhh24miss') hiredate,
  4             to_number(t.data.c3) empno
  5    from (
  6  select deptno,
  7         (select myType( ename, to_char(hiredate,'yyyymmddhh24miss'), empno, null )
  8                from emp
  9                   where emp.deptno = dept.deptno
 10                     and rownum = 1 ) data
 11    from dept
 12         ) t
 13  /
 
    DEPTNO ENAME      HIREDATE       EMPNO
---------- ---------- --------- ----------
        10 CLARK      09-JUN-81       7782
        20 SMITH      17-DEC-80       7369
        30 ALLEN      20-FEB-81       7499
        40



you could setup default constructors, but I would just send in the nulls to avoid having to call a 
custom constructor for each row 

is this a bug?   June 7, 2004 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Menon 

Hi Tom 
Is this a bug in 10gr1 or am I missing something? 
- the subscript 100 gives an error below..Any higher
 number gives the same problem. (100  may not be the 
lowest number where the exception occurs.) Same results 
in 9201.

--
benchmark@ORA10G> drop type  bm_type force;

Type dropped.

benchmark@ORA10G> drop type  bm_type_varray force;

Type dropped.

benchmark@ORA10G> 
benchmark@ORA10G> /* create an object type - bm_type */
benchmark@ORA10G> create or replace type bm_type as object
  2  (
  3    x_char varchar2(30),
  4    y_number number,
  5    z_date  date
  6  );
  7  /

Type created.

benchmark@ORA10G> show errors;
No errors.
benchmark@ORA10G> 
benchmark@ORA10G> /* create a varray of bm_types */
benchmark@ORA10G> create or replace type bm_type_varray as varray(20) of bm_type;
  2  /

Type created.

benchmark@ORA10G> declare
  2    l_bm_type_varray bm_type_varray ;
  3  begin
  4    l_bm_type_varray := bm_type_varray();
  5    l_bm_type_varray.extend(5);
  6  end;
  7  /

PL/SQL procedure successfully completed.

benchmark@ORA10G> declare
  2    l_bm_type_varray bm_type_varray ;
  3  begin
  4    l_bm_type_varray := bm_type_varray();
  5    l_bm_type_varray.extend(10);
  6  end;
  7  /

PL/SQL procedure successfully completed.

benchmark@ORA10G> declare
  2    l_bm_type_varray bm_type_varray ;
  3  begin
  4    l_bm_type_varray := bm_type_varray();
  5    l_bm_type_varray.extend(100);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5


Thanx!
 

create or replace type bm_type_varray as varray(20) of
                                                ^^^


you said 20 and then tried to go for 100.   

duh!!   June 7, 2004 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Sorry to have bothered you with such a trivial q!:) 

compare objects..?   March 4, 2005 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

I have two objects, how can I compare them ?
----
Oracle 8.1.7.4 EE on Sun Sol
----
eg.

 CREATE TYPE defaulttype AS OBJECT (
   code   CHAR (16),
   name  VARCHAR2 (4000),
   description   varchar2(4000),
   value_date     DATE,
   is_live        CHAR (1),
   site_code      CHAR (11),
   updated   DATE
);

CREATE OR REPLACE TYPE data_array_type IS VARRAY (4000) OF defaulttype;


---------------------
create table data_value_table (
   code   CHAR (16),
   name  VARCHAR2 (4000),
   description   varchar2(4000),
   value_date     DATE,
   is_live        CHAR (1),
   site_code      CHAR (11),
   updated   DATE);
-------------------------
CREATE OR REPLACE PROCEDURE p_add_value (p_data_array data_array_type) AS
   v_data_array   data_array_type;
BEGIN
   v_data_array := data_array_type ();
   v_data_array.EXTEND (50);

--*** now I want to load  values in  my local
--*** v_data_array from cursor, and than
--*** compare v_data_array with p_data_array
-- any easy way ? examples ?
   FOR x IN (SELECT *
               FROM data_value_table)
   LOOP
---Q1. how do I load my v_data_array ?
      NULL;
   END LOOP;
-- Q2, How do I **comapre** the database values in
-- v_data_array to parameter valus in p_data_array ?
END;
/

show err;

please help
thanks, 

you implement map or order methods in order to be able to compare and sort objects.

for you are the only one that knows how to "compare" them.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96594/adobjbas.htm#459409

polymorphism   April 21, 2005 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: András Csicsári from Hungary

Hi Tom!

I think that I have found a very interesting bug/feature related to polymorphism... My test script 
looks like:

SET SERVEROUTPUT ON
SET FEEDBACK OFF

CREATE TYPE t_root
  AS OBJECT(
    dummy  NUMBER
  )
  NOT FINAL
  NOT INSTANTIABLE
/

CREATE TYPE t_under_root_1
  UNDER t_root(
    test_id  NUMBER
   ,CONSTRUCTOR FUNCTION t_under_root_1 RETURN SELF AS RESULT
  )
/

CREATE TYPE BODY t_under_root_1 AS
  CONSTRUCTOR FUNCTION t_under_root_1
    RETURN SELF AS RESULT IS
  BEGIN
    RETURN;
  END;
END;
/

CREATE TYPE t_under_root_2
  UNDER t_root(
    test_object  t_under_root_1
   ,CONSTRUCTOR FUNCTION t_under_root_2 RETURN SELF AS RESULT
  )
/

CREATE TYPE BODY t_under_root_2 AS
  CONSTRUCTOR FUNCTION t_under_root_2
    RETURN SELF AS RESULT IS
  BEGIN
    SELF.test_object := t_under_root_1;
    RETURN;
  END;
END;
/

DECLARE
  v_root          t_root;
  v_under_root_1  t_under_root_1;
  v_under_root_2  t_under_root_2;

  PROCEDURE extract_test IS
    v_test_id  NUMBER;
  BEGIN
    SELECT treat( v_root AS t_under_root_1 ).test_id
      INTO v_test_id
      FROM dual;
    dbms_output.put_line( nvl( to_char(v_test_id), 'NULL' ) );
  END;

BEGIN

  -- 1 - simple inherited object
  v_root := t_under_root_1( dummy   => 1
                           ,test_id => 1 );
  extract_test;


  -- inherited object as part of an other inherited object

  -- 2 - explicit initialization / explicit association to supertype
  v_root :=
    t_under_root_2( dummy       => 1
                   ,test_object => t_under_root_1( dummy   => 1
                                                  ,test_id => 1 ) ).test_object;
  extract_test;

  -- 3 - explicit initialization / implicit association to supertype v1
  v_under_root_2 :=
    t_under_root_2( dummy       => 1
                   ,test_object => t_under_root_1( dummy   => 1
                                                  ,test_id => 1 ) );
  v_root := v_under_root_2.test_object;
  extract_test;

  -- 4 - explicit initialization / implicit association to supertype v2
  v_under_root_1 := t_under_root_1( dummy   => 1
                                   ,test_id => 1 );
  v_under_root_2 := t_under_root_2( dummy       => 1
                                   ,test_object => v_under_root_1 );
  v_root := v_under_root_2.test_object;
  extract_test;

  -- 5 - implicit initialization - constructors used / implicit association to supertype
  v_under_root_2 := t_under_root_2();
--  v_under_root_2.test_object := t_under_root_1(); -- not really necessary
  v_under_root_2.test_object.test_id := 1;
  v_root := v_under_root_2.test_object;
  extract_test;

  -- 6 - same as 5 - except the explicit sub-object initialization
  v_under_root_2 := t_under_root_2();
  v_under_root_2.test_object := t_under_root_1(); -- not really necessary
  v_under_root_2.test_object.test_id := 1;
  v_root := v_under_root_2.test_object;
  extract_test;

  -- 7 - TOTALLY same as 5
  v_under_root_2 := t_under_root_2();
--  v_under_root_2.test_object := t_under_root_1(); -- not really necessary
  v_under_root_2.test_object.test_id := 1;
  v_root := v_under_root_2.test_object;
  extract_test;

END;
/

DROP TYPE t_under_root_2;
DROP TYPE t_under_root_1;
DROP TYPE t_root;

-- RESULTS on Oracle9i Enterprise Edition Release 9.2.0.6.0 for MS-Win:
-- 1 - fine
-- 1 - fine
-- 1 - fine
-- 1 - fine
-- 1 - fine
-- 1 - fine

-- RESULTS on Oracle9i Enterprise Edition Release 9.2.0.5.21 for IBM z/OS:
-- ( also the same on linux and aix/unix )
-- 1 - fine
-- 1 - fine
-- NULL - bad
-- NULL - bad
-- NULL - bad
-- 1 - fine
-- 1 - fine


I know that this one is a bit sophisticated, but I relly need these features. I will report this as 
a bug, but before doing that, please confirm me that this is not a feature what happening on z/OS.

I think this is related to the representation of the objects, because if I try to identify the 
object type with "sys_typeid(object)" or "object is of (ONLY type)" the result is bad in the 
problematic cases above.

Thanks a lot! 

looks to be a 9205 and before issue, it reproduced in my 9204 database, but not 10g. 

SQL-3 new features   May 15, 2005 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: AD from UK

Tom,
Could you please explain me the following two terms or point me to a reference with respect to 
object relational databases:

- dereference
- double dot notation 

Thanks, 

dereference, to get the value of.  You have a pointer to something, in order to manipulate the 
thing it points to, you dereference it.

http://dictionary.reference.com/search?q=dereference

double dot notation, too vague -- need context.

ls ../another_dir/*
.. for "my parent"


version 10.1.0  -- double dot versioning.


double dot .. serves as a range operator (for i in 1 .. 10 )

 

SQL-3 syntax   May 15, 2005 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: AD from UK

Tom,

Thanks. My question was related to object relational features.


The note that I have with me suggests the following(I have only one slide which is not complete at 
all):

"In SQL-3 sub-components are not referenced by dereferencing operator but by double dot operator 
'..' (x..A)

ex:

select maker->president->name
from automobile
where mechanicalparts..motor='xv'         

In this example the motor attribute of the sub-object mechanical parts of cars is being accessed"

I am relating dereference with Deref (provided by oracle), am I correct ?
Is there equivalent thing for double dot notation (..) in oracle?

I have made several attempts to searching for SQL-3 syntax on the net for the above but without 
joy. Do you know any such links?

Regards



 

no database has implemented that (the SQL-3 stuff) to my knowledge as of today.  
 

Where do methods execute?   August 6, 2005 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Michael Friedman from Shanghai

I want to use Objects to pass large and complex data sets from the server to a forms client and 
then execute encapsulated methods on those objects within the client.

My plan is to create the object type on the server and have a forms package that gets the object 
type and places it into a variable in the Forms PL/SQL engine.

Starting thinking about this and started wondering - if I use this approach is the PL/SQL that 
implements the object methods automagically copied to the form?

If not, is the entire object copied down to the server so it can execute the method, thus totally 
destroying the whole performance rationale of my approach?

Or does something else happen?

I can't think of any way to test this - except for performance this copying if it happens should be 
totally transparent. 

the object code lives in the database, much like a database package lives in the database and forms 
"calls the package".  The forms developer syntactically cannot see that the package is in the 
database.

I haven't a test for it as I don't have forms anywhere, but the object type method code is in one 
place -- the database. 

So I think I understand the implications...   August 7, 2005 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Michael Friedman from Shanghai

1.  The object on the client will be copied to the server any time you execute any object method.  
Then the object will be copied back to client after the execution.

2.  Therefore, it does not make sense to use object methods on clients that implement PL/SQL unless 
the objects are small and you are willing to accept the round trip transmission to the server.

3.  However, you can still use objects on the client if all you do is access their attributes - 
they're still a good way to encapsulate jagged result sets, etc. - since that will not require 
transmission to the server to access them.

(Please tell me I'm right about #3!!!) 

3) I don't know, as I said "forms and me parted ways over a decade ago" 

PS. How to test?   August 7, 2005 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Michael Friedman from Shanghai

We have forms even if you don't.  Can you suggest a test for this? 

retrieve object
disconnect network :)
do something with object

that would be fairly conclusive. 


Or, get connected, find your session in v$session, enable 10046 level 12 trace via dbms_system, 
tail -f your trace file.  Use the form, anything something happens in client that goes to database, 
you should see activity in the trace file (end of sqlnet message from client at the very least) 

  September 29, 2005 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

How to improve object-relational select times.   May 2, 2006 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Harris from Australia

Tom,

I've got a specific example that I am having trouble in obtaining reasonable select times from an 
object-relational type.  The collection data doesn't exist in actual tables but I want to be able 
to use "select" syntax over PL/SQL "for" loop syntax because I intend to embed such select 
statements into other encompassing select's.  Trouble is, I just can't get the performance of the 
"select" from the basic object-relational component to be, even close, to that of a PL/SQL "for" 
loop.  I've tried adding MAP and ORDER functions but am not sure if these are getting invoked.  If 
you have time, could you make some suggestions regarding the "test 1" select statement in relation 
to performance improvements.  NB. if you run the test case as-is, you'll see that the "select" on 
the object-relational type takes around 10 times longer than the PL/SQL "for" loop.

Regards,

Harris.

Test case follows ...


drop type mytype1_tab;
drop type mytype1;
create type mytype1 as object(f1 number(10),
                              f2 varchar2(100),
                              map member function map1 return number,
                              pragma restrict_references(map1,
                               WNDS,RNDS,WNPS,RNPS)
);
/
show errors;

create type body mytype1 as
 map member function map1 return number
 as
 begin
  return f1;
 end;
end;
/
show errors;

create type mytype1_tab as table of mytype1;
/


set serverout on;
declare
 vMYTYPE mytype1_tab:= mytype1_tab();
 vI NUMBER;
 vJ NUMBER;
 vOFFSET NUMBER;
 vX VARCHAR2(100);
begin
  vI:= dbms_utility.get_time;
 for vC in 1 .. 100000
  loop
   vMYTYPE.extend(1);
   vMYTYPE(vMYTYPE.LAST):= mytype1(vC,'REC'||vC);
  end loop;
  vJ:= dbms_utility.get_time;
  dbms_output.put_line('Time populating 10000 records: '||(vJ-vI)/100);

  -- Test1: Running a select table() ...
  vI:= dbms_utility.get_time;
  select f2
  into vX
  from table(vMYTYPE) t
  where f1 = 99000;
  vJ:= dbms_utility.get_time;
  dbms_output.put_line('Time checking for record 99000: '||(vJ-vI)/100);
  dbms_output.put_line('Value vMYTYPE(99000) is: '||vX);

  -- Test2: PL/SQL scanning of collection ...
  vI:= dbms_utility.get_time;
  for vC in vMYTYPE.FIRST .. vMYTYPE.LAST
   loop
    if vMYTYPE(vC).f1 = 99000
     then
      --dbms_output.put_line('99000 is scanned');
      vOFFSET:= vC;
      exit;
     end if;
   end loop;
  vJ:= dbms_utility.get_time;
  dbms_output.put_line('Time scanning for record 99000: '||(vJ-vI)/100);
  dbms_output.put_line('Value vMYTYPE(99000) is: '||vMYTYPE(vOFFSET).f2);
end;
/


 

utterly incomplete example - one would not even know where to begin looking.

BUT - I don't use nested tables/collections to persist data myself, way too much stuff just happens 
by magic with them.  Much easier to model the parent/child relationship yourself using - well - 
relational tables. 

select table() vs PL/SQL for loop   May 3, 2006 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Harris from Australia

Appologies, probably should have included the results, which are;
Type dropped.


Type dropped.


Type created.

No errors.

Type body created.

No errors.

Type created.

Time populating 10000 records: .93
Time checking for record 99000: 2.99
Value vMYTYPE(99000) is: REC99000
Time scanning for record 99000: .22
Value vMYTYPE(99000) is: REC99000

PL/SQL procedure successfully completed.


Test1 and Test2 are essentially performing the same lookup function for an arbitrary record (value 
99000).  Notice that the time for the "select" is about 10 times worse than the PL/SQL "for" loop.

Regards,

Harris. 

how to insert   May 17, 2006 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Hi

I have these types


CREATE OR REPLACE
TYPE "CONTACT_ARR" AS VARRAY (3) OF CONTACT_AUTO
/

CREATE OR REPLACE
TYPE "CONTACT_AUTO" AS OBJECT (
NAME    VARCHAR2(65),
EMAIL    VARCHAR2(65)
)
/


and a table

create table emp_contact
(
empno number,
emp_details CONTACT_ARR)

I dont have a clue how to insert a row to this table, I am reading Database Application Developer's 
Guide - Object-Relational Features but I cant find a way.

Can you help me?

 

Objects in columns   August 14, 2006 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: RP from London, UK

Hi Tom,

I've read her and in your books that you don't like the idea of storing objects physically.

Overall i agree, however, I have a need to store addresses consistently across my app and i think 
creating an object type to represent addresses and then using that as a column type would be quite 
elegent?

In fact, to do it relationaly was quite ugly. If addresses are needed for different entity types 
(Person, Business, Factory, etc) then you ahve an issue with foreign key management - or creating 
seperate address tables for each entity type.

What do you think?

 

I'm not a fan of using the object types, using object types of simple scalars as columns is 
probably "safe" (no magic, no collections, no refs, etc), but you may well have to hide the 
construct in a view for many third party tools.

 

Question on multiset except on pl/sql table..please help   August 31, 2006 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Subhasis Gangopadhyay from INDIA

Hi Tom ,

I am puzzled by one pl/sql array problem.Please help me.I want to use 10g provided set operator on 
pl/sql table.


If I run below code, it is working fine.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> 
SQL> declare
  2  TYPE string1 IS TABLE OF VARCHAR2(100);
  3  lv_string1 string1:=string1('a','b','c');
  4  lv_string2 string1:=string1('a','x','c');
  5  lv_changed string1:=string1();
  6  begin
  7  lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
  8  for i in 1..lv_changed.count
  9  loop
 10  dbms_output.put_line(lv_changed(i));
 11  end loop;
 12  end;
 13  /
x

PL/SQL procedure successfully completed.

But when I run ....

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> 
SQL> desc subha1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(100)
 SAL                                                NUMBER

SQL>  select id,name,sal  from subha1;
ID NAME     SAL    
-----------------------     
1  subha    100
1  SUBH     10


SQL> set serveroutput on
SQL> declare
  2  type str1 is record (cname varchar2(100),value VARCHAR2(1000));
  3  TYPE string1 IS TABLE of str1;
  4  lv_string1 string1:=string1();
  5  lv_string2 string1:=string1();
  6  lv_changed string1:=string1();
  7  cursor c1 is
  8  select column_name from user_tab_columns
  9  where table_name='SUBHA1';
 10  curstr varchar2(32000):='select ';
 11  cnt number:=1;
 12  begin
 13  for c1_rec in c1
 14  loop
 15  lv_string1.extend;
 16  lv_string2.extend;
 17  lv_string1(cnt).cname:=c1_rec.column_name;
 18  lv_string2(cnt).cname:=c1_rec.column_name;
 19  execute immediate curstr|| lv_string1(cnt).cname ||' from subha1 where sal=100' into 
lv_string1(cnt).value;
 20  execute immediate curstr|| lv_string2(cnt).cname ||' from subha1 where sal=10' into 
lv_string2(cnt).value;
 21  dbms_output.put_line('first array -> '||lv_string1(cnt).cname||'-'||lv_string1(cnt).value);
 22  dbms_output.put_line('second array -> '||lv_string2(cnt).cname||'-'||lv_string2(cnt).value);
 23  cnt:=cnt+1;
 24  end loop;
 25  dbms_output.put_line('-----------------------------------------------------------------');
 26  lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
 27  end;
 28  /
lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
              *
ERROR at line 26:
ORA-06550: line 26, column 15:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'
ORA-06550: line 26, column 1:
PL/SQL: Statement ignored

I tried several times in different ways, but it seems whenever I run a 'multiset except' on a 
record type array(i.e. an pl/sql table built on user defined type)..it is throwing same error.

Please give me suggestions or am I missing something.Offcourse I could loop throgh one array and 
compare the values and show the differences..but I think it will be much beneficial to use set 
operator from code maintainability and preformance aspect.

Waiting for your early reply as one of my implemenation is stuck on this.

Many Thanks & regards
Subhasis Gangopadhyay 

in order for two ADT instances (complex structures, not simple scalars), you need a map/order 
member function, eg, it could look like this:

ops$tkyte%ORA10GR2> create or replace type str1 as object
  2  ( cname varchar2(100),
  3    value varchar2(1000),
  4    map member function mapper return varchar2
  5  )
  6  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type body str1
  2  as
  3  map member function mapper return varchar2
  4  is
  5  begin
  6          return cname ||'/'|| value;
  7  end;
  8  end;
  9  /

Type body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type string1 is table of str1
  2  /

Type created.

ops$tkyte%ORA10GR2> set serveroutput on
ops$tkyte%ORA10GR2> declare
  2          lv_string1 string1:=string1( str1( 'x', 1 ), str1('y',2) );
  3          lv_string2 string1:=string1( str1( 'x', 1 ), str1('y',2), str1('z',3) );
  4          lv_changed string1;
  5  begin
  6          lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
  7
  8          for i in 1 .. lv_changed.count
  9          loop
 10                  dbms_output.put_line( lv_changed(i).cname || ', ' || lv_changed(i).value );
 11          end loop;
 12  end;
 13  /
z, 3

PL/SQL procedure successfully completed.


that simple of a map function will work here - in this case - since we only really care about 
equality and not sorting.  We'd have to go for a order member function if you wanted to sort 
str1-wise (and you'd have to decide what it meant for one ADT instance to be greater than another) 

Re:Question on multiset except on pl/sql table..please help   September 1, 2006 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Subhasis Gangopadhyay from INDIA

Hi Tom,

The solution that you have given me has been most usefull.Thanks a lot for this.But I suppose I 
have to create types(though not a very big problem) ..I can not use pl/sql types declared in code 
itself(as I have to create member function)..or otherwise use dynamic sqls to create types in the 
code itself.

Also,secondly just one thing coming to my mind from your answer.You have mentioned that if I want 
to sort the contents then I have to use order member function.If you are specifying that for 
'multiset union' it is required, then there is no problem(please see the below code)in running the 
code but it is working like sql operator 'union all' but not like 'union' to show the distinct 
combination.Can you please cite an small example to show the use of 'order' member function to 
solve the above requirement.

  1  declare
  2  type str1 is record (cname varchar2(100),value VARCHAR2(1000));
  3  TYPE string_new IS TABLE of str1;
  4  lv_string_new string_new:=string_new();
  5  lv_string2 string_new:=string_new();
  6  lv_changed string_new:=string_new();
  7  cursor c1 is
  8  select column_name from user_tab_columns
  9  where table_name='SUBHA1';
 10  curstr varchar2(32000):='select ';
 11  cnt number:=1;
 12  begin
 13  for c1_rec in c1
 14  loop
 15  lv_string_new.extend;
 16  lv_string2.extend;
 17  lv_string_new(cnt).cname:=c1_rec.column_name;
 18  lv_string2(cnt).cname:=c1_rec.column_name;
 19  execute immediate curstr|| lv_string_new(cnt).cname ||' from subha1 where sal=10' into 
lv_string_new(cnt).value;
 20  execute immediate curstr|| lv_string2(cnt).cname ||' from subha1 where sal=10' into 
lv_string2(cnt).value;
 21  dbms_output.put_line('first array -> 
'||lv_string_new(cnt).cname||'-'||lv_string_new(cnt).value);
 22  dbms_output.put_line('second array -> '||lv_string2(cnt).cname||'-'||lv_string2(cnt).value);
 23  cnt:=cnt+1;
 24  end loop;
 25  dbms_output.put_line('-----------------------------------------------------------------');
 26  lv_changed := lv_string2 MULTISET union lv_string_new;
 27  for i in 1..lv_changed.count
 28  loop
 29  dbms_output.put_line(lv_changed(i).cname||'-'||lv_changed(i).value);
 30  end loop;
 31* end;
SQL> /
first array -> ID-1
second array -> ID-1
first array -> NAME-SUBH
second array -> NAME-SUBH
first array -> SAL-10
second array -> SAL-10
-----------------------------------------------------------------
ID-1
NAME-SUBH
SAL-10
ID-1
NAME-SUBH
SAL-10

PL/SQL procedure successfully completed.

From the above output it can be seen that it is not picking the distinct combination of values.An 
example will help very much like always you do.

Many thanks & Regards
Subhasis Gangopadhyay
 

check out the application developers guide for object relational features - lots of examples in 
there.

the multiset operators support the use of distinct and all as well. 

Applying String Function ob Object Type (without using Object Method)   August 1, 2009 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Rupali from NJ, USA

Hi Tom,

For my problem, I have seen many questions, but none of the questions was matching my requirement. 
I thought the question I am responding to talks something about it, hence adding it here. 
The assignment is very crucial for my project, please help me.
=================================
In my current assignment, we have 

Object Type
CREATE OR REPLACE TYPE MESSAGEHEADER_TY AS OBJECT( 
MESSAGETYPE    VARCHAR2(16), 
MESSAGEDATE    NUMBER(14))
/
 
Table is based on above Object Type
CREATE TABLE MESSAGES
(
  MSG_ID                NUMBER                 NOT NULL,
  MESSAGEHEADER         MESSAGEHEADER_TY 
)
/
 
Records in the Messages table are
 
MSG_ID    MESSAGEHEADER
=========================
100320    (ichicsr, 2)
100321    (ichicsr, 2)
100322    (ichicsr, 2)
100330    (ichicsr, 2)
100331    (xyzcsr, 2)
100332    (pqrcsr, 2)

Requirement is cut first word from Messageheader column that is "ichicsr" or "xyzcsr" or "pqrcsr". 
And then do lookup on some other table based on this value.

Above tables are in Source schema, hence I do have only SELECT privileges. Hence I can not create 
Object Method to apply string functions on object type data.

Since the table has object type column, I can access it over DB Links, nor can apply CTAS.

Can you please suggest me way for this?

regards
Rupali

oracle   October 5, 2009 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: ebtehal from jordan

what deos oracle depend on ? relational model or object oriented model?

http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/intro.htm#i68236

  October 20, 2009 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Tom,
What business problem did oracle solve using object relational model?