Solved

Insert a UROWID into a ANYDATA table column

Posted on 2006-06-25
3
999 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now