Solved

Insert a UROWID into a ANYDATA table column

Posted on 2006-06-25
3
1,039 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:johan777
  • 2
3 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16980834
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;
/
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 250 total points
ID: 16981344
-- iNSERTS ROWID DATA INTO sys.anydata table

declare
t sys.anydata;
urowid_fld ROWID := chartorowid('AAAMaHAAEAAAAIHAAZ');
begin
t := sys.anydata.CONVERTvarchar2(rowidtochar(urowid_fld));
if t.gettypename='SYS.VARCHAR2'
THEN
insert into test_anydata(fld) values (t);
END IF;
end;
/


-- Select value from the table and convert to rowid

declare
v_str varchar2(100);
urowid_fld ROWID;
BEGIN
for c1 in(select * from test_anydata)
LOOP
if ( c1.fld.getVarchar2(v_str) = dbms_types.success )
THEN
dbms_output.put_line(v_str);
urowid_fld := chartorowid(v_str);
END IF;
END LOOP;
END;
/
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 250 total points
ID: 16981387
to  MohanKNair:

       Again, an implicit cast has been done and you are not having ROWID anymore.

       Also, it should be "UROWID", not "ROWID".
 
       as I quoted, 10g supports UROWID.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question