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(uro wid_fld));
c_fld := NULL;
SELECT to_clob(anydata.accessurow id(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
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(uro
c_fld := NULL;
SELECT to_clob(anydata.accessurow
Dbms_Output.put_line ('UROWID - ' || c_fld);
commit;
END;
Regards,
Js
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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('UROW
END;
/