How to view LONG (BLOB) or CLOB columns via SQL

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
I want to be able to see the z00_data (format: LONG) and z00p_data (format: CLOB).

I can do this:

select z00_data from z00 where z00_doc_number = '000000123'; or

select z00p_ptr from z00p where z00p_doc_number = '000000123';

and I see the bib record in a "raw" form, but if I try to view big bib records, the result is truncated.

Also, I am unable to do a select ... where:

abc01@ALEPH1> select z00_data from z00 where z00_data like '%Estado%';

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Resolution:
In order to see longer z00 records, you need to do the "set long" command prior to the "select":

SQL> set long 45000

(45,000 is the maximum doc record size in Aleph.)

or for the z00p:

SQL> set long 1000000

(n can be up to 2 million.)

This will enable you to see the entire z00_data or z00p_data field.

Note: Longer records will scroll off the screen and, even scrolling up, you will not be able to see them. To see the entire record, you will need to enter a "spool" command prior to the select. Then you can quit SQL and do "more" on the file you spooled.

There is no way to do an SQL "select .. where" against a LONG column. If you want to find records containing particular bib field values using SQL, you need to search the z00r or z13.

The "select ... where" does work OK against a *CLOB* in Oracle 10-up.