?
Solved

Insert a UROWID into a ANYDATA table column

Posted on 2006-06-25
3
Medium Priority
?
1,089 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 500 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 500 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

801 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