Link to home
Start Free TrialLog in
Avatar of johan777
johan777

asked on

Insert a UROWID into a ANYDATA table column

Hi,

Please note this is 1 of a few almost identical questions, but on different data types.

I need to take the value of a UROWID and store it within a ANYDATA data type column. Obviously, I also need to retrieve. First prize is to get it back as the original data type, but if this is not possible, to get it back as a clob data type.

I include an example I am working on, and would like help to get the script to work.
-------------------------------------------
--CREATE TABLE test_anydata (
--  fld anydata NOT NULL
--);

DECLARE
  c_fld CLOB := NULL;
  urowid_fld ROWID := 'AAAMaHAAEAAAAIHAAZ';
BEGIN
  DELETE FROM test_anydata;
--UROWID
  INSERT INTO test_anydata (fld) VALUES (anydata.converturowid(urowid_fld));
  c_fld := NULL;
  SELECT to_clob(anydata.accessurowid(fld)) INTO c_fld FROM test_anydata WHERE anydata.gettypename(fld) LIKE 'SYS.UROWID';
  Dbms_Output.put_line ('UROWID - ' || c_fld);
  commit;
END;

Regards,
Js
Avatar of Acton Wang
Acton Wang
Flag of United States of America image

urowid is not in oracle 9i only in oracle 10g:

use this to test it if you are in oracle 10g:

DECLARE
   uid_value UROWID := NULL;
   uid UROWID := 'AAAMaHAAEAAAAIHAAZ';
BEGIN


  DELETE FROM test_anydata;
  INSERT INTO test_anydata (fld) VALUES (anydata.converturowid(uid));
  commit;

  SELECT anydata.accessurowid(fld) INTO uid_value FROM test_anydata;
  DBMS_OUTPUT.PUT_LINE('UROWID - ' || uid_value);


END;
/
ASKER CERTIFIED SOLUTION
Avatar of MohanKNair
MohanKNair

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial