Solved

Insert a BLOB into a ANYDATA table column

Posted on 2006-06-25
19
727 Views
Last Modified: 2008-01-09
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 BLOB 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;
  blob_fld NCLOB := 'CCCCCC';
BEGIN
  DELETE FROM test_anydata;
--NCLOB
  INSERT INTO test_anydata (fld) VALUES (anydata.convertnclob(blob_fld));
  c_fld := NULL;
  SELECT to_clob(anydata.accessblob(fld)) INTO c_fld FROM test_anydata WHERE anydata.gettypename(fld) LIKE 'SYS.BLOB';
  Dbms_Output.put_line ('BLOB - ' || c_fld);
  commit;
END;

Regards,
Js
0
Comment
Question by:johan777
  • 7
  • 6
  • 6
19 Comments
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
as clob, it is not supported in oracle 9i, you will get:
ORA-22370: incorrect usage of method AnyData Insert
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
if in oracle 10g, you would try as:

DECLARE
  b_value BLOB := NULL;
  blob_fld BLOB := HEXTORAW('FFFF');
BEGIN
  delete from  test_anydata;
  INSERT INTO test_anydata VALUES (anydata.convertblob(blob_fld));
  commit;

  SELECT anydata.accessblob(fld) INTO b_value FROM test_anydata;
END;
/
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
0
 
LVL 19

Accepted Solution

by:
actonwang earned 250 total points
Comment Utility
See my comments. in 10g, "bugs" in 9i should be fixed. try my example there.
0
 

Author Comment

by:johan777
Comment Utility
Hi,

Thanx for the help so far!

I only now saw that I've got an error in my initial script:

INSERT INTO test_anydata (fld) VALUES (anydata.convertnclob(blob_fld));
.. should have been ..
  INSERT INTO test_anydata (fld) VALUES (anydata.convertblob(blob_fld));  <-- The function convertBlob exists in the definitions

However, when I try to execute the script, I get:
Line,Pos,Text
7,50,ORA-06550: line 7, column 50:
PLS-00306: wrong number or types of arguments in call to 'CONVERTBLOB'
ORA-06550: line 7, column 42:
PL/SQL: ORA-00904: "ANYDATA"."CONVERTBLOB": invalid identifier
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored

It seems I'm still stuck. I'm trying to use the functions as defined by Oracle and it seems they are not working.

Regards,
Js
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
The type anydata is owned by SYS
Try SYS.anydata.convertnclob instead of anydata.convertnclob
0
 

Author Comment

by:johan777
Comment Utility
Hi MohanKNair,

I have, no difference. It is still giving me the errors.

Regards,
Js
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
Who owns the TYPE anydata? Try this query

select owner, object_type from dba_objects
where object_name='ANYDATA'
/
0
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.

 

Author Comment

by:johan777
Comment Utility
select owner, object_type from dba_objects
where object_name='ANYDATA'
/


Result:
OWNER,OBJECT_TYPE
SYS,TYPE
SYS,TYPE BODY
PUBLIC,SYNONYM
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
See whether the following SQL works. It gives output from Oracle 9.2 as

SQL> select sys.anydata.accessnclob(sys.anydata.convertnclob('Sample NCLOB Conversion to anydata')) s from dual;

S
--------------------------------------------
Sample NCLOB Conversion to anydata

SQL>
0
 

Author Comment

by:johan777
Comment Utility
Nope.

I get ORA-24806: LOB form mismatch.

Could it be that the function was'nt properly defined, or that the header differs to the body?

0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
There is difference in character set between the Oracle server and the client machine from where the command is issued. Invoke sql*plus from the server and see the difference.
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 250 total points
Comment Utility
Find the server character set value

SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTER_SET';

Set the NLS_LANG environment variable at the client machine also.

Also see this link
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1224836384599#6220131326137
0
 

Author Comment

by:johan777
Comment Utility
ok,

Using SQLPlus, setting the NLS_LANG parameter the same as the server, I got the following to work.

select sys.anydata.accessnclob(sys.anydata.convertnclob('Sample NCLOB Conversion to anydata')) s from dual;

Result:
S
--------------------------------------------------------------------------------
Sample NCLOB Conversion to anydata

BUT !!

the following still does not work!

DECLARE
  c_fld CLOB := NULL;
  nclob_fld NCLOB := 'CCCCCC';
BEGIN
  DELETE FROM test_anydata;
  INSERT INTO test_anydata (fld) VALUES (anydata.convertnclob(nclob_fld));
  commit;

  SELECT anydata.accessnclob(fld) INTO c_fld FROM test_anydata;
  Dbms_Output.put_line ('NCLOB - ' || c_fld);
END;                      

Result:
--------
ERROR at line 1:
ORA-22370: incorrect usage of method AnyData Insert
ORA-06512: at line 6

Regards,
Js
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
try the following.

DECLARE
  c_fld CLOB := NULL;
  nclob_fld NCLOB := 'CCCCCC';
  t ANYDATA;
BEGIN
  t := anydata.convertnclob(nclob_fld);
  DBMS_OUTPUT.PUT_LINE('test - ' || t.gettypename());  -- if you see this, the function convertnclob is working.

  DELETE FROM test_anydata;
  INSERT INTO test_anydata (fld) VALUES (t); --if error here. that means the storage is still not supported there ..
  commit;

  SELECT anydata.accessnclob(fld) INTO c_fld FROM test_anydata;
  Dbms_Output.put_line ('NCLOB - ' || c_fld);
END;              
/
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
see my comments and links in post of CLOB and you have the same situation.
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
if you have metalink, open a ticket there to ask for help from oracle support.
0
 

Author Comment

by:johan777
Comment Utility
Hi all,

I got it figured out! Up to Oracle 10g R2, although some of the functions have enven been defined, DO NOT WORK. Blob, Clob and NClob only works for the BFile scenario's.

I'm splitting the points as you were both very helpfull. I wish I could allocate the points to both.

Regards,
Js
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.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

771 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

11 Experts available now in Live!

Get 1:1 Help Now