asked on

Insert a LONG 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 LONG 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;
long_fld LONG := 'AAAAA';
BEGIN
DELETE FROM test_anydata;
--LONG
c_fld := to_clob(long_fld);
INSERT INTO test_anydata (fld) VALUES (c_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 ('LONG - ' || c_fld);
commit;
END;

Regards,
Js

Acton Wang

AnyData will not support LONG datatype. You can not store a LONG type into ANYDATA column both in oracle 9i/10g.

see:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/t_anydat.htm#1007367

Acton Wang

for other datatype such as varchar, number, you can look at this example:

http://asktom.oracle.com/pls/ask/f?p=4950:8:11346582379345341916::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3099475696866

MohanKNair

SQL>
SQL> CREATE TABLE test_anydata (fld sys.anydata NOT NULL);
Table created.

SQL>
SQL> set serveroutput on
SQL>

-- iNSERTS lONG DATA INTO sys.anydata table
SQL>
SQL> declare
2 t sys.anydata;
3 l1 long := 'This is is sample data';
4 begin
5 t := sys.anydata.CONVERTvarchar2(l1);
6 if t.gettypename='SYS.VARCHAR2'
7 THEN
8 insert into test_anydata(fld) values (t);
9 END IF;
10 end;
11 /

PL/SQL procedure successfully completed.

-- It is not possible to insert anydata converted from CLOB into a table
-- iNSERTS CLOB INTO sys.anydata table
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

-- Select anydata value from the table to a LONG variable

SQL> declare
2 v_str long;
3 BEGIN
4 for c1 in(select * from test_anydata)
5 LOOP
6 if ( c1.fld.getVarchar2(v_str) = dbms_types.success )
7 THEN
8 dbms_output.put_line(v_str);
9 END IF;
10 END LOOP;
11 END;
12 /
This is is sample data

PL/SQL procedure successfully completed.

ASKER CERTIFIED SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johan777

ASKER

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