Convert LONG data to a Text String

[IanWaterman]

I am using Toad to create views on a Oracle 8 data base. The view is to be used in a Crystal report. Unfortunately Crystal will not let me do much with a LONG field.

There is only a small amount of data in this field (upto 25-30 characters max)

I have tried using SUBSTR(FieldName,1,30) "Description" in my SQL statement but get Oracle error ORA-00932 Inconsistent Datatypes.

Is it possible to extract first 30 characters from a Long field and convert them to a VARCHAR2 (30)?

Thank you

Ian

 

[Bimalaggarwal]

Long Columns are restrictive in your example.
I would do it this way.
Create a function using your long column and returning a varchar2 value.

Using this function to create the view.

See template code below and substitute your values.

You may drop the function later if not needed.

-----------------------
CREATE OR REPLACE FUNCTION long_to_varchar2 (<your_column> datatype)
RETURN VARCHAR2 IS

varcharVal VARCHAR2(4000);
varcharLength NUMBER;
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fetchIt PLS_INTEGER;

BEGIN

DBMS_SQL.PARSE
(cur,'SELECT FieldName FROM <your_table> WHERE <your_column>= '|| <your_id>, DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN_LONG(cur,1);

fetchIt := DBMS_SQL.EXECUTE_AND_FETCH(cur);

DBMS_SQL.COLUMN_VALUE_LONG(cur,1,40,0,varcharVal,varcharLength);

DBMS_SQL.CLOSE_CURSOR(cur);

RETURN varcharVal;

END;
------------------------

View query in Toad
----------------

CREATE OR REPLACE VIEW <view_name> ( VAL
) AS select long_to_varchar2(<your_column>) val from
<your_table>