Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert a BLOB into a ANYDATA table column

Posted on 2006-06-25
19
Medium Priority
?
768 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 6
19 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16980781
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
ID: 16980799
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

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

Author Comment

by:johan777
ID: 16982299
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
ID: 16982416
The type anydata is owned by SYS
Try SYS.anydata.convertnclob instead of anydata.convertnclob
0
 

Author Comment

by:johan777
ID: 16982539
Hi MohanKNair,

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

Regards,
Js
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16982659
Who owns the TYPE anydata? Try this query

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

Author Comment

by:johan777
ID: 16982775
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
ID: 16982886
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
ID: 16983017
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
ID: 16983071
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 750 total points
ID: 16983108
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
ID: 16983387
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
ID: 16984096
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
ID: 16984100
see my comments and links in post of CLOB and you have the same situation.
0
 
LVL 19

Expert Comment

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

Author Comment

by:johan777
ID: 17128879
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 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