Re: cast a long to integer?

On Oct 18, 8:34 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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 atwww.psoug.org/library.html,
> but at least it gives you a place to start.
>
> David Fitzjarrell

The above example is from my own hand, not from asktom.oracle.com. His is better, and I suggest you look it over; you can find it here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582

Again, my example isn't the greatest, but it does illustrate how you might go about such a task.

David Fitzjarrell Received on Thu Oct 18 2007 - 08:38:03 CDT