johan777
asked on
Insert a CLOB 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 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
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_
c_fld := NULL;
SELECT to_clob(anydata.accessclob
Dbms_Output.put_line ('CLOB - ' || c_fld);
commit;
END;
Regards,
Js
are you working on oracle 10 or 9?
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
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
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;
DECLARE
c_fld CLOB := NULL;
clob_fld CLOB := 'CCCCCC';
BEGIN
DELETE FROM test_anydata;
INSERT INTO test_anydata (fld) VALUES (anydata.convertclob(clob_
commit;
SELECT anydata.accessclob(fld) INTO c_fld FROM test_anydata;
Dbms_Output.put_line ('CLOB - ' || c_fld);
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
LONG type has been depreciated so either in 9i or 10g, LONG datatype will not be supported for anydata.
to MohanKNair :
please see my comments and links. all technical details have been there.
Let me know if you have any question.
acton
please see my comments and links. all technical details have been there.
Let me know if you have any question.
acton
ASKER
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
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
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
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
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.
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.
>>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.
>>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.
>>>>>>>>>>>
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!
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_f ld);
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;
/
DECLARE
c_fld CLOB := NULL;
clob_fld CLOB := 'CCCCCC';
t ANYDATA;
BEGIN
t := anydata.convertclob(clob_f
DBMS_OUTPUT.PUT_LINE('test
INSERT INTO test_anydata(fld) VALUES (t); -- NO. not working. because clob storage is not supported for anydata column
END;
/
>>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';
..
>>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';
ASKER
Nope:
ERROR at line 1:
ORA-22370: incorrect usage of method AnyData Insert
ORA-06512: at line 8
ERROR at line 1:
ORA-22370: incorrect usage of method AnyData Insert
ORA-06512: at line 8
>> 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.
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.