This forum is currently locked. You can't register or post questions at this time. (read more)

SYS.ANYDATA to BLOB

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

mano7033
Member
Posts: 17
Joined: Tue Aug 23, 2011 12:59 pm

SYS.ANYDATA to BLOB

Postby mano7033 » Mon Feb 27, 2012 10:34 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SYS.ANYDATA to BLOB

Postby Tim... » Mon Feb 27, 2012 11:25 am

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SYS.ANYDATA to BLOB

Postby Tim... » Mon Feb 27, 2012 11:51 am

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SYS.ANYDATA to BLOB

Postby Tim... » Mon Feb 27, 2012 3:40 pm

Hi.

I wrote up a little note about it:

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron