I do this all the time, but you have to keep a few key facts in mind:
1) If you have native geometry (GEOMETRY/SDO_GEOMETRY/ST_GEOGRAPHY), you should
use SQL to construct your view, and register the resulting layer with 'sdelayer' (create_view
is only for SDEBINARY/SDELOB layers).
2) If you intend to use the resulting layer in ArcGIS, you *MUST* include an OBJECTID column
in your column list (it must map to SE_INT32_TYPE, be NOT NULL, and contain DISTINCT,
positive, non-zero, and repeatable values).
3) 'sdetable -o create_view' will use table aliases (-t "mytab1 a, mytab2 b"), but you cannot
alias the geometry column (it won't know what type to use)
4) Beware of polynomial expansion with one-to-many joins and multiple tables
You didn't say what version and service pack of ArcSDE you're using, or even which Oracle
release is under it, so it's hard to reproduce your issue. An "identifier too long" message
is generated if a column name exceeds 30 characters -- this has nothing to do with Esri
software (it's an Oracle limitation).
-V