• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

Insert a BLOB into a ANYDATA table column

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
johan777
Asked:
johan777
  • 7
  • 6
  • 6
2 Solutions
 
actonwangCommented:
as clob, it is not supported in oracle 9i, you will get:
ORA-22370: incorrect usage of method AnyData Insert
0
 
actonwangCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
actonwangCommented:
See my comments. in 10g, "bugs" in 9i should be fixed. try my example there.
0
 
johan777Author Commented:
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
 
MohanKNairCommented:
The type anydata is owned by SYS
Try SYS.anydata.convertnclob instead of anydata.convertnclob
0
 
johan777Author Commented:
Hi MohanKNair,

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

Regards,
Js
0
 
MohanKNairCommented:
Who owns the TYPE anydata? Try this query

select owner, object_type from dba_objects
where object_name='ANYDATA'
/
0
 
johan777Author Commented:
select owner, object_type from dba_objects
where object_name='ANYDATA'
/


Result:
OWNER,OBJECT_TYPE
SYS,TYPE
SYS,TYPE BODY
PUBLIC,SYNONYM
0
 
MohanKNairCommented:
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
 
johan777Author Commented:
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
 
MohanKNairCommented:
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
 
MohanKNairCommented:
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
 
johan777Author Commented:
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
 
actonwangCommented:
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
 
actonwangCommented:
see my comments and links in post of CLOB and you have the same situation.
0
 
actonwangCommented:
if you have metalink, open a ticket there to ask for help from oracle support.
0
 
johan777Author Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now