Converting Long to Varchar2

How to cast LONG to VARCHAR2 inline

You can use XML unless expressions contain something which can brake XML parsing.

select *
  from xmltable(
          '/ROWSET/ROW'
          passing (select dbms_xmlgen.getxmltype('select * from all_ind_expressions
                                                   where index_name = ''XDB$COMPLEX_TYPE_AK''')
                     from dual)
          columns index_owner varchar2(30) path 'INDEX_OWNER',
                  index_name varchar2(30) path 'INDEX_NAME',
                  table_owner varchar2(30) path 'TABLE_OWNER',
                  table_name varchar2(30) path 'TABLE_NAME',
                  column_expression varchar2(4000) path 'COLUMN_EXPRESSION')

INDEX_OWNER     INDEX_NAME           TABLE_OWNER     TABLE_NAME           COLUMN_EXPRESSION                  
--------------- -------------------- --------------- -------------------- -----------------------------------
XDB             XDB$COMPLEX_TYPE_AK  XDB             XDB$COMPLEX_TYPE     SYS_OP_R2O("XMLDATA"."ALL_KID")    
1 row selected.

Converting LONG column to VARCHAR2 into a view

It can be interesting to see how Oracle themselves have addressed the problem, as they use LONGs heavily in the data dictionary.

DBA_VIEWS, amongst others, has the columns TEXT (as LONG) and TEXT_VC as VARCHAR2. That is based of an internal view INT$DBA_VIEWS, where the TEXT_VC is derived from a function getlong(1, v.rowid). DBA_ARGUMENTS shows the parameters for that function are an OPCODE and a ROWID.

A trace (or inspired poking around of v$sql) shows that corresponds with the execution of "select text from view$ where rowid=:1" .

So basically they have a a PL/SQL function that selects the column into a varchar2 variable and returns that.

If you don't want to create an independent function, the latest versions of Oracle can include that as part of the view:

create or replace view my_views asWITH FUNCTION view_text(i_name VARCHAR2) RETURN VARCHAR2 IS   v_ret varchar2(32000); BEGIN   execute immediate 'select text from user_views where view_name = :name' into v_ret using i_name ;   RETURN substr(v_ret,1,4000); END;select view_name, view_text(view_name) view_textfrom user_views/

How do you convert LONG data to TIMESTAMP or VARCHAR2?

The only way to convert LONG columns is in PL/SQL. Look at the following example, which determines the length of the LONG field:

SET SERVEROUTPUT ON SIZE 10000;  
DECLARE
long_var LONG;
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
DBMS_OUTPUT.PUT_LINE('The length is '||LENGTH(long_var));
END;

Basically, you define a variable as the LONG type, then SELECT the column INTO the variable. Finally, it is output to the user. SET SERVEROUTPUT ON SIZE 10000 allows spooling from the PUT_LINE to go to the screen.

You can use a similar method to select the LONG into a varchar field. The following example puts the first 2000 characters into TABLE_B, which for our purposes has one column, TEXT_FIELD:

DECLARE
   long_var LONG;
   var_var  VARCHAR2(2000);
   BEGIN
   SELECT text_column INTO long_var
   FROM table_with_long
   WHERE rownum < 2;
   var_var := substr(long_var,1,2000);
   INSERT INTO table_b
   VALUES (var_var);
   END;

Converting a Long to a varchar

I created a Temp table to get around this as there was no elegant solution

CREATE TABLE t1
  AS
  SELECT ZIP_CODE,
           to_lob(LONGITUDE) as LONGITUDE
    FROM POSTAL_CODES;

update ALL_POSTAL_CODES AP
Set AP.LONGITUDE = 
( select P.LONGITUDE
    from
    T1 P
    where 
    P.ZIP_CODE = AP.ZIP_CODE
     and
      rownum = 1 
    )
    where exists (
      select 
        null
      from 
        T1 P
      where 
        P.ZIP_CODE = AP.ZIP_CODE
      );

Drop table T1;

Convert long decimal or float to varchar in SQL Server

If you don't specify a length for your varchar, it defaults to 30 characters in the case of a CONVERT operation.

That's not long enough to hold your 38-digit decimal. So give your varchar an appropriate length in the CONVERT statement!!

Try this:

select convert(varchar(40), 19040220000.0000000000000000000)

convert long as varchar in db into type long

1. In .NET make sure to use InvariantCulture.

   decimal d = Convert.ToDecimal("733.25", CultureInfo.InvariantCulture);
   

   More Information: MSDN: Using the InvariantCulture Property

2. In T-SQL you can cast that in your query.

   SELECT cast('733.25' as float)
   

   More Information: MSDN: CAST and CONVERT (Transact-SQL)

Both will result in 733.25.

hope this helps

---

Related Topics