RE: Oracle Type Query

try using nested tables. using your tables and type it would look like:

create or replace type itskills as object
  (skillname varchar2(100),
   expmonths number);
create or replace type skill_tab_type as table of itskills;
create table swdv
  (swdvid number,
   swname varchar2(100),
   skills   skill_tab_type)
   nested table skills store as skills_nested;

what is different here from yours is the inclusion of "skill_tab_type" and 
  "nested table skills store as skills_nested"

to select from it:
    select d.swdvid,d.swname,skills.*
     from swdv d, table(d.skills) skill

 your output would like:  swdvid   swname  skillname  expmonths
                             100     ms     prog         10
                             100     ms     engineer     12
                             200     oracle developer    10
                             200     oracle dba          20
                etc....

to insert:
     insert into table (select skills from swdv where swdvid = 100)
       values ('software engineer',20);
to delete:
     delete from table (select skills from swdv where swdvid = 100)
     where skillname = 'prog';

another select:
    select d.swname,skillname from swdv d, table(d.skills) s
      where d.swdvid in (100, 200);
    
      output:   ms     prog
                ms     engineer
                oracle developer
                oracle dba

to update:
     unfortunately this is very complex. you will have to create a "instead
of update" trigger
     on swdv.


-----Original Message-----
From: [email protected]
[mailto:[email protected]]
Sent: Tuesday, January 28, 2003 11:03 PM
To: Oracle
Subject: [oracle] Oracle Type Query


I have a user defined type in my database schema (Oracle 8i).
I created a table based on thsi user type. 
I was able to insert data in the database. 
However I want to enter multiple data in the udt column.
How do I dod that?

Eg - 
Create Type ITSKILLS(SKILLNAME VARCHAR2(100), EXPMONTHS NUMBER) AS OBJECT;
Create Table SWDV( SWDVID NUMBER, SWNAME VARCHAR2(100), SWSKILL ITSKILLS);

How do I insert multiple rows in the SWSKILL column in the SWDV table?

Another question I have is how do I retrieve this in an ADO recordset, if I
am 
using Visual Basic?

Thanks & Regards,
Abhijit



-------------------------------------------------
This mail sent through Nucsoft: http://nucsoft.co.in/


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].