-- creating sequence is actually not a PL/SQL code but rather just DDL command. -- It is included here to reduce the number of scripts. CREATE SEQUENCE "CH17_SEQ_INQUIRY" MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1 START WITH 100 CACHE 1 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL; / create or replace function ch17_count_docs(xinqid in number) return number -- Function ch17_count_docs returns the number of documents associated with -- particular inquiry. Function parameter 1 is the ID of inquiry. -- If the length of the BLOB in the table ch17_inquiry_doc is greater than 0, -- then we assume that the content is stored. So, function adds 1 to variable -- wnumdocs, which is used as a counter. When all matching rows in the -- table ch17_inquiry_doc are examined, than counter function returns counter. is wnumdocs number := 0; CURSOR c_blob is select contblb from ch17_inquiry_doc where inquiry_id = xinqid; Begin FOR r_blobs IN c_blob LOOP if dbms_lob.getlength(r_blobs.contblb) > 0 then wnumdocs := wnumdocs + 1; end if; END LOOP; RETURN wnumdocs; END ch17_count_docs; / create or replace function ch17_doc_bytesize(xdocid in number) return number -- Function ch17_doc_bytesize returns the size of the BLOB, stored in contblb -- column in the table ch17_inquiry_doc for particular row. -- Function parameter 1 is the ID of of ch17_inquiry_doc table. -- The length of contblb column is provided by another system level PL/SQL -- function called dbms_lob.getlength. is wdoc_size number := 0; wblob blob; Begin select contblb into wblob from ch17_inquiry_doc where id = xdocid; wdoc_size := dbms_lob.getlength(wblob); RETURN wdoc_size; END ch17_doc_bytesize; /