1 Reply Latest reply: Oct 11, 2012 9:59 AM by pgab RSS

    How to use OCILobWrite2 properly?

    pgab
      Hi everyone,

      I am pretty new to Oracle and Oracle Database development. In the database I have some C/C++ stored procedures. Some of them have a varchar2 output. Sadly this is limited to a specific length of characters. But these procedures sometimes produce very large output. That is why I wanted to use a lob as output. But I sadly did not make it to get some output into the lob.

      Here is how I want to write in the lob:
      extern "C" DLLEXPORT 
      void version(OCIExtProcContext* context, OCILobLocator **out, sb4 *ind_out){
           OCIEnv* envh;
           OCISvcCtx* svch;
           OCIError* errh;
           OCIExtProcGetEnv (context, &envh, &svch, &errh);
      
           std::string versioninfo = "104";
      
           oraub8 maxChar = 10;
           
           OCIDescriptorAlloc(envh, 
                       (dvoid **) out,
                       (ub4) OCI_DTYPE_LOB,       /* Type of Descriptor */
                       (size_t) 0, 
                       (dvoid **) 0);
      
           OCILobTrim2(svch, 
                    errh, 
                       *out,
                   (ub4)1);
                
           OCILobWrite2(/*svcctx*/svch, /*errh*/errh, /*ociloblocator*/*out,
                     /*byte_amtp*/NULL, /*char_amtp*/&maxChar, /*offset*/1,
                     /*bufp*/(void*)versioninfo.c_str(), /*buflen*/versioninfo.size(),
                        /*piece*/OCI_ONE_PIECE, /*context for callback*/NULL,
                     /*callback*/NULL, /*csid*/0, /*csfrm*/SQLCS_IMPLICIT);
           *ind_out = 0;
      }
      This is how I registered the function in Oracle Database:
      create or replace
      procedure version(versioninfo OUT clob) as
           external name "version" library myLib language c with context 
           parameters (context, versioninfo, versioninfo INDICATOR SB4);
      I call the stored procedure like this:
      declare
        res clob;
        begin
        -- the following doesnt help much
        --dbms_lob.createtemporary(res,true);
        version(res);
        dbms_output.put_line(res);
      end;
      This gives me just an empty line without the desired content of versioninfo.

      How can I use OCILobWrite2 properly?

      Regards
        • 1. Re: How to use OCILobWrite2 properly?
          pgab
          Solved it by using `OCILobCreateTemporary` before working with that CLOB.
                   OCIDescriptorAlloc(envh, 
                                          (dvoid **) out,
                                          (ub4) OCI_DTYPE_LOB,       /* Type of Descriptor */
                                          (size_t) 0, 
                                          (dvoid **) 0);
                   OCILobCreateTemporary(svch, errh, *out, 0, SQLCS_IMPLICIT, OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, OCI_DURATION_CALL);
                   ub4 amt = static_cast<ub4>(result.size());
                   OCILobTrim2(svch, 
                                  errh, 
                                  *out,
                                  (ub4)amt);
                   
                   oraub8 amtp = static_cast<oraub8>(result.size());
                   OCILobWrite2(/*svcctx*/svch, /*errh*/errh, /*ociloblocator*/*out,
                                   /*byte_amtp*/NULL, /*char_amtp*/&amtp, /*offset*/1,
                                   /*bufp*/reinterpret_cast<dvoid*>(const_cast<char*>(result.c_str())), /*buflen*/amt,
                                   /*piece*/OCI_ONE_PIECE, /*context for callback*/NULL,
                                   /*callback*/NULL, /*csid*/0, /*csfrm*/SQLCS_IMPLICIT);
                   *ind = 0;