OCI, proc calls and nested tables

[sedj]

Hi,

I am having a nightmare using OCI to map to an object via a proc call.

Anyone ever had any joy ? On execution I get an OCI_ERROR, but the message just says "ORA-00000 :normal, successful completion", but I know the proc does not execute because an insert into a log table does not occur.

Would appreciate any help on this, or, of how to map onto a nested table object ...

As usual the OCI prog guide is bewildering !

		struct masterMapData {
			  OCINumber  code;
			  OCIString  *coordinates;
		};
		typedef struct masterMapData masterMapData;

		struct masterMapData_ind
		{
			OCIInd _atomic;
			OCIInd code;
			OCIInd coordinates;
		};
		typedef struct masterMapData_ind masterMapData_ind;




			sword     status;
			int in_x = 292500;
			int in_y = 92500;
			int in_width = 200;
			int in_height = 200;
			int i, nrows = 0,
			rows_fetched = 0,
			rows_processed = 0,
			rows_to_process = 0;
			boolean   has_more_data = TRUE;

			/* parse query */
		  /* the stored procecdure call */
			char * stmt =
				"BEGIN\n"
				"	BEN.READ7(:in_x, :in_y, :in_width, :in_height, :mm_data);\n"
				"END;\n";
			prepareStatement(stmt);

			bindByName(":in_x", &in_x, sizeof(in_x), SQLT_INT);
			bindByName(":in_y", &in_y, sizeof(in_y), SQLT_INT);
			bindByName(":in_width", &in_width, sizeof(in_width), SQLT_INT);
			bindByName(":in_height", &in_height, sizeof(in_height), SQLT_INT);

			masterMapData		*mm_data_obj[ARRAY_SIZE];
			masterMapData_ind	*mm_data_ind[ARRAY_SIZE];
			OCIDefine *defn1p = NULL;

			bindByName(":mm_data", &mm_data_obj, 0, SQLT_NTY);

			printf("OCIDefineByPos\n");
			checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4)2,
				(dvoid *)0, (sb4)0, SQLT_NTY, (dvoid *)0,
				(ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT));
			printf("OCIDefineObject\n");

			OCIType* mm_tdo = get_tdo("t_mm_tab");

			checkerr(errhp, OCIDefineObject(defn1p, errhp, mm_tdo,
				(dvoid **)mm_data_obj, (ub4 *)0,
				(dvoid **)mm_data_ind, (ub4 *)0));

			// execute
			printf("Executing ...");
			status =  OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
						(OCISnapshot *)NULL, (OCISnapshot *)NULL,
						(ub4)OCI_DEFAULT);

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[sedj]

Found it ...

OCIType   *typeDesc  = get_tdo("mastermap.ben_array");
OCITable  *out_array;
OCIBind   *bndp     = 0;  /* bind handle                     */
OCINumber cnt;            /* number of elements in the array */
OCIIter *iterator; /* after the array is bound, use it to create an oci iterator
					* so it can be traversed using Iter function
					*/

OCIInd *element_ind; /* element of the array */

//checkerr(errhp, OCITypeByName(envhp,errhp,svchp,(text*)"mastermap",strlen("mastermap"),(text*)"ben_array",strlen("ben_array"),0,0, OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,&typeDesc));
printf("OCIBindByName\n");
checkerr(errhp,OCIBindByName(stmthp,&bndp,errhp,(text*)":out_array",(sb4)-1,(dvoid*)&cnt,(sb4)sizeof(OCINumber),SQLT_NTY,0,0,0,0,0,OCI_DEFAULT));
printf("OCIObjectNew\n");
checkerr(errhp, OCIObjectNew(envhp,errhp,svchp,OCI_TYPECODE_TABLE,typeDesc,0,OCI_DURATION_DEFAULT,TRUE,(dvoid**)&out_array));
printf("OCIBindObject\n");
checkerr(errhp, OCIBindObject(bndp,errhp,typeDesc,(dvoid**)&out_array,0,0,0));

printf("Executing ...");
status =  OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
			(OCISnapshot *)NULL, (OCISnapshot *)NULL,
			(ub4)OCI_DEFAULT);

where get_tdo() is :

OCIType *get_tdo(char* _typename) {
	OCIParam *paramp = NULL;
	OCIRef *type_ref = NULL;
	OCIType *tdo = NULL;

	checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)_typename,
		(ub4)strlen((char *)_typename),
		OCI_OTYPE_NAME, (ub1)1,
		(ub1)OCI_PTYPE_TYPE, dschp));
	checkerr(errhp, OCIAttrGet((dvoid *)dschp, (ub4)OCI_HTYPE_DESCRIBE,
		(dvoid *)&paramp, (ub4 *)0,
		(ub4)OCI_ATTR_PARAM, errhp));
	checkerr(errhp, OCIAttrGet((dvoid *)paramp, (ub4)OCI_DTYPE_PARAM,
		(dvoid *)&type_ref, (ub4 *)0,
		(ub4)OCI_ATTR_REF_TDO, errhp));
	checkerr(errhp, OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *)0,
		OCI_PIN_ANY, OCI_DURATION_SESSION,
		OCI_LOCK_NONE, (dvoid **)&tdo));
	if (!tdo)
	{
		fprintf(stderr, "Null tdo returned for type %s.\n", _typename);
		disconnect();
	}

	return tdo;
}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[pr0n603]

hi sedj,

Im sorry i dont have a solution to your problem. I was trying get in touch with you and coudnt find any other way. I have a question about a code u posted in

http://www.tek-tips.com/viewthread.cfm?qid=881282.

I set up my own thread regarding that at

http://www.tek-tips.com/viewthread.cfm?qid=968204&page=1

Please, if you could, check out the and see if you can help me.

I really appreciate it!

Good luck with your C programming.