Solved

Insert a UROWID into a ANYDATA table column

Posted on 2006-06-25
3
1,050 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

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 …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

730 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