Re: cast a long to integer?

From: <fitzjarrell_at_cox.net>
Date: Thu, 18 Oct 2007 06:34:29 -0700
Message-ID: <1192714469.591073.51020@v29g2000prd.googlegroups.com>

On Oct 18, 6:57 am, JonL <boha..._at_yahoo.com> wrote:
> We have a table, T1, with a range partition on a numeric value. I am
> trying to join the dba_tab_partitions table with T1 where high_value =
> key_value. Of course this does'nt work cause HIGH_VALUE is a long. I
> thought I would be able to somehow cast the HIGH_VALUES as a number,
> but the simple way to_number (HIGH_Values) does not work. Could
> someone help me with this ?
>
> JonL
>
> Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options

You'll need to use PL/SQL to get the LONG into a VARCHAR2 variable; at that point you can then use TO_NUMBER or CAST to convert it.

asktom.oracle.com has at least one example of converting a LONG to a VARCHAR2. It's not the most efficient example, however:

SQL> create table mytable(

  2          mylong long,
  3          mytext varchar2(40),
  4          mycomment varchar2(200));

Table created.

SQL>
SQL> insert all
  2 into mytable
  3 values (47328, 'Test','Testing 1')
  4 into mytable
  5 values (47327, 'Test','Testing 2')
  6 into mytable
  7 values (47326, 'Test','Testing 3')
  8 into mytable
  9 values (47325, 'Test','Testing 4')
 10 into mytable
 11 values (47324, 'Test','Testing 5')
 12 into mytable
 13 values (47323, 'Test','Testing 6')
 14 into mytable
 15 values (47322, 'Test','Testing 7')
 16 into mytable
 17 values (47321, 'Test','Testing 8')
 18 into mytable
 19 values (47320, 'Test','Testing 9')
 20 into mytable
 21 values (47319, 'Test','Testing 10')  22 select * from dual;

10 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set serveroutput on size 1000000
SQL>
SQL> declare
  2          my_char varchar2(20);
  3          my_num  number;
  4
  5          cursor get_long_val is
  6          select mylong
  7          from mytable;

  8
  9 begin

 10          open get_long_val;
 11          loop
 12
 13                  fetch get_long_val into my_char;
 14                  exit when get_long_val%notfound;
 15
 16                  my_num := cast(my_char as number);
 17
 18                  dbms_output.put_line('Varchar2: '||my_char||'

Number: '||my_num);
 19

 20          end loop;
 21          close get_long_val;

 22
 23 end;
 24 /

Varchar2: 47328    Number: 47328
Varchar2: 47327    Number: 47327
Varchar2: 47326    Number: 47326
Varchar2: 47325    Number: 47325
Varchar2: 47324    Number: 47324
Varchar2: 47323    Number: 47323
Varchar2: 47322    Number: 47322
Varchar2: 47321    Number: 47321
Varchar2: 47320    Number: 47320
Varchar2: 47319    Number: 47319

PL/SQL procedure successfully completed.

SQL> Note the LONG in the original table is converted to a varchar2 then to a number. I expect Daniel has better examples at www.psoug.org/library.html, but at least it gives you a place to start.

David Fitzjarrell Received on Thu Oct 18 2007 - 08:34:29 CDT