SYS.ANYDATA to BLOB

[mano7033]

How to convert a SYS.ANYDATA object to BLOB?

or any user defined type to BLOB?

The following is my requirement. I need to wrap any of my user-defined type to SYS.AQ$_JMS_OBJECT_MESSAGE.
SYS.AQ$_JMS_OBJECT_MESSAGE has the following fields.
SQL> desc SYS.AQ$_JMS_OBJECT_MESSAGE;
Name Null? Type
----------------------------------------- -------- ----------------------------
HEADER AQ$_JMS_HEADER
BYTES_LEN NUMBER(38)
BYTES_RAW RAW(2000)
BYTES_LOB BLOB

[Tim...]

Hi.

A quick look at the manual suggest you should try these.

Code: Select all

SET SERVEROUTPUT ON

DECLARE
  l_blob    BLOB;
  l_anydata ANYDATA;
  l_result  PLS_INTEGER;
BEGIN
  l_blob := UTL_RAW.cast_to_raw('This is some data');
 
  -- Convert BLOB to ANYDATA
  l_anydata := ANYDATA.convertBlob(l_blob);
 
  -- Convert ANYDATA to BLOB
  l_result := l_anydata.getBlob(l_blob);
 
  DBMS_OUTPUT.put_line(UTL_RAW.cast_to_varchar2(l_blob));
END;
/
This is some data

PL/SQL procedure successfully completed.

test@db11g>

http://docs.oracle.com/cd/E11882_01/app ... anydat.htm
http://docs.oracle.com/cd/E11882_01/ser ... #ADQUE2200

Never tried to serialize a user defined type as a blob, so I'm not really sure what I would do in that case.

Cheers

Tim...

[Tim...]

Hi.

Just tried this and it seems to work fine.

Code: Select all

CREATE OR REPLACE TYPE t_my_type AS OBJECT (
  g_val1 VARCHAR2(10),
  g_val2 VARCHAR2(10)
);
/

SET SERVEROUTPUT ON

DECLARE
  l_obj     t_my_type := t_my_type('1111111111', '2222222222');
  l_anydata ANYDATA;
  l_result  PLS_INTEGER;
BEGIN
  -- Convert Object to ANYDATA
  l_anydata := ANYDATA.convertObject(l_obj);
 
  -- Convert ANYDATA to BLOB
  l_result := l_anydata.getObject(l_obj);
 
  DBMS_OUTPUT.put_line(l_obj.g_val1 || ' : ' || l_obj.g_val2);
END;
/
1111111111 : 2222222222

PL/SQL procedure successfully completed.

test@db11g>

Cheers

Tim...

[Tim...]

Hi.

I wrote up a little note about it:

http://www.oracle-base.com/articles/misc/anydata.php

Cheers

Tim...