| 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: oci stored procedure parameter bind
Tom,
Thanks for your quick reply to my post. Unfortunately, your answer didn't seem to fix the problem. I have included with this message copies of the code used to generate the package procedure and C executable. I am using a library of oci calls that was written to emulate the PHP style oci calls to simplify coding. The function within the library that calls obndra has also been included:
PACKAGE dc_test
IS
TYPE ttchar16 IS TABLE OF VARCHAR2(16) INDEX BY BINARY_INTEGER;
PROCEDURE log_msg (in_msg_num IN messages.msg_num%TYPE,
in_msg_txt IN messages.msg_txt%TYPE,
results OUT NUMBER);
PROCEDURE test_oci (inout_txt IN OUT ttchar16);
END;
PACKAGE BODY dc_test IS
PROCEDURE log_msg (in_msg_num IN messages.msg_num%TYPE,
in_msg_txt IN messages.msg_txt%TYPE,
results OUT NUMBER) IS
BEGIN
results := 0;
INSERT INTO messages VALUES(in_msg_num, in_msg_txt, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Oracle Error ' || SQLERRM);
END log_msg;
PROCEDURE test_oci (inout_txt IN OUT ttchar16) IS
results NUMBER;
func_name VARCHAR2(16) := 'test_oci: ';
BEGIN
log_msg(1,
func_name || 'inout_txt 1:' || inout_txt(1), results);
log_msg(1,
func_name || 'inout_txt 2:' || inout_txt(2), results);
inout_txt(1) := 'Return Text 1';
inout_txt(2) := 'Return Text 2';
END test_oci;
END;
#include <dblib.h>
#include <db.h>
static oraConnection conn;
static oraCursor cursor;
#include <sys/types.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/time.h>
#include <ctype.h>
#include <signal.h>
#include <curses.h>
#include <term.h>
#include <errno.h>
#include <string.h>
#include <sys/shm.h>
#include "proto.h"
#include "myapp.h"
#include "app.h"
#include "myapplp.h"
#define MAX_RECS 2
sb2 indp[MAX_RECS];
ub2 alen[MAX_RECS];
ub2 arcode[MAX_RECS];
char inout_str[80];
char inout_text[MAX_RECS][16];
char ac_locprog[PRGNMSZ+1], /* local program name */
ac_locnode[SYSNMSZ+1], /* local program node */
ac_hostname[SYSNMSZ+1]; /* name of computer program is on */
char ac_logmsg[161];
char ac_sql[8192];
int main(argc, argv)
int argc;
char *argv[];
{
char *pc_locprog;
void cleanup(int);
/* --- Get the local system host name. --- */
gethostname(ac_hostname, sizeof(ac_hostname));
/* --- Get the program name from the runstring. --- */
if((pc_locprog = strrchr(argv[0], '/')) == NULL)
strcpy(ac_locprog, argv[0]);
else
strcpy(ac_locprog, ++pc_locprog);
/* --- Connect to the database. --- */
if(!ora_logon(&conn, (char *)pc_dbuserid)) {
fprintf(stderr,"Connect: %s\n",ora_error(&conn));
cleanup(EXIT_FAILURE);
}
if(!ora_open(&cursor,&conn)) {
fprintf(stderr,"Open: %s\n",ora_error(&cursor));
cleanup(EXIT_FAILURE);
}
strcpy(inout_text[0], "Some Text...");
strcpy(inout_text[1], "More Text...");
sprintf(ac_sql,
"BEGIN \n"
" dc_test.test_oci(\n"
" :inout_text\n"
" );\n"
"END;\n");
if(!ora_parse(&cursor, ac_sql)) {
fprintf(stderr,"ora_parse: %s\n", ora_error(&cursor));
cleanup(EXIT_FAILURE);
}
if(!ora_bindspvara(&cursor, ":inout_text", inout_text, 16, SQLT_STR,
indp, alen, arcode, MAX_RECS, "INOUT")) {
fprintf(stderr,"ora_bindspvar(:inout_text): %s\n",
ora_error(&cursor));
cleanup(EXIT_FAILURE);
}
if(!ora_exec(&cursor)) {
fprintf(stderr,"ora_exec:%s\n", ora_error(&cursor));
cleanup(EXIT_FAILURE);
}
printf("inout_text1=[%s]\n", inout_text[0]);
printf("inout_text2=[%s]\n", inout_text[1]);
cleanup(EXIT_SUCCESS);
}
/************************************************************************** **************************************************************************Function cleanup() -- Close data base, detach from shared memory segment,
shutdown UISG, close XCS, and exit the program.
**************************************************************************
**************************************************************************/
void cleanup(j_exitcode)
int j_exitcode;
{
/* Disconnect from Oracle */
if(!ora_close(&cursor)) {
sprintf(ac_logmsg,"Close: %s\n",ora_error(&cursor));
logmsg(SYS_MSGSEV, ac_locprog, ac_hostname, ac_logmsg);
}
sprintf(ac_logmsg,"Disconnect: %s\n",ora_error(&conn));
logmsg(SYS_MSGSEV, ac_locprog, ac_hostname, ac_logmsg);
}
exit(j_exitcode);
}
int ora_bindspvara(oraCursor *p_curs, char *pc_name, void *buf, size_t buflen,
sword type, sb2 *indp, ub2 *alen, ub2 *arcode, int
j_maxsiz,
char *pc_inout)
ORAERR();
if(strncmp(pc_inout, "IN", 2) == 0) {
for(j=0; j<j_maxsiz; j++) {
indp[j] = 0;
alen[j] = (ub2)buflen;
mycursiz = (ub4)j_maxsiz;
return obndra(&p_curs->cda, pc_name, -1, (ub1 *)buf, (sword) buflen, type,
-1, indp, alen, arcode, (ub4)j_maxsiz, &mycursiz,
(text *) 0,
-1, -1) ? ORA_FAIL : ORA_SUCCESS;
}
Thanks again for any help you can provide,
John Bullock
OCI Programmer (Wannabee)
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9gkpm602cu4_at_drn.newsguy.com>...
> In article <bfd9866f.0106180053.32c895cc_at_posting.google.com>,
> jbullock_at_neo.rr.com says...
> >
> >I am trying to get a standard C application to call an existing
> >database stored procedure using the OCI library. The program uses
> >function "obndra" to bind a parameter declared as "IN OUT" and "TABLE
> >OF VARCHAR2(16)" to a two dimensional C char array. When the
> >application is run, the following error is reported:
> >
> >error 1485 "compile bind length different from execute bind length"
> >
> >If I modify the stored procedure parameter to be OUT only, the C app
> >works fine.
> >
> >Can anyone tell me if you can bind to an "IN OUT" parameter this way?
> >
> >I would like to know if it's even possible to bind to an "IN OUT"
> >procedure parameter using "obndra". I have not included copies of the
> >software here because the application can be made to work, although it
> >would be more effective to leave the existing stored procedure as is.
> >I will supply stored procedure and C code upon request if more
> >information is required.
> >
> >Thanks in advance for any help you can give.
>
>
> 01485, 00000, "compile bind length different from execute bind length"
> // *Cause: You bound a buffer of type DTYVCS (VARCHAR with the two byte
> // length in front) and at execute time the length in the first two
> // bytes is more than the maximum buffer length (given in the bind
> // call). The number of elements in the array and the current number
> // of elements in the array cannot be more than the maximum size of
> // the array.
>
>
> that typically means the data in the array on the way in was un-initialized. It
> is customary to set the length fields to the maximum width for uninitialized
> data on the way in.
>
>
> Here is an example from a pro*c app that needed to dynamically call a procedure
> with a PLSQL table type:
>
> static void process()
> {
> Lda_Def lda;
> Cda_Def cda;
> char stmt[255];
> char ename[40];
> char data[50][255];
> short data_l[50];
> short data_i[50];
> short data_rc[50];
> int data_nelements;
> int i;
>
>
> sqllda( &lda );
>
> if ( oopen(&cda,&lda,NULL,-1,01,NULL,-1) ) oci_error(lda,cda);
>
> strcpy( stmt, "begin do_something( :p_table ); end;" );
>
> if ( oparse(&cda, stmt, -1, 0, 2 ) ) oci_error(lda,cda);
>
> /* init all array elements to NULL and set their max length */
> for( i = 0; i < sizeof(data)/sizeof(data[0]); i++ )
> {
> data_i[i] = -1;
> data_l[i] = sizeof(data[0]);
> }
>
> /* for the elements we have data for, fix the NULL indicator and
> put some data in there */
> for( data_nelements = 0; data_nelements < 15; data_nelements++ )
> {
> data_i[data_nelements] = 0;
> sprintf( data[data_nelements], "Some data %d", data_nelements );
> }
>
> /* Bind it */
> if (
> obndra( &cda, /* cursor data area */
> ":p_table", /* bind variable name */
> -1, /* indicates prev. parameter is C String */
> (void*)data, /* data array (plsql table) */
> sizeof(data[0]), /* sizeof each array element */
> STRING_TYPE, /* binding C strings in table */
> -1, /* scale (not used) */
> data_i, /* indicators for the i'th tbl entry */
> data_l, /* lengths of the i'th tbl entry */
> data_rc, /* return codes for the i'th tbl entry */
> sizeof( data )/sizeof(data[0]), /* MAXsize of array */
> &data_nelements, /* CURRENT size of array */
> (void*)0, /* not used */
> -1, /* not used */
> -1 ) /* not used */
> ) oci_error(lda,cda);
>
> /* run it */
> if( oexec( &cda ) ) oci_error(lda,cda);
>
> /* show it... */
> for( i = 0; i < data_nelements; i++ )
> printf( "Element(%d), Length = %d ind = %d = %s\n",
> i, data_l[i], data_i[i], data[i] );
> }
Received on Tue Jun 19 2001 - 12:56:15 CDT
 |
 |