Analytics Software

http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2

>
How can i convert from long to varchar2?
I want do that in single sql select.
>
The simplest method is to use a cursor in PL/SQL. See this Oracle-developer.net article
http://www.oracle-developer.net/display.php?id=430
>
method two: pl/sql
The second method simply takes advantage of PL/SQL's ability to convert LONG data to VARCHAR2 while fetching from cursors.
>
You also don't mention your 4 digit Oracle version or how long the LONG is that you are trying to convert. SQL has a limit of 4000 characters for VARCHAR2 while in PL/SQL the limit is 32K so that could also drive where the conversion is done.

Very funny. Trust me i used google and didnt find anything interesting.

I want do that i single sql select. I dont want using pl/sql, create table ;/

Same question has been asked before, just a few hours ago, amongst others....
2429488

We can't always have want we want.

LONGs have lots of restictions, they're difficult to work with and for very good reason they are deprecated.

A VARCHAR2 column is limited to 4000 character otherwise you'll need to use a CLOB.

In a CREATE TABLE statement or an INSERT SELECT statement you should be able to use [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions205.htm#SQLRF06134]TO_LOB.

Otherwise you're going to have to just work around it.

SQL> create table t1
  2  (col1 long);

Table created.

SQL> 
SQL> create table t2
  2  as
  3  select col1 from t1;
select col1 from t1
       *
ERROR at line 3:
ORA-00997: illegal use of LONG datatype


SQL> create table t2
  2  as
  3  select to_lob(col1) col1 from t1;

Table created.

SQL> drop table t2;

Table dropped.

SQL> create table t2
  2  (col1 varchar2(4000));

Table created.

SQL> insert into t2
  2  select to_lob(col1) from t1;

0 rows created.

SQL> drop table t2;

Table dropped.

SQL> drop table t1;

Table dropped.

SQL> 

But you can't use TO_LOB in a straight SELECT:

SQL> select to_lob(col1) from t1;
select to_lob(col1) from t1
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG

Well, I just looked on google and found loads of things telling me how to convert longs to varchar2. Your google must be broken... try re-installing it.
Why have you got a long deprecated datatype in the first place?

Answer is, you cannot do it in a single sql select, and you must use either pl/sql or create table as already demonstrated. If you don't want to take those options, then you're not going to do it, so that's your problem, not ours.