Converting long columns

[kmm_micha]

Hi.

When trying to convert a LONG column to a VARCHAR2 column in the following statement...

--------------------------------
select to_char(search_condition)
from sys.dba_constraints;
--------------------------------

... I get the following error:

---------------------------------------
ORA-00997: illegal use of LONG datatype
---------------------------------------

When executing the following statement on de pl/sql developer...

---------------------------
select search_condition
from sys.dba_constraints;
---------------------------

... the grid does not return the value of the rows, but a on each row. When the button "..." is pressed, the value is showed in a separated window.

I need to put this text converted into a column VARCHAR2(4000) of a table (to make the constraints translation in the TOracleSession error_message, from DOA). I just could not make it, after trying a lot of things. I would like to know how you made this on your tool.

Thank you for your attention, and waiting for a possible answer.

 

[mike]

You can assign a long to a varchar2 within pl/sql and then do your insert or whatever. You don't need to do a to_char.

Here's a silly small example:

SET SERVEROUTPUT ON
DECLARE
   CURSOR c_whatever IS
      SELECT search_condition
      FROM   sys.dba_constraints
      WHERE  search_condition IS NOT NULL
      AND    ROWNUM <= 10;

   l_variable VARCHAR2(4000);
BEGIN
   FOR c_whatever_rec IN c_whatever
   LOOP
      -- I'm only taking 40 in the substring to display it,
      -- but you can go up to the 4000 when inserting.
      l_variable := substr(c_whatever_rec.search_condition, 1, 40);
      dbms_output.put_line(l_variable);
   END LOOP;
END;
/

Does that work for you?

Mike

 

[kmm_micha]

Thanks a lot! It worked fine for me, but after a milion of silly tries we discovered other way:

create table temp
(teste clob);

insert into temp
select to_lob(search_condition)
from sys.dba_constraints;

select t.*, to_char(teste)
from temp t;

 

[mike]

Good job - simpler and that would give better performance (at least I think so!).

Mike