Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert a LONG into a ANYDATA table column

Posted on 2006-06-25
5
Medium Priority
?
172 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:johan777
  • 3
5 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16980756
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
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16980785
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16981320
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.
0
 
LVL 19

Accepted Solution

by:
actonwang earned 2000 total points
ID: 16981381
>>  t := sys.anydata.CONVERTvarchar2(l1);

to MohanKNair :
     as I said, you CAN NOT store LONG type (both 9i and 10g) ! here an implicit cast has been done as:

          t := sys.anydata.CONVERTvarchar2( cast(l1 as varchar2) );

     So it is not really a LONG type anynmore!
0
 

Author Comment

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

782 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