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

It's not my area of expertise, but I think you should probably be using Oracle OCI calls to do the memory allocation and copying.

However, get the book by Tom Kyte - it has worked examples of this sort of thing.

Expert One on One: Oracle
By Wrox Press

http://www.amazon.co.uk/exec/obidos/ASIN/1861004826/o/qid=996688254/sr=2-1/r ef=sr_sp_bow_1_1/202-7823643-1963056

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Ivan Samuelson wrote in message
<9e3f3c6.0108010828.2621dd4c_at_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!