-- PL/SQL example of accessing the BLOB

-- need following line to enable output to screen
   set serveroutput on format wrapped size 1000000

-- also bear in mind that with dbms_output.put_line,
--   we're limited to a maximum string length of 255

-- this example is easily adaptable to bib or mfhd.
--   merely change "auth" to "bib" or "mfhd" 
--   in the case of mfhd, the recseg length needs to be 300
-- also remember to replace "wmichdb" in the query below
--   with the database name at your installation

declare
  cursor marcrec is
    select auth_id,
           record_segment,
           seqnum
    from wmichdb.auth_data
    where auth_id >= 650161 and auth_id <= 650163
    order by auth_id asc, seqnum desc;
-- why order query like this?
--   see assemble marc record section below for explanation

  m_authid number(22);
  m_recseg char(990);
  m_seqnum number(22);

  marc       varchar2(17000);
  marclen    integer(5);
  baseaddr   integer(5);
  strptr     integer(5);
  tagid      varchar2(3);
  taglen     integer(4);
  offset     integer(5);
  tagaddr    integer(5);
  tagdata    varchar2(9999);
  
  idx        integer(5);
  strlen     integer(5);
  pos        integer(5);
  mchar      char(1);
  subfldchar char(1):= '|';
  marceof    boolean:= false;

begin
  open marcrec;

-- this loop goes through all the rows returned by the cursor above
  loop

-- begin assemble marc record section
    marc:= '';
    fetch marcrec into m_authid, m_recseg, m_seqnum;
    exit when marcrec%notfound;
-- previous line is our exit for when we've gotten all the
--   cursor's records.

-- exit mechanism below in case we hit the end of the cursor
    while m_seqnum <> 1
    loop
      marc:= m_recseg || marc;
      fetch marcrec into m_authid, m_recseg, m_seqnum;
      if marcrec%notfound then
        marceof:= true;
        exit;
      end if;
    end loop;
    exit when marceof;
    -- get segment #1
    marc:= m_recseg || marc;

-- EXPLANATION:
-- start off by getting a marc record segment.
-- if it's a typical record, all the data is in this one
--   segment and only the last line of code gets executed.
-- if this is a longer record spread across multiple rows,
--   we have the last segment of this record (remember the 
--   query order above?). the while loop is now employed to
--   march through successive rows from our cursor, going
--   backwards by seqnum and prepending successive segments
--   to what we already have.
--
-- so why go "backwards"?
-- if we predicate the segment-to-marc-record assembly on
--   when the auth_id changes, once it changes we've gone
--   too far and can't go back to get the last segment to
--   completely assemble the now previous record.
-- thus the looping is predicated on seqnum in reverse order
--   because there will *always* be a seqnum of -1-.
-- if there are multiple segments, we'll always end with a
--   seqnum of -1- *and* still be on the same auth_id and can
--   go on processing the record.

-- end assemble marc record section


-- get the record length and the base address where tag data begins
    marclen:= substr(marc, 1, 5);
    baseaddr:= substr(marc, 13, 5) + 1;
    dbms_output.put_line('authid= ' || m_authid || '  marclen= ' || marclen || '  baseaddr= ' || baseaddr);


-- begin get tag section
-- this loop goes through the record's directory, reading a tag's
--   information and its data, and doing some formatting

-- position ourselves at the start of the directory
    strptr:= 25;
-- start going through directory and stopping at the end,
--   before the base address where the data begins
    while strptr < baseaddr-1
    loop
-- getting the tag's parameters from the directory "triplet"
      tagid:=  substr(marc, strptr,   3);
      taglen:= substr(marc, strptr+3, 4);
      offset:= substr(marc, strptr+7, 5);
      dbms_output.put_line('strptr= ' || strptr || '  tagid= ' || tagid || '  taglen= ' || taglen || '  offset= ' || offset);
-- compute where the tag's data starts
      tagaddr:= baseaddr + offset;
      dbms_output.put_line('tagaddr= ' || tagaddr);
-- read the tag's data
      tagdata:= substr(marc, baseaddr+offset, taglen-1);
-- I like to convert the subfield delimiter to a vertical bar (|)
--   for better readability
      tagdata:= translate(tagdata, chr(31), subfldchar);

-- do some additional readability formatting by adding spaces
--   around a subfield indicator
      idx:= 2;
      strlen:= length(tagdata);
      while idx < strlen
      loop
        mchar:= substr(tagdata, idx, 1);
        if mchar = subfldchar then
          tagdata:= substr(tagdata, 1,    idx-1) || ' ' ||
                    substr(tagdata, idx,  2    ) || ' ' ||
                    substr(tagdata, idx+2      );
          idx:= idx + 2;
          strlen:= length(tagdata);
        end if;
        idx:= idx + 1;
      end loop;

-- output the tag's data
-- breaking it into 80 character sections looks better and 
--   gets us around the 255 character limit
      pos:= 1;
      while pos < strlen
      loop
        dbms_output.put_line(substr(tagdata, pos, 80));
        pos:= pos + 80;
      end loop;

-- move to the next tag in the directory
      strptr:= strptr + 12;
    end loop;
-- end get tag section


  end loop;
  close marcrec;
end;
/