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
Solved

Insert a CLOB into a ANYDATA table column

Posted on 2006-06-25
15
1,384 Views
Last Modified: 2007-12-19
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 CLOB 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;
  clob_fld CLOB := 'CCCCCC';
BEGIN
  DELETE FROM test_anydata;
--CLOB
  INSERT INTO test_anydata (fld) VALUES (anydata.convertclob(clob_fld));
  c_fld := NULL;
  SELECT to_clob(anydata.accessclob(fld)) INTO c_fld FROM test_anydata WHERE anydata.gettypename(fld) LIKE 'SYS.CLOB';
  Dbms_Output.put_line ('CLOB - ' || c_fld);
  commit;
END;

Regards,
Js
0
Comment
Question by:johan777
  • 10
  • 3
  • 2
15 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16980748
are you working on oracle 10 or 9?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16980766
If you use oracle 9i, you can not insert a clob into anydata column.

convertclob is not supported in oracle9i though it is in the document.

see:
http://forums.oracle.com/forums/thread.jspa?messageID=483909񶉅

also in metalink:
https://metalink.oracle.com/metalink/plsql/f?p=200:27:5125284200540248465::::p27_id,p27_show_header,p27_show_help:343054.996,1,1
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16980807
in 10g, try:

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

  SELECT anydata.accessclob(fld) INTO c_fld FROM test_anydata;
  Dbms_Output.put_line ('CLOB - ' || c_fld);

END;
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

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 16981360
It is not possible to insert anydata of CLOB into a table. But variables of anydata datatype can be handled using PL/SQL.

SQL> declare
  2  t sys.anydata;
  3  c1 clob;
  4  l1 long := 'This is is sample data';
  5  begin
  6  c1 := l1;
  7  t := sys.anydata.CONVERTCLOB(c1);
  8  if t.gettypename='SYS.CLOB'
  9  THEN
 10  insert into test_anydata(fld) values (t);
 11  END IF;
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-22370: incorrect usage of method AnyData Insert
ORA-06512: at line 10


The workaround is to convert CLOB to varchar2 or long datatype.

declare
v_str long;
BEGIN
for c1 in(select * from test_anydata)
LOOP
if ( c1.fld.getVarchar2(v_str) = dbms_types.success )
THEN
dbms_output.put_line(v_str);
END IF;
END LOOP;
END;
/
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16981393
>>The workaround is to convert CLOB to varchar2 or long datatype.

      LONG type has been depreciated so either in 9i or 10g, LONG datatype will not be supported for anydata.

0
 
LVL 19

Expert Comment

by:actonwang
ID: 16981399
to MohanKNair :

       please see my comments and links. all technical details have been there.
       Let me know if you have any question.

acton
0
 

Author Comment

by:johan777
ID: 16982155
Hi all,

To start: I'm using 10g Rel 1

If I run the first solution, I get the error as stated:
ORA-22370: incorrect usage of method AnyData Insert
ORA-06512: at line 6

What baffels me is that there is a function convertclob, but it does'nt work!
I agree, we should not try to use the long data type as it is obsolete, but I'm not sure that varchar2 is a viable workaraound.

So, the verdict is still out on how to get the convertclob function working.

Thanx for the help so far!
Js
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16982233
The error is happening at Line 10 for insert statement

SQL>
SQL> declare
  2  t sys.anydata;
  3  c1 clob;
  4  l1 long := 'This is is sample data';
  5  begin
  6  c1 := l1;
  7  t := sys.anydata.CONVERTCLOB(c1);
  8  if t.gettypename='SYS.CLOB'
  9  THEN
 10  insert into test_anydata(fld) values (t);
 11  END IF;
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-22370: incorrect usage of method AnyData Insert
ORA-06512: at line 10
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16982255
SQL> declare
  2  t sys.anydata;
  3  c1 clob;
  4  l1 long := 'This is is sample data';
  5  begin
  6  c1 := l1;
  7  t := sys.anydata.CONVERTCLOB(c1);
  8  end;
  9  /

PL/SQL procedure successfully completed.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16983987
>>ERROR at line 1:
>>ORA-22370: incorrect usage of method AnyData Insert
>>ORA-06512: at line 10

     Ok. then, I think that it is still not supported in oracle 10g for table storage.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16984011

>>>>>>>>>>>
 SQL> declare
  2  t sys.anydata;
  3  c1 clob;
  4  l1 long := 'This is is sample data';
  5  begin
  6  c1 := l1;
  7  t := sys.anydata.CONVERTCLOB(c1);
  8  end;
  9  /


to MohanKNair: convertclob member function is defintely working for both 9i and 10g. BUT the storage mechanism is still not supported in ORACLE for ANYDATA column.
                  PLEASE see my previous posts and links for details!


0
 
LVL 19

Expert Comment

by:actonwang
ID: 16984023
try the following and you will see what I mean:

DECLARE
  c_fld CLOB := NULL;
  clob_fld CLOB := 'CCCCCC';
  t ANYDATA;
BEGIN
  t := anydata.convertclob(clob_fld);
  DBMS_OUTPUT.PUT_LINE('test - ' || t.gettypename());  -- this should be ok since the function is working.

  INSERT INTO test_anydata(fld) VALUES (t);  -- NO. not working. because clob storage is not supported for anydata column
END;
/
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16984042
>>l1 long := 'This is is sample data';
..
>>c1 := l1;

to MohanKNair: This is unnecssary.  LONG is a depreciated datatype. An implicit conversion is done there. This is better to be replaced by a simple:

      c1: = 'This is a sample data';
0
 

Author Comment

by:johan777
ID: 16984114
Nope:

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

0
 
LVL 19

Expert Comment

by:actonwang
ID: 16984136
>> Nope:

       YES. that verifies what I just said!

       did you issue:

      set serveroutput on

       you should see "test - SYS.CLOB" line output which means the function is working. but you can not insert this value into anydata column because the storage mechanism is still not supported as I showed in the links.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing parameters to sql script oracle 4 58
Oracle 12c database link between pdb not working 20 116
Oracle Verification of DataPump Export and Import 17 59
Fill Date time Field 12 23
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

808 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