OCI 调用Oracle 存储过程实现
OCI调用存储过程 int ora_procedure_execute(structS_OracleContext*oractx,constchar*pszSQL,char*userid,char*exptype,char*conditionexp,char**resultof,int*errorcode,char**errormsg){
OCIBind * bidhp[6];
sb2 sb2aInd[3];
OCIStmt*stmthp=NULL;
swordretcode = 0;
sb4errcode = 0;
*resultof= NULL;
*errorcode=NULL;
*errormsg= NULL;
char *pszGBKSQL;
//数据库需要 GBK的SQL语句。因此需要转换字符集
pszGBKSQL= UTF8ToGBK(pszSQL);
if (!pszGBKSQL)
{
LogMessage("utf8 to gbk failed in ora_execute()");
return -1;
}
if (NULL ==oractx->envhp)
{
LogMessage("oractx->envhp==NULL inora_execute().");
delete pszGBKSQL;
return -2;
}
retcode= OCIHandleAlloc( (dvoid *) oractx->envhp,(dvoid **) &stmthp,OCI_HTYPE_STMT,(size_t) 0,(dvoid **) 0);
ora_check_error(oractx->errhp,retcode,&errcode);
if (0!=retcode || NULL==stmthp)
{
LogMessage("OCIHandleAlloc()failed in ora_execute().");
delete pszGBKSQL;
return -3;
}
retcode= OCIStmtPrepare(stmthp,oractx->errhp,(text *)pszGBKSQL,(ub4) strlen(pszGBKSQL),(ub4)OCI_NTV_SYNTAX,(ub4) OCI_DEFAULT);
ora_check_error(oractx->errhp,&errcode);
if(retcode!=OCI_SUCCESS&& retcode!=OCI_SUCCESS_WITH_INFO)
{
LogMessage("OCIStmtPrepare() failed %d,%d. reconnect ORACLE,sql=%s",errcode,pszGBKSQL);
if(errcode != 1756)
{
OCIHandleFree(stmthp,OCI_HTYPE_STMT);
ora_clean(oractx);
delete pszGBKSQL;
return -4;
}
LogMessage("Error 1756,minor error,ignore it.");
OCIHandleFree(stmthp,OCI_HTYPE_STMT);
delete pszGBKSQL;
return 0;
}
//绑定3个输入变量
if ((retcode=OCIBindByPos(stmthp,&bidhp[0],(ub4) 1,(dvoid *)userid,(sb4)(strlen(userid)+1),SQLT_STR,NULL,0,(ub4)OCI_DEFAULT))!=OCI_SUCCESS)
{
ora_check_error(oractx->errhp,&errcode);
LogMessage("ora_procedure_execute Error when OCIBindByPosuseridn");
return -9;
}
if ((retcode=OCIBindByPos(stmthp,&bidhp[1],(ub4) 2,(dvoid *)exptype,(sb4)(strlen(exptype)+1),&errcode);
LogMessage("ora_procedure_execute Error when OCIBindByPosexptypen");
return -9;
}
if ((retcode=OCIBindByPos(stmthp,&bidhp[2],(ub4) 3,(dvoid*)conditionexp,(sb4)(strlen(conditionexp)+1),&errcode);
LogMessage("ora_procedure_execute Error when OCIBindByPosconditionexpn");
return -9;
}
//定义3个变量存储输出参数
char szResult[4096];
memset(szResult,sizeof(szResult));
int nErrorcode = 0 ;
char szErrorMsg[4096];
memset(szErrorMsg,sizeof(szErrorMsg));
if ((retcode= OCIBindByPos(stmthp,&bidhp[3],(ub4)4,(dvoid*)(szResult),(sword)4096,&sb2aInd[0],&errcode);
LogMessage("ora_procedure_execute Error when OCIBindByNamebusiness_date n");
return -9;
}
if ((retcode=OCIBindByPos(stmthp,&bidhp[4],(ub4)5,(&nErrorcode),(sizeof(int)),SQLT_INT,&sb2aInd[1],&errcode);
LogMessage("ora_procedure_execute Error when OCIBindByNamebussiness_nodeid n");
return -9;
}
if ((retcode=OCIBindByPos(stmthp,&bidhp[5],(ub4)6,(dvoid*)(szErrorMsg),&sb2aInd[2],&errcode);
LogMessage("ora_procedure_execute Error when OCIBindByNamebusiness_date n");
return -9;
}
retcode= OCIStmtExecute(oractx->svchp,stmthp,(ub4)0,(OCISnapshot *)NULL,(OCISnapshot*)NULL,(ub4)OCI_DEFAULT);
ora_check_error(oractx->errhp,&errcode);
if (0!=retcode)
{
LogMessage("OCIStmtExecute() failed.retcode=%d,errcode=%d,sql:%s",pszGBKSQL);
if (OCI_ERROR==retcode&& errcode!=1756)
{
LogMessage("OCIStmtExecute() failed. erroroccured,disconnect");
OCIHandleFree(stmthp,OCI_HTYPE_STMT);
ora_clean(oractx);
delete pszGBKSQL;
return -5;
}
}
*errorcode= nErrorcode;
*errormsg= newchar[sizeof(szErrorMsg) + 1];
memcpy(*errormsg,(char*)szErrorMsg,strlen(szErrorMsg)+ 1);
*resultof= newchar[sizeof(szResult) + 1];
memcpy(*resultof,(char*)szResult,strlen(szResult)+ 1);
//commit
if((retcode=OCITransCommit(oractx->svchp,(ub4) 0)) !=OCI_SUCCESS)
{
ora_check_error(oractx->errhp,&errcode);
LogMessage("ora_dbbind_execute Error when OCIStmtCommitn");
return -6;
}
delete pszGBKSQL;
OCIHandleFree(stmthp,OCI_HTYPE_STMT);
return 0;
} (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |