Skip to main content
  • Subscribe by RSS

    Ex Libris Knowledge Center

    Looking at doc records with SQL; Binary Large OBject format

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

    You are trying to look at bib, adm, hol, authority, or course reserve records in SQL. When you select "Z00_data", all you see is the first 80 bytes. You want to see the rest.

    The Z00 is stored in Binary Long OBject (or Binary Large OBject) format. This is SQL data type "LONG RAW". By default, SQL will only display 80 bytes from a type LONG field. To see more of the record, in SQLPlus, enter this command: "set long 2000". This will enable up to 2,000 bytes of the Z00 to display.

    Alternatively, you can use the ALEPH "util f" /4 command, specifying "doc" as the File Name, to look at Z00/doc records. (The fields are formatted better / more readable when viewed under this utility.)

    Note: though you can, using the "select z00_data from z00;" command, display as many bytes of the z00 as you have specified in the "set long" command, you are *not* able --regardless of what "set long" is set to-- to anything like this:

    select substr (Z00_DATA, 1,7) from z00;


    select * from z00 where z00_data like '0008FMT LBK%';

    These will not work ( you get the message: "ORA-00932: inconsistent datatypes") since SQL does not allow such queries against LONG-format data.

    In 15.2-up, the z00r table (if built) can be used for SQL queries. (See KB 9560.)

    If the field in question is author, title, call number, imprint or isbn/issn, you can do SQL on the Z13 record.

    Additional Information


    • Article last edited: 10/8/2013
    • Was this article helpful?