Home » Developer & Programmer » Precompilers, OCI & OCCI » call a stored procedure with varchar2 parameter
call a stored procedure with varchar2 parameter [message #94417] Tue, 19 October 2004 09:16
Arne Adams
Messages: 5
Registered: October 2004
Junior Member
Hi,

the following code crashs when OCIStmtExecute gets called in oracommon8 (windows). What is wrong?

note that all works fine, when the procedure is executed using SQLPlus.

Many thanks in advance,

Arne

The stored procedure used is defined by:

create or replace procedure proc_with_in_param(newName in varchar2) as

begin

update oci_test set name = newName;

end;

/

 

static OCIEnv    *p_env;
static OCIError   *p_err;
static OCISvcCtx   *p_svc;
static OCIStmt    *p_sql;
static OCIDefine   *p_dfn  = (OCIDefine *) 0;
static OCIBind    *p_bnd  = (OCIBind *) 0;

int main()
{
 int     rc;
 char     errbuf[[500]];
 int     errcode;
 char const statementText[[]] = "BEGIN
          proc_with_in_param(:obsolete);
          END;";
 char const theo[[]] = "theo";
 
 rc = OCIInitialize( OCI_THREADED | OCI_OBJECT, NULL, NULL, NULL, NULL);
 
 rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );
 
 rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
  (size_t) 0, (dvoid **) 0);
 rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
  (size_t) 0, (dvoid **) 0);
 
 rc = OCILogon(p_env, p_err, &p_svc, (OraText *)"user", 5, (OraText *)"passwd", 7, (OraText *)"database", 8);
 if (rc != 0) {
  OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText *)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
  printf("Error - %.*sn", 512, errbuf);
  exit(8);
 }
 
 rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
  OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
 
 rc = OCIStmtPrepare(p_sql, p_err, (OraText *)statementText,
  (ub4) strlen(statementText), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
 if (rc != 0) {
  OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText *)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
  printf("Error - %.*sn", 512, errbuf);
  exit(8);
 }

 OCIString* oshi = NULL;
 if(OCIStringAssignText(p_env, p_err, (text*)theo, strlen(theo),&oshi) !=0)
 {
  OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText *)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
  printf("Error - %.*sn", 512, errbuf);
  exit(8);
 }

 rc = OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":obsolete",
  -1, (dvoid*) oshi, strlen(theo) + 1, SQLT_VST, (dvoid *) 0,
  (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
 if (rc != 0) {
  OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText *)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
  printf("Error - %.*sn", 512, errbuf);
  exit(8);
 }
 
 rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
  (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
 if (rc != 0) {
  OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText *)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
  printf("Error - %.*sn", 512, errbuf);
  exit(8);
 }
 
 rc = OCILogoff(p_svc, p_err);           /* Disconnect */
 rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);   /* Free handles */
 rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
 rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
 return 0;
}
Previous Topic: Thread-c+
Next Topic: c++/ Oracle 8i database connectivity
Goto Forum:
  


Current Time: Thu Mar 28 20:54:32 CDT 2024