CONNECT SCOTT/TIGER;

drop TABLE jdbc20_refs;
drop TABLE jdbc20_tab;
drop TYPE jdbc20_ty force;

CREATE TYPE jdbc20_ty AS OBJECT
( 
  num     NUMBER,
  b       BLOB, 
  c       CLOB 
);
/

CREATE TABLE jdbc20_tab OF jdbc20_ty;
INSERT INTO jdbc20_tab
       VALUES (jdbc20_ty(2, NULL, EMPTY_CLOB()));
INSERT INTO jdbc20_tab
       VALUES (jdbc20_ty(8, EMPTY_BLOB(), NULL));
INSERT INTO jdbc20_tab
       VALUES (jdbc20_ty(9, EMPTY_BLOB(), EMPTY_CLOB()));

CREATE TABLE jdbc20_refs
( 
  first     REF  jdbc20_ty,
  second    REF  jdbc20_ty,
  id        NUMBER
);
INSERT INTO jdbc20_refs (id) VALUES (1);
UPDATE jdbc20_refs
 SET first  =  (SELECT REF(d) FROM jdbc20_tab d WHERE d.num = 2),
     second =  (SELECT REF(d) FROM jdbc20_tab d WHERE d.num = 9)
 WHERE id = 1;

INSERT INTO jdbc20_refs (id) VALUES (999);
UPDATE jdbc20_refs
 SET first  =  (SELECT REF(d) FROM jdbc20_tab d WHERE d.num = 1),
     second =  (SELECT REF(d) FROM jdbc20_tab d WHERE d.num = 8)
 WHERE id = 999;


COMMIT;

/

EXIT;
