RE: LONG

Atish,

I don't think the data being left or right aligned has any impact on weather you can use a function or not. Think of Long as an object, with no SUBSTR
(or like) functions defined for it. period. You are right though in
mentioning that Oracle stores everything as characters, but when you treat a bunch of these characters (as a varchar2 or a long or a long raw) as an object, the flexibility you get to manipulate them varies.

Simple thing, open standard.sql in $ORACLE_HOME/rdbms/admin and check definitions for all SUBSTR functions, you won't find any which works on a long. Following are two ways to manipulate longs ...

1. OCI calls
2. Copying longs into varchar2 vars using dbms_sql and then manipulate them ...
   (I haven't tried both, 'cause we don't use LONG in here ...)