[DB-SIG] Buffer objects in DCOracle

Eric Brunson brunson@level3.net
Tue, 30 May 2000 16:44:53 -0600

I'm having a problem with passing a Buffer (type 'string') and having
Oracle be able to manipulate it as a string, i.e. concatenate to it.

Here is some code I'm using:


errtextbuff = DCOracle.Buffer( 1, 1000 )
sid = 1

dbh = dbconn.prepare( """
            errs service_pkg.ErrorList;
            i number;
            service_pkg.validate( :service_id, errs );
            for i in 1..errs.COUNT LOOP
                :errtext := :errtext || errs(i);
            end loop;
        """ )

dbh.execute( service_id = sid,
             errtext = errtextbuff )


ErrorList is declared as a table of varchar2(255) and within the
validate procedure each row is set to a newline terminated error

When this is run I get the following error:

oci.error: (6502, 'ORA-06502: PL/SQL: numeric or value
error\012ORA-06512: at line 8\012')

If I change line 8 to read:
   :errtext := errs(i);

then I, correctly, get the last error message reported, followed by
the remaining space in the buffer, errtextbuff[0], set to "^@"s (ascii
0) padded to 1000 places.

Am I doing something wrong?  I would like to understand why the Buffer
object is not behaving as I would expect it to, rather than have
people suggest "why don't you not use a table for err", or "why don't
you use a real variable for the concatenation, then set :errtext equal
to that" (which is what I'm actually having to do).


Eric Brunson - brunson@level3.net - page-eric@level3.net  

"When governments fear the people there is liberty. When the people
fear the government there is tyranny." - Thomas Jefferson