HELP! RETURN CLOB from External DLL

From: wing chun <wchun>
Date: Thu, 27 Dec 2001 20:56:12 +0800
Message-ID: <a0f5qu$l2g1@hkunae.hku.hk>

I am working on an External DLL using OCI. Basically, PL/SQL calls an External DLL to process the data and then returns LOB back to the PL/SQL procedure. Inside the DLL, I created a Temporary lob to process the data. The error is prompted from PL/SQL:

SQL> exec isdb.test_clob;

           Before original length=10
          ORA-24805: LOB type mismatch
          ORA-06512: at "ISDBSVR.ISDB", line 24
          ORA-06512: at "ISDBSVR.ISDB", line 29
          ORA-06512: at line 1

So far I know that error is caused by the return from PLS_CLOB is not clob. "After_original := PLS_CLOB(before_original); {you will see more code in the below}" However inside the DLL, I have defined OCIClobLocator and OCI_TEMP_CLOB to carry the CLOB data.

I have found the following observations: 1. If I change everything to blob, it works fine. (such as OCILobLocator, OCI_TEMP_CLOB and all variables in PL/SQL) 2. If I run the same DLL and PL/SQL in 9i, it works fine (clob too)

I guess it would be a bug for clob, please help to check thanks

CODE - PL/SQL CREATE OR REPLACE PACKAGE BODY "ISDBSVR"."ISDB" as Function PLS_CLOB(var_clob in clob) RETURN clob IS EXTERNAL LIBRARY bb WITH CONTEXT NAME "Encrypt_Blob" LANGUAGE C PARAMETERS ( CONTEXT, var_clob, var_clob INDICATOR SB4, RETURN INDICATOR SB4 );

PROCEDURE TEST_CLOB IS
  before_original cLOB;
  After_original cLOB;
  CHAR_TO_CLOB varchar(200);
  bsize int;
BEGIN
CHAR_TO_CLOB := 'AAAAAAABBBBBBCCCCCCC';   dbms_lob.createtemporary( before_original, TRUE );   DBMS_LOB.write( before_original,10,1,CHAR_TO_CLOB);   bSize := DBMS_LOB.GetLength(before_original);   DBMS_OUTPUT.PUT_LINE('Before original length='||bSize);   After_original := PLS_CLOB(before_original);   bSize := DBMS_LOB.GetLength(After_original);   DBMS_OUTPUT.PUT_LINE('After original length='||bSize);   dbms_lob.freetemporary(before_original); END;
END ISDB; /* CODE - DLL */
_EXPORT OCILobLocator * Encrypt_Blob(OCIExtProcContext *with_context,

              OCILobLocator *plaintext2,
                sb4 pt_indicator2,
                sb4 *ret_indicator
               )

{ FILE *fp;
OCIClobLocator *cipherbb;
ub4 amount;

 ocictx         oci_ctx;
 ocictx         *oci_ctxp = &oci_ctx;
 status = OCIExtProcGetEnv(with_context,
                         &oci_ctxp->envhp,
                         &oci_ctxp->svchp,
                         &oci_ctxp->errhp);
 status = OCIDescriptorAlloc(oci_ctxp->envhp,
                           (dvoid **) &cipherbb,
                           (ub4) OCI_DTYPE_LOB,
                           (size_t) 0,
                           (dvoid **) 0);
 status = (int) OCILobCreateTemporary(oci_ctxp->svchp,
                            oci_ctxp->errhp, cipherbb, OCI_DEFAULT,
OCI_DEFAULT,
                            OCI_TEMP_CLOB, OCI_ATTR_NOCACHE,
                            OCI_DURATION_SESSION);

amount = 6;
status = OCILobCopy(oci_ctxp->svchp,

                   oci_ctxp->errhp,
                   cipherbb,
                   plaintext2,
                   amount,
                   (ub4) 1,
                   (ub4) 1);

*ret_indicator = OCI_IND_NOTNULL;

OCIDescriptorFree((dvoid *) cipherbb, (ub4) OCI_DTYPE_LOB); return(cipherbb); Received on Thu Dec 27 2001 - 06:56:12 CST