Solved

Insert a BLOB into a ANYDATA table column

Posted on 2006-06-25
19
735 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
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
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16981365
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.

 
LVL 16

Expert Comment

by:MohanKNair
ID: 16981370
0
 
LVL 19

Accepted Solution

by:
actonwang earned 250 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 250 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
File generation using utl_file 4 54
Getting TNS:Connect timeout occurred while opening the application 5 79
Oracle DATE Column Space 11 79
Creation date for a PDB 5 21
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 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