Solved

Insert a UROWID into a ANYDATA table column

Posted on 2006-06-25
3
1,024 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.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

813 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

9 Experts available now in Live!

Get 1:1 Help Now