BLOB question ???

From: Leslie Lu <leslie_y_lu_at_yahoo.com>
Date: Mon, 29 Oct 2001 09:59:09 -0800
Message-ID: <F001.003B72EB.20011029093521@fatcity.com>

Hi all,

I have a table that has BLOB data type, and there is a SP to select from it. The length out parameter returns the length of the contents in the BLOB. Now the SP returns the length for all the content even if the trailing character is a space, the developer wants to return length-1 if the ending character is a space.  Can anyone let me know how to do that? (815 on Sun56) Thank you so much!

Here is the SP:

CREATE OR REPLACE PROCEDURE test1
(in_serv_agree_id in number,

amount       in     number,
offset       in     number,
length       out    number,
text         out    varchar2,

RC1 IN OUT GLOBALPkg.RCT1
) as

   v_blob blob;
   buffer raw(32000);
   amount_read binary_integer;
   StoO_error integer;
   StoO_errmsg varchar2(255);

BEGIN
    amount_read := amount;
    select service_agree_text

      into v_blob
      from ref_service_agreement
     where service_agree_id = in_serv_agree_id;

    length := dbms_lob.getlength(v_blob);     dbms_lob.read(v_blob,amount_read,offset,buffer);         

    text:=utl_raw.cast_to_varchar2(buffer);     OPEN RC1 FOR

                 SELECT text 
                 FROM dual; 

    exception
    when others then

           StoO_error := SQLCODE;
           StoO_errmsg := SQLERRM;
        

raise_application_error(-20599,substr(StoO_errmsg,1,100));

 end test1;

Table create syntax:

CREATE TABLE Ref_Service_Agreement (

        service_agree_id     NUMBER(10) NOT NULL,
        service_agree_version VARCHAR2(20) NOT NULL,
        service_agree_start_date DATE NOT NULL,
        service_agree_end_date DATE NULL,
        service_agree_desc   VARCHAR2(255) NOT NULL,
        service_agree_text   BLOB NOT NULL
        )
        TABLESPACE LD_DATA
        lob(service_agree_text) store as
service_agree_text_data_seg
        ( tablespace LD_DATA
          storage(initial 1M next 1M pctincrease 0 )
          chunk 8192
          pctversion 0
          enable storage in row
          index service_agree_text_idx_seg
        ) 

Global package syntax:
CREATE or REPLACE PACKAGE GLOBALpkg AS
TYPE RCT1 IS REF CURSOR;
END;
/

---

Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Leslie Lu
  INET: leslie_y_lu_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Oct 29 2001 - 11:59:09 CST