reporting an Oracle LONG RAW text field (shows as BLOB in crystal)

[prigro]

I would like to use crystal reports to report off a LONG RAW column in Oracle which contains text. Crystal Reports handles this datatype as a BLOB, therefore not displaying the text. I believe that a stored procedure can be used to solve this problem, unfortunately I don't know anything about stored procedures.

more Info:
Oracle 9i
Crystal Reports 9
Table name: NOTATION
column name: NOTE

 

[synapsevampire]

What connectivity are you using?

Try creating a SQL Expression to CAST it, as in:

CAST(table.field AS VARCHAR2(1000))

Can't recall if you can use a CAST on a LONG RAW.

-k

 

[prigro]

I'm using native oracle connectivity.

The field I am trying to use (NOTE) is not in the list of fields available. Not sure why this would be the case.

I then typed in the formula CAST(NOTATION.NOTE AS VARCHAR2(1000)), but the CAST did not turn blue indicating crystal doesn't recognize it. The error produced is:

Error in compiling SQL Expression:
Query Engine Error: 'ORA-00932: inconsistant datatypes: expected number got BINARY'.

I then changed the formula to use the CONVERT(NOTATION.NOTE AS VARCHAR2(1000)), which did turn blue but it showed an error:

Error in compiling SQL Expression:
Query Engine Error: 'ORA-00907: Missing right parenthesis'.

 

[Turkbear]

Hi,
The Insert Sql Expression editor does not behave like other parts of the Designer interface..It often will not recognize Database-specific terms or syntax..So the 'did not turn blue' may not have meant it was wrong..

( BTW, BLOBS and CLOBS( or Long Raws, the deprecated type) will not show in a field list)

Try Inserting the Sql Expression
Substr(NOTATION.NOTE,1,1000)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Yeah, long raw isn't used to store text, I just looked it up.

Speak with your dba, or get a new one

CAST does not support LONG, LONG RAW, any of the LOB datatypes, or the Oracle-supplied types.

There may be a means to extract it using a Stored Procedure though, depends on what they've stored int here, if it's text, it might be a text file, which isn't the same as text.

Info on RAW and LONG RAW Datatypes:

The RAW and LONG RAW datatypes store data that is not to be interpreted (that is, not explicitly converted when moving data between different systems) by Oracle Database. These datatypes are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.

Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns. See TO_LOB for more information.

RAW is a variable-length datatype like VARCHAR2, except that Oracle Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, Oracle Net and Import/Export automatically convert CHAR, VARCHAR2, and LONG data from the database character set to the user session character set (which you can set with the NLS_LANGUAGE parameter of the ALTER SESSION statement), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as CB.

-k

 

[prigro]

Unfortunately, the database is the back-end of a commercial ERP system for which I have no control. I too shook my head when I first came across this problem.

Inserting the Sql Expression Substr(NOTATION.NOTE,1,1000) once again yielded the error:
Error in compiling SQL Expression:
Query Engine Error: 'ORA-00932: inconsistant datatypes: expected number got BINARY'.

I was given an example of how to convert another table which requires the same conversion:

First, create a function:
Create or Replace Function Func_CUST_ORDER_BIN(CO_ID VARCHAR2)
RETURN VARCHAR2
IS
bits_blob LONG RAW;
Bits_Varchar2 Varchar2(32000);
Bits_Test Varchar2(4000);

Begin
select bits into bits_blob
from Cust_Order_Binary
where Cust_Order_ID = CO_ID;

Bits_Varchar2 := UTL_RAW.CAST_TO_VARCHAR2(bits_blob);

If LENGTHB(Bits_Varchar2) > 4000 then
Bits_Test := 'SPEC TOO LONG, CONSULT THE ORDER';
Else
Bits_Test := Bits_Varchar2;
End if;

RETURN Bits_Test;
END;


Second, create a view:
DROP VIEW View_CUST_ORDER_SPEC
;
CREATE VIEW View_CUST_ORDER_SPEC(CUST_ORDER_ID, SPECIFICATIONS)
AS
SELECT
ID,
Func_CUST_ORDER_BIN(ID)
FROM
CUSTOMER_ORDER
;
CREATE PUBLIC SYNONYM View_CUST_ORDER_SPEC
FOR SYSADM.View_CUST_ORDER_SPEC
;
GRANT SELECT ON View_CUST_ORDER_SPEC TO PUBLIC
;
COMMIT
;

I made changes to the syntax to work for the table and columns I need to display and came up with:

create or replace function func_note(note varchar2)
return varchar2
is
note_blob LONG RAW;
note_varchar2 varchar2(2000);

begin
select note into note_blob from notation;
note_varchar2 := utl_raw.cast_to_varchar2(note_blob);
end;



create view view_note(type, owner_id, create_date, notes)
as
select type, owner_id, create_date, func_note(owner_id)
from notation;

The statements were successful, but when doing a select on the view, I get the error:
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value

 

[synapsevampire]

If you're allowed to create functions, just create a Stored Procedure.

Unfortunately you didn't bother to post which connectivity you used. I suggest that you use the Crystal supplied native connectivity, or if you must use ODBC, use the Crystal supplied ODBC driver and make sure that you turn on the Procedure Returns Results, but don't use the Oracle supplied ODBC driver.

-k

 

[prigro]

I was using the Native connectivity within Crystal, but can use whatever it takes.

I'm completely new to stored procedures. How would I go about creating the stored procedure... what would be the syntax?

 

[prigro]

can anyone help me with the stored procedure syntax?