Home » SQL & PL/SQL » SQL & PL/SQL » REF to table of object (10.2.0.1)
REF to table of object [message #309132] Wed, 26 March 2008 09:23 Go to next message
peeterek
Messages: 9
Registered: March 2008
Junior Member
hi,

Is it possible to use references to values of table of objects???

CREATE OR REPLACE TYPE A_type AS OBJECT (
some_value VARCHAR2(10)
);

CREATE TABLE tmp_tbl (
some_field A_TYPE
);

INSERT INTO tmp_tbl VALUES(A_TYPE('some'));


Now when I want to populate v_atype with an object everything works fine:

DECLARE 
v_atype  a_type;
BEGIN
	SELECT 
		pp.some_field INTO v_atype
	FROM 
		tmp_tbl pp 
	WHERE 
		pp.some_field.some_value='some';
END;
/


But when I want to put REF into a v_atype Oracle returns an error

DECLARE 
v_atype REF a_type;
BEGIN
	SELECT 
		REF(pp.some_field) INTO v_atype
	FROM 
		tmp_tbl pp 
	WHERE 
		pp.some_field.some_value='some';
END;
/


So, is it possible or maybe I'm doing something wrong:\

thank you in advance,
Re: REF to table of object [message #309149 is a reply to message #309132] Wed, 26 March 2008 11:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I believe you need a little different syntax for your tmp_tbl creation, so that you have an object table, instead of a table containing an object. Please see the demonstration below.


SCOTT@orcl_11g> CREATE OR REPLACE TYPE a_type AS OBJECT
  2    (some_field  VARCHAR2 (10),
  3  	other_field VARCHAR2 (10));
  4  /

Type created.

SCOTT@orcl_11g> CREATE TABLE tmp_tbl OF a_type
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (a_type ('some1', 'other1'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (a_type ('some2', 'other2'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT pp.* FROM tmp_tbl pp
  2  /

SOME_FIELD OTHER_FIEL
---------- ----------
some1      other1
some2      other2

SCOTT@orcl_11g> SELECT REF (pp) FROM tmp_tbl pp
  2  /

REF(PP)
--------------------------------------------------------------------------------
00002802095864CBD4F67947838822EEFEECC1A08100B7EDCEDBEE4E1AB7ED52CBB456702601032D
1C0000

0000280209CE52F7F96FAC4E01AF9172EDE9705DEF00B7EDCEDBEE4E1AB7ED52CBB456702601032D
1C0001


SCOTT@orcl_11g> DECLARE
  2    v_atype	a_type;
  3  BEGIN
  4    SELECT a_type (pp.some_field, pp.other_field)
  5    INTO   v_atype
  6    FROM   tmp_tbl pp
  7    WHERE  pp.some_field = 'some1';
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> DECLARE
  2    v_atype REF a_type;
  3  BEGIN
  4    SELECT REF (pp)
  5    INTO   v_atype
  6    FROM   tmp_tbl pp
  7    WHERE  pp.some_field = 'some1';
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: REF to table of object [message #309185 is a reply to message #309149] Wed, 26 March 2008 14:21 Go to previous messageGo to next message
peeterek
Messages: 9
Registered: March 2008
Junior Member
hi,

the solution above is good but I can not create object table because of problem shown here:
http://www.orafaq.com/forum/m/308132/121340/


so I have a table of objects :\

best regards,
Peeter
Re: REF to table of object [message #309210 is a reply to message #309185] Wed, 26 March 2008 16:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following is intended as a solution to the combined problems. It involves changing the initial type to use a varray to solve the nested table storage clause problem, then everything else can work.

SCOTT@orcl_11g> create or replace type A as varray(100) of varchar2(10);
  2  /

Type created.

SCOTT@orcl_11g> create or replace type B as object (
  2  id_B number(1),
  3  zm_B A
  4  );
  5  /

Type created.

SCOTT@orcl_11g> create or replace type C as object (
  2  id_CC number(1),
  3  MEMBER FUNCTION SHOW RETURN VARCHAR2
  4  )NOT FINAL;
  5  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY C AS
  2    MEMBER FUNCTION show RETURN VARCHAR2 IS
  3    BEGIN
  4  	 RETURN 'object C';
  5    END;
  6  END;
  7  /

Type body created.

SCOTT@orcl_11g> create or replace type D under C (
  2  id_D number(1),
  3  zm_D B,
  4  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2
  5  );
  6  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY D AS
  2    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  3    BEGIN
  4  	 RETURN 'object D';
  5    END;
  6  END;
  7  /

Type body created.

SCOTT@orcl_11g> create table tmp_tbl of c
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (c (1))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (d (1, 2, b (3, a ('some', 'more'))))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT p.show() FROM tmp_tbl p
  2  /

P.SHOW()
----------------------------------------------------------------------------------------------------
object C
object D

SCOTT@orcl_11g> SELECT REF (p) FROM tmp_tbl p
  2  /

REF(P)
----------------------------------------------------------------------------------------------------
0000280209E42A3B7B677E444EB4EE0FF435EBC67EFECBE0649D1B4191B6A543C66663553D010327840000
0000280209C30FE9DBD6CB4851A3130324C150D80EFECBE0649D1B4191B6A543C66663553D010327840001

SCOTT@orcl_11g> SELECT TREAT (VALUE (p) AS d) FROM tmp_tbl p WHERE VALUE (p) IS OF (d)
  2  /

TREAT(VALUE(P)ASD)(ID_CC, ID_D, ZM_D(ID_B, ZM_B))
----------------------------------------------------------------------------------------------------
D(1, 2, B(3, A('some', 'more')))

SCOTT@orcl_11g> SELECT TREAT (VALUE (p) AS d).zm_d.zm_b FROM tmp_tbl p WHERE VALUE (p) IS OF (d)
  2  /

TREAT(VALUE(P)ASD).ZM_D.ZM_B
----------------------------------------------------------------------------------------------------
A('some', 'more')

SCOTT@orcl_11g> SELECT t.*
  2  FROM   tmp_tbl p,
  3  	    TABLE (TREAT (VALUE (p) AS d).zm_d.zm_b) t
  4  /

COLUMN_VAL
----------
some
more

SCOTT@orcl_11g> SELECT REF (p)
  2  FROM   tmp_tbl p,
  3  	    TABLE (TREAT (VALUE (p) AS d).zm_d.zm_b) t
  4  WHERE  t.column_value = 'some'
  5  /

REF(P)
----------------------------------------------------------------------------------------------------
0000280209C30FE9DBD6CB4851A3130324C150D80EFECBE0649D1B4191B6A543C66663553D010327840001

SCOTT@orcl_11g> DECLARE
  2    v_c REF c;
  3  BEGIN
  4    SELECT REF (p) INTO v_c
  5    FROM   tmp_tbl p,
  6  	      TABLE (TREAT (VALUE (p) AS d).zm_d.zm_b) t
  7    WHERE  t.column_value = 'some';
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: REF to table of object [message #309226 is a reply to message #309210] Wed, 26 March 2008 18:24 Go to previous message
peeterek
Messages: 9
Registered: March 2008
Junior Member
I am soOOoo grateful Smile
Thank You very much for Your time.

I have made necessary changes in my base and everything works fine Smile


but I am still curious about those nested tables Cool hahahahah


best regards,
Peeter
Previous Topic: Get session to ignore commits and rollbacks
Next Topic: bind variable error...WHY?
Goto Forum:
  


Current Time: Wed Dec 07 04:52:30 CST 2016

Total time taken to generate the page: 0.06181 seconds