| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Pass a CLOB to an external procedure and pass changes back to calling procedure
Sorry, you've got a lot more reading to do yet!!!!! Here's wot I wrote, and resulted in me losing what hair I had left. This is for a unix implementation on 8.1.6, that i used to export blobs. This should get you half way. It's only similar, but should get you started. OK, it's a bit short on error handling, but, well there you go. Most of the processing was stolen from examples given either on the net, or in the documentation that is delivered with oracle.
As you can see, it's amoost like you're getting the data from a queue, and isn't a nice, clean 'C' structure at all. Once you've read it, you may change your mind on what you're trying to do!
Cheers,
Steve
#ifndef OCI_ORACLE
#include <oci.h>
#endif
#define MAXBUFLEN 16384
struct ocictx
{
OCIEnv *envhp; /* For OCI Environment Handle */ OCISvcCtx *svchp; /* For OCI Service Handle */ OCIError *errhp; /* For OCI Error Handle */ OCIStmt *stmtp; /* For OCI Statement Handle */ OCIStmt *stm1p; /* For OCI Statement Handle */ OCIBind *bnd1p; /* For OCI Bind Handle */ OCIBind *bnd2p; /* For OCI Bind Handle */ OCIBind *bnd3p; /* For OCI Bind Handle */ OCIDefine *dfn1p; /* For OCI Define Handle */ OCIDefine *dfn2p; /* For OCI Define Handle */ OCIDefine *dfn3p; /* For OCI DefineHandle */
void BlobToFile(
OCIExtProcContext *Context,
char *FileName,
short FileName_ind,
int FileName_len,
OCILobLocator *Image,
unsigned int ImageLen)
{
int Fileptr;
char FileStr [2048];
char TmpStr [2048];
sb4 Result;
int err;
ocictx oci_ctx;
ocictx *oci_ctxp = &oci_ctx;
ub4 amtp;
ub4 loblen;
ub1 bufp [MAXBUFLEN];
ub4 offset = 1;
sword retval;
ub4 remainder;
strcpy ( FileStr, "/parentdir/" );
strncat ( FileStr, FileName, strlen (FileName) );
Fileptr = open(FileStr, O_RDWR|O_CREAT, 0666);
(void) lseek ( Fileptr, 0, 0);
err = OCIExtProcGetEnv(Context,
&oci_ctxp->envhp,
&oci_ctxp->svchp,
&oci_ctxp->errhp);
(void) OCILobGetLength(oci_ctxp->svchp,
oci_ctxp->errhp, Image, &loblen);
amtp = loblen;
memset ( bufp, '\0', MAXBUFLEN);
retval = OCILobRead(oci_ctxp->svchp, oci_ctxp->errhp, Image,
&amtp, offset, (dvoid *) bufp,
(loblen < MAXBUFLEN ? loblen : MAXBUFLEN),
(dvoid *)0,
(sb4 (*)(dvoid *, const dvoid *, ub4, ub1)) 0,
(ub2) 0, (ub1) SQLCS_IMPLICIT);
switch (retval)
{
case OCI_SUCCESS: /* only one piece */
Result = write ( Fileptr, bufp, (size_t) amtp );
break;
case OCI_ERROR:
fprintf (stderr, "Error\n");
break;
case OCI_NEED_DATA:
Result = write ( Fileptr, bufp, (size_t) MAXBUFLEN );
remainder = loblen;
do
{
memset(bufp, '\0', MAXBUFLEN);
amtp = 0;
remainder -= MAXBUFLEN;
retval = OCILobRead(oci_ctxp->svchp,
oci_ctxp->errhp, Im
&amtp, offset, (dvoid *) bufp,
(loblen < MAXBUFLEN ? loblen : MAXBUFLEN),
(dvoid *)0,
(sb4 (*)(dvoid *, const dvoid *, ub4, ub1)) 0,
(ub2) 0, (ub1) SQLCS_IMPLICIT);
if (remainder < MAXBUFLEN)
{
Result = write ( Fileptr, bufp,
(size_t) remain
}
else
{
Result = write ( Fileptr, bufp,
(size_t) MAXBUF
}
}
while (retval == OCI_NEED_DATA);
break;
default:
fprintf (stderr, "Untrapped error\n");
break;
}
close ( Fileptr );
}
CREATE OR REPLACE LIBRARY WriteFile AS
'libWriteFile.so'
/
Procedure BlobToFile
(
Image IN BLOB,
ImageLen IN Binary_Integer,
FileName IN Varchar2
)
IS EXTERNAL
Library WriteFile
Name "BlobToFile"
Language C
With Context
Parameters
(
CONTEXT,
FileName String,
FileName Indicator short,
FileName Length Int,
Image OCILOBLOCATOR,
ImageLen Int
)
;
gcc -I$ORACLE_HOME/rdbms/demo -I$ORACLE_HOME/rdbms/public
-I$ORACLE_HOME/network/public -I$ORACLE_HOME/plsql/public -fPIC -Wall
-c WriteFile.c
gcc -shared -o $ORACLE_HOME/lib/libWriteFile.so WriteFile.o
On 1 Aug 2001 09:28:45 -0700, isamuels_at_columbus.rr.com (Ivan Samuelson) wrote:
>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 - 13:20:26 CDT
 |
 |