Pass a CLOB to an external procedure and pass changes back to calling procedure

From: Ivan Samuelson <isamuels_at_columbus.rr.com>
Date: 1 Aug 2001 09:28:45 -0700
Message-ID: <9e3f3c6.0108010828.2621dd4c@posting.google.com>

I want to call an external procedure to read data into a temporary clob and pass that back to the database.

What I've done is create an external procedure that accepts an OCILobLocator pointer (defined as OCILobLocator **myclob).

I allocate the temporary CLOB in a PL/SQL program and then call the external procedure. The external procedure reads the data from an MQSeries queue and then does a memcpy into the myclob variable as such:

    memcpy((*myclob), mybuf, strlen(mybuf));

When I print out the value in the external procedure of myclob, it shows that I copied the information somewhere. But, when I return back to the PL/SQL program and do a DBMS_LOB.SUBSTR(myclob), there's nothing there.

Is this possible? I know you can't share a CLOB between sessions, but my external procedure doesn't use any Pro*C or OCI calls other than using the definition of the OCILobLocator.

Here's the C program:

#include <stdio.h>
#include <stdlib.h>
#include <oci.h>

void loadclob (OCIClobLocator **myclob)
{

    FILE *fp;
    char mybuf[MAX_CLOB_LENGTH];
    int buf_len;
    int iamount = MAX_CLOB_LENGTH;
    char szMsg[250];
    fp = fopen("DEVCEA$DRA0:[USER.S010792.PROJECTS.XML]CLAIM77805.XML","r");

    if (!fp) {

        print_log ("Couldn't open file\n", 'W');
        exit(0);

    }
    fgets (mybuf, iamount, fp);
    fclose (fp);

    iamount = strlen(mybuf);
    sprintf (szMsg, "Size of clob = %d\n", strlen(myclob));     print_log (szMsg, 'W');
    sprintf (szMsg, "clob address = %ld\n", *myclob);     print_log(szMsg, 'A');
    print_log ("Copying to myclob...\n", 'A');     memcpy((*myclob), mybuf, iamount);
    print_log ("Copy successful...\n", 'A');     sprintf (szMsg, "myclob = %s\n", (char *)(*myclob));     print_log (szMsg, 'A');
}

Here's the PL/SQL library package to link to the external procedure:

CREATE OR REPLACE PACKAGE load_clob AS

    PROCEDURE MYLOADCLOB (buff IN OUT CLOB); END load_clob;
/

CREATE OR REPLACE PACKAGE BODY load_clob AS

    PROCEDURE MYLOADCLOB (buff IN OUT CLOB)

     IS EXTERNAL
       LIBRARY loadclob
       NAME "loadclob"
       LANGUAGE C
       PARAMETERS (buff OCILOBLOCATOR);

END load_clob;
/

And here's the PL/SQL program that calls the external procedure:

set serveroutput on size 100000
declare

    myclob CLOB;
    length NUMBER;
    mystring VARCHAR2(20000);
begin

    dbms_lob.CREATETEMPORARY(myclob,FALSE);
    dbms_lob.open (myclob, DBMS_LOB.LOB_READWRITE);
    load_clob.myloadclob(myclob);
    DBMS_OUTPUT.PUT_LINE('myclob = ' || dbms_lob.substr(myclob));

end;
/

I get the following output when I run the above PL/SQL program:

@loadclob
myclob =

PL/SQL procedure successfully completed.

SQL> Is this possible? The reason I'm doing this is because we are expecting XML messages of up to 2 megs in size that are sent to us via MQSeries. We want to use an external procedure library (MQ0I) that allows direct access to MQSeries from within PL/SQL, but it uses RAWs, so we're limited to messages of 32K.

This is with Oracle 8i (8.1.7).

Thanks! Received on Wed Aug 01 2001 - 11:28:45 CDT