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

On Oct 18, 6:57 am, JonL <boha... (AT) yahoo (DOT) com> wrote:
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

On Oct 18, 8:34 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
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/...D:839298816582

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


David Fitzjarrell