How to convert the Long value to String using sql

Question

I am doing a long to string conversion using java in following way.

Long longValue = 367L;
String str = Long.toString(longValue, 36).toUpperCase();

this is returning me as value A7. how can achieve this in doing oracle sql.

UPDATED:

Hi, I have analyzed how java code is working then wanted to implement the same thing in procedure.

First point is Input vaues. LONG and Radix. in my case Radix is 36. so i will have values from 1..9A...Z0 It picks up the values from this set only. Second point Long value as input. we have to divide this value with radix. if the quotient is more than 36 again we need to divide.

For eaxmple 367 then my converted value is 10(quotient) 7(remainder) that is A7. 3672 converted value is 102 0 i need to do again for 102 that is 2 -6 so my final value will be 2-6 0 that is 2U0(- means reverse the order).

UPDATE 2:

Using oracle built in functions we can do this. this was solved by my friend and gave me a function.I want to thank my friend. this will give me an out put as follows.

367 then my converted value is 10(quotient) 7(remainder) that is *A*7.(I modified this to my requirement).

FUNCTION ENCODE_STRING(BASE_STRING    IN    VARCHAR2,
                            FROM_BASE      IN    NUMBER,
                            TO_BASE        IN    NUMBER) 
  RETURN VARCHAR2
  IS
      V_ENCODED_STRING    VARCHAR(100);
  BEGIN

      WITH N1 AS (
                   SELECT SUM((CASE 
                                   WHEN C BETWEEN '0' AND '9' 
                                       THEN TO_NUMBER(C) 
                                   ELSE 
                                       ASCII(C) - ASCII('A') + 10 
                               END) * POWER(FROM_BASE, LEN - RN)    
                              ) AS THE_NUM
                   FROM (SELECT SUBSTR(BASE_STRING, ROWNUM, 1) C, LENGTH(BASE_STRING) LEN, ROWNUM RN 
                         FROM DUAL 
                         CONNECT BY ROWNUM <= LENGTH(BASE_STRING))
                  ),
           N2 AS ( 
                   SELECT (CASE 
                               WHEN N < 10 
                                    THEN TO_CHAR(N) 
                               ELSE CHR(ASCII('A') + N - 10) 
                           END) AS DIGI, RN
                   FROM (SELECT MOD(TRUNC(THE_NUM/POWER(TO_BASE, ROWNUM - 1)), TO_BASE) N, ROWNUM RN
                         FROM N1
                         CONNECT BY ROWNUM <= TRUNC(LOG(TO_BASE, THE_NUM)) + 1)
                  )
      SELECT SYS_CONNECT_BY_PATH(DIGI, '*') INTO V_ENCODED_STRING  
      FROM N2
      WHERE RN = 1    
      START WITH RN = (SELECT MAX(RN) FROM N2)
      CONNECT BY RN = PRIOR RN - 1;

      RETURN V_ENCODED_STRING;

Answer 1

IN PL/SQL (or Oracle SQL) you have the a function called TO_CHAR.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions181.htm

Answer 2

It is not possible to do it in the pure SQL. You have to use PL/SQL.

Simple example how to do it PL/SQL:

CREATE TABLE long_tbl
(
    long_col LONG
);

INSERT INTO long_tbl VALUES('How to convert the Long value to String using sql');

DECLARE
    l_varchar VARCHAR2(32767);
BEGIN
    SELECT  long_col
    INTO    l_varchar
    FROM    long_tbl;

    DBMS_OUTPUT.PUT_LINE(l_varchar);
END;
-- How to convert the Long value to String using sql

There is TO_LOB function but it can only by used when you insert data into table. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions185.htm

You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement.

There is also other, more proper way to do it by using "dbms_sql.column_value_long" but this gets complicated (fetching of the LONG column and appending to the CLOB type.)

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1025399

(Oracle Database PL/SQL Packages and Types Reference)