Reading clobs from external c procedure called from PL/SQL

From: Andreas <andreas_at_volcanomail.com>
Date: 7 Feb 2002 03:25:30 -0800
Message-ID: <dad35c78.0202070325.7a12c3e3@posting.google.com>

I have problems reading clobs from Oracle 8.1.7.

In a session, the first call to OCILobRead() will work. All OCILobRead() calls after that will only read up to the length of the first read clob within that session.

OCILobGetLength() always returns the correct length of the clob. The data of the clob is correct, apart from being truncated.

I am a bit of a newbie when it comes to OCI and PL/SQL, and this really confuses me.

I have include the part of the C and PL/SQL.

Thanks for any suggestions on how to fix this.

Andreas

CLOB reading C code:

___CUT___

 // get the environment from the external procedure context

        if( OCIExtProcGetEnv( pWithContext, &envhp, &svchp, &errhp) !=OCI_SUCCESS){

		if( pError != NULL ){
			strcat(pError, "OCIExtProcGetEnv failed"  );
		}
		return -1;			
	} 	
  	
	// get the length of the CLOB and allocate a buffer
	if (OCILobGetLength(svchp, errhp, lobplain, &lenp) != OCI_SUCCESS){
		sprintf( tmpErr, "Unable to get plain length: (%s @ %d)|\n"
				, __FILE__, __LINE__ );
				
		if( pError != NULL ){
			strcat(pError, tmpErr );
		}
  		return -2;	
	}
	
	
	plain=(char*)malloc( (long)lenp +1 );
	memset ((void *)plain, '\0', (long)lenp+1);
	
	// read the clob until no more data is available
	do{
		err = OCILobRead(svchp, errhp, lobplain, &amtp, offset,
               (dvoid *) (plain+offset-1), (ub4)lenp , (dvoid *) 0,
               NULL, (ub2) 0, (ub1) SQLCS_IMPLICIT);
        
        if (err == OCI_SUCCESS || err == OCI_NEED_DATA){
	    	offset +=amtp;
	    }
	    

    } while (err == OCI_NEED_DATA);

___CUT___

#################################################################

PL/SQL c_rec.plain_message is the lob locator

___CUT___

DECLARE
  P_ERROR_MSG VARCHAR2(200);   CURSOR GET_PENDING_DIALOGUES IS

  SELECT dh.sender,
         dh.recipient,
         dh.copy_to,
         dh.blind_copy_to,
         dbms_lob.SUBSTR(dd.summary,4000,1) summary,
         dd.message plain_message,
         NULL fancy_message,
         NULL plain_include,
         dd.include_files fancy_include,
         dd.attach_files,
         dh.id                           
    FROM DIALOGUE_HEADERS dh,
         DIALOGUE_DATA dd
   WHERE dh.id = dd.dialogue_id
     AND dh.status = 'ACTIVE'
     AND dh.channel_id = 'EMAIL'

   order by dh.sender;

  l_rec get_pending_dialogues%ROWTYPE;
  l_test_length integer;
BEGIN for c_rec in get_pending_dialogues
loop
  Servmail2.SENDMAIL (

      p_senders           => c_rec.sender,
      p_from		  => c_rec.sender,
      p_recipients        => c_rec.recipient,
      p_copy_to           => c_rec.copy_to ,
      p_blind_copy_to     => c_rec.blind_copy_to,
      p_subject           => c_rec.summary,
      p_plain_message     => c_rec.plain_message,
      p_fancy_message     => NULL,
      p_includes_plain    => c_rec.plain_include,
      p_includes_fancy    => c_rec.fancy_include,
      p_attachments       => c_rec.attach_files,
      p_error_msg         => P_ERROR_MSG);

  l_test_length := dbms_lob.getlength(c_rec.plain_message);

  DBMS_OUTPUT.Put_Line('id: '||c_rec.id||' recip: '||c_rec.recipient||' P_ERROR_MSG = ' || P_ERROR_MSG);   DBMS_OUTPUT.Put_Line('length: '||l_test_length); end loop;
END; Received on Thu Feb 07 2002 - 05:25:30 CST