6 Replies Latest reply: Aug 15, 2012 8:47 AM by BluShadow RSS

    Conversion long to varchar2

    952515
      How can i convert from long to varchar2?
      I want do that in single sql select.

      Edited by: 949512 on 2012-08-14 14:38
        • 1. Re: Conversion long to varchar2
          sb92075
          949512 wrote:
          How can i convert from long to varchar2?
          I want do that in single sql select.

          Edited by: 949512 on 2012-08-14 14:38
          http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2
          • 2. Re: Conversion long to varchar2
            rp0428
            >
            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.
            • 3. Re: Conversion long to varchar2
              952515
              sb92075 wrote:
              949512 wrote:
              How can i convert from long to varchar2?
              I want do that in single sql select.

              Edited by: 949512 on 2012-08-14 14:38
              http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2
              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 ;/
              • 4. Re: Conversion long to varchar2
                Hoek
                Same question has been asked before, just a few hours ago, amongst others....
                Convert long to varchar2 or clob in pure Oracle SQL
                • 5. Re: Conversion long to varchar2
                  Dom Brooks
                  I want do that in single sql select.
                  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
                  • 6. Re: Conversion long to varchar2
                    BluShadow
                    949512 wrote:
                    sb92075 wrote:
                    949512 wrote:
                    How can i convert from long to varchar2?
                    I want do that in single sql select.

                    Edited by: 949512 on 2012-08-14 14:38
                    http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2
                    Very funny. Trust me i used google and didnt find anything interesting.
                    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.
                    I want do that i single sql select. I dont want using pl/sql, create table ;/
                    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.