Home » SQL & PL/SQL » SQL & PL/SQL » Using oracle object (oracle 11g)
Using oracle object [message #633427] Wed, 18 February 2015 09:51 Go to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member
created the employee table as

create table employee (id number, first_name varchar2(50))

Created the object as

CREATE TYPE rec_type AS OBJECT (eid VARCHAR2(25), ename VARCHAR2(15))

create type rec_type_collection is table of rec_type

Now how can i read the value to my procedure and insert to employee table.

below one is not working

PROCEDURE print_rec_type (rec rec_type_collection) IS
BEGIN

DBMS_OUTPUT.PUT_LINE(rec.eid);
DBMS_OUTPUT.PUT_LINE(rec.ename);
END;


Report message to a moderator

Re: Using oracle object [message #633428 is a reply to message #633427] Wed, 18 February 2015 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Now how can i read the value to my procedure and insert to employee table.

Read the value of what exactly?

you can not INSERT rec_type_collection into EMPLOYEE table due to datatype mismatch.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Using oracle object [message #633429 is a reply to message #633427] Wed, 18 February 2015 10:00 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
What element of rec.eid do you want to display? I will give you some hints.

you will need a loop
read up on how to use collections (try google for "oracle collections")

Report message to a moderator

Re: Using oracle object [message #633430 is a reply to message #633429] Wed, 18 February 2015 10:05 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member
created the employee table as

create table employee (id number, first_name varchar2(50));

--Created the object as 

CREATE TYPE rec_type AS OBJECT (eid VARCHAR2(25), ename VARCHAR2(15)) 

create type rec_type_collection is table of rec_type


Now how can i read the value to my procedure and insert to employee table.

below one is not working 
PROCEDURE print_rec_type (rec rec_type_collection) IS
 BEGIN

        DBMS_OUTPUT.PUT_LINE(rec.eid);
        DBMS_OUTPUT.PUT_LINE(rec.ename);
 END;

Report message to a moderator

Re: Using oracle object [message #633431 is a reply to message #633430] Wed, 18 February 2015 10:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
And what about the data? Why do you think that just declaring a TYPE would suffice to fetch the required rows from the table? You are way behind even understanding the basics.

Report message to a moderator

Re: Using oracle object [message #633432 is a reply to message #633431] Wed, 18 February 2015 10:18 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member
yes, its something new to me, can you help how can i achieve this

Report message to a moderator

Re: Using oracle object [message #633433 is a reply to message #633430] Wed, 18 February 2015 10:24 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
When you say "not working" it helps us better if you Copy & Paste the actual error. i.e. ORA-XXXXX

Report message to a moderator

Re: Using oracle object [message #633434 is a reply to message #633433] Wed, 18 February 2015 10:31 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member
its PLS-00302 component EID must be declared

Report message to a moderator

Re: Using oracle object [message #633435 is a reply to message #633434] Wed, 18 February 2015 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"rec" is not a record it is an array of records (at least you declared it as it).

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Report message to a moderator

Re: Using oracle object [message #633447 is a reply to message #633435] Wed, 18 February 2015 16:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
You need to read up on collections and object types.

insert into sometable
select a.*
from table(cast(somevariable as somecollectiontype)) a
/


Basically you use Oracle functions to convert the collection to a set of rows and then do the normal stuff with the row set. This is of course all explained in the manuals. Plus there are numerous examples on the WEB that can be found by searching.

One more thing... once you find a solution, don't forget to come back and post it so that others can see.

Observe the following. In particular, pay attention to all the nesting and use of open/close parenthesis. These are all required and is one of the reasons learning this particular stuff can be frustrating.

17:55:09 SQL> drop type rec_type_collection
17:55:16   2  /

Type dropped.

Elapsed: 00:00:00.00
17:55:16 SQL> drop type rec_type
17:55:16   2  /

Type dropped.

Elapsed: 00:00:00.00
17:55:16 SQL> drop table employee
17:55:16   2  /

Table dropped.

Elapsed: 00:00:00.00
17:55:16 SQL>
17:55:16 SQL> create table employee (id number, first_name varchar2(50))
17:55:16   2  /

Table created.

Elapsed: 00:00:00.00
17:55:16 SQL>
17:55:16 SQL> CREATE TYPE rec_type AS OBJECT (eid VARCHAR2(25), ename VARCHAR2(15))
17:55:16   2  /

Type created.

Elapsed: 00:00:00.01
17:55:16 SQL>
17:55:16 SQL> create type rec_type_collection is table of rec_type
17:55:16   2  /

Type created.

Elapsed: 00:00:00.00
17:55:16 SQL>
17:55:16 SQL> select 1 c1,'Michel Cadot' c2 from dual union all
17:55:16   2  select 2,'manns' from dual union all
17:55:16   3  select 3,'gazzag' from dual union all
17:55:16   4  select 4,'Lalit Kumar B' from dual union all
17:55:16   5  select 5,'Bill B' from dual union all
17:55:16   6  select 6,'BlackSwan' from dual
17:55:16   7  /

        C1 C2
---------- -------------
         1 Michel Cadot
         2 manns
         3 gazzag
         4 Lalit Kumar B
         5 Bill B
         6 BlackSwan

6 rows selected.

Elapsed: 00:00:00.01
17:55:16 SQL>
17:55:16 SQL> set linesize 80
17:55:16 SQL>
17:55:16 SQL> select cast(multiset(select * from (
17:55:16   2                                       select 1 c1,'Michel Cadot' c2 from dual union all
17:55:16   3                                       select 2,'manns' from dual union all
17:55:16   4                                       select 3,'gazzag' from dual union all
17:55:16   5                                       select 4,'Lalit Kumar B' from dual union all
17:55:16   6                                       select 5,'Bill B' from dual union all
17:55:16   7                                       select 6,'BlackSwan' from dual
17:55:16   8                                     )
17:55:16   9                      ) as rec_type_collection
17:55:16  10             ) data_as_a_collection
17:55:16  11  from dual
17:55:16  12  /

DATA_AS_A_COLLECTION(EID, ENAME)
--------------------------------------------------------------------------------
REC_TYPE_COLLECTION(REC_TYPE('1', 'Michel Cadot'), REC_TYPE('2', 'manns'), REC_T
YPE('3', 'gazzag'), REC_TYPE('4', 'Lalit Kumar B'), REC_TYPE('5', 'Bill B'), REC
_TYPE('6', 'BlackSwan'))


1 row selected.

Elapsed: 00:00:00.00
17:55:16 SQL>
17:55:16 SQL> insert into employee
17:55:16   2  select a.*
17:55:16   3  from table(cast(
17:55:16   4                  (
17:55:16   5                   select cast(multiset(select * from (
17:55:16   6                                                        select 1 c1,'Michel Cadot' c2 from dual union all
17:55:16   7                                                        select 2,'manns' from dual union all
17:55:16   8                                                        select 3,'gazzag' from dual union all
17:55:16   9                                                        select 4,'Lalit Kumar B' from dual union all
17:55:16  10                                                        select 5,'Bill B' from dual union all
17:55:16  11                                                        select 6,'BlackSwan' from dual
17:55:16  12                                                      )
17:55:16  13                                       ) as rec_type_collection
17:55:16  14                              ) data_as_a_collection
17:55:16  15                   from dual
17:55:16  16                  ) as rec_type_collection
17:55:16  17                )
17:55:16  18           ) a
17:55:16  19  /

6 rows created.

Elapsed: 00:00:00.00
17:55:16 SQL>
17:55:16 SQL> select *
17:55:16   2  from employee
17:55:16   3  /

        ID FIRST_NAME
---------- --------------------------------------------------
         1 Michel Cadot
         2 manns
         3 gazzag
         4 Lalit Kumar B
         5 Bill B
         6 BlackSwan

6 rows selected.

Elapsed: 00:00:00.01
17:55:16 SQL>


HAVE FUN! Razz Kevin

Report message to a moderator

Re: Using oracle object [message #633509 is a reply to message #633447] Thu, 19 February 2015 14:23 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Kevin,
wish I could give you a star for that writeup. Very nice and I'm sure a great help to the OP.

Report message to a moderator

Re: Using oracle object [message #633523 is a reply to message #633509] Fri, 20 February 2015 02:40 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Agreed. We need a "like" button Smile

Report message to a moderator

Re: Using oracle object [message #633528 is a reply to message #633427] Fri, 20 February 2015 11:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can also use COLLECT:

SQL> with t as (
  2             select 1 c1,'Michel Cadot' c2 from dual union all
  3             select 2,'manns' from dual union all
  4             select 3,'gazzag' from dual union all
  5             select 4,'Lalit Kumar B' from dual union all
  6             select 5,'Bill B' from dual union all
  7             select 6,'BlackSwan' from dual
  8            )
  9  select  cast(
 10               collect(
 11                       rec_type(
 12                                c1,
 13                                c2
 14                               )
 15                       )
 16               as rec_type_collection
 17              ) data_as_a_collection
 18    from  t
 19  /

DATA_AS_A_COLLECTION(EID, ENAME)
--------------------------------------------------------------------------------
REC_TYPE_COLLECTION(REC_TYPE('1', 'Michel Cadot'), REC_TYPE('2', 'manns'), REC_T
YPE('3', 'gazzag'), REC_TYPE('4', 'Lalit Kumar B'), REC_TYPE('5', 'Bill B'), REC
_TYPE('6', 'BlackSwan'))


SQL>


SY.

[Updated on: Fri, 20 February 2015 11:49]

Report message to a moderator

Re: Using oracle object [message #633529 is a reply to message #633528] Fri, 20 February 2015 11:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Nice, I never would have considered that.

Report message to a moderator

Re: Using oracle object [message #633866 is a reply to message #633529] Thu, 26 February 2015 10:11 Go to previous message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member
Thank you all

Report message to a moderator

Previous Topic: Nested table as in parameter
Next Topic: how to return a row based on count using multiple tables
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 04:53:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.