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
johan777Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

actonwangCommented:
See my comments. in 10g, "bugs" in 9i should be fixed. try my example there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.