retriving number in a packed decimel format

From: Oussama Mkaabal <oussama_at_isg.co.il>
Date: Thu, 29 Oct 1998 14:06:48 +0200
Message-ID: <719lc7$nde$1@news.netvision.net.il>

I am trying to retrieve a column values that are in a number format into a packed decimal format SQLT_PDN buffer I keep getting this error "ORA-01457: converting column overflows decimal datatype" i succeeded in retrieving it in other many forms like SQLT_CHR ?(chars) floating point ... SQLT_FLT

I have wrote a c program that show what I am trying to do and what i get.

the c program

---

#include <stdio.h>
#include <string.h>
#include "oci.h"

#define SQL_STR "select empno from emp" /* the sql statment */
#define SERVER_NAME "oraserver" /*specify the server name */

#define USER_NAME "scott" /*specify the user name */ #define PASSWORD "tiger" /*specify the password */

long print_error(sword status,OCIError *errhp,char *context);

int
main()
{
 OCIEnv *envhp;
 OCIServer *srvhp;
 OCIError *errhp;
 OCISvcCtx *svchp;
 OCIStmt *stmthp;
 OCISession *usrhp;
 OCIDefine *defhp;
 ub2 scale;
 sword ret;
 unsigned char data[100] = { '\0' };
 sb2 indhp = 0;
 sb4 size;
 int type;
 double *pfltdata;
 text pdfmt[100];

 OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT, (dvoid *)0, (dvoid * (*)()) 0,
(dvoid * (*)()) 0, (void (*)()) 0 );

 OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp, (ub4) OCI_HTYPE_ENV,   0, (dvoid **) 0);

 OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 0, (dvoid **) 0 );

 OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR,   0, (dvoid **) 0);
 OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER,   0, (dvoid **) 0);

 OCIServerAttach( srvhp, errhp, (text *) SERVER_NAME, (sb4) strlen(SERVER_NAME), (ub4) OCI_DEFAULT);

 OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX,   0, (dvoid **) 0);

 /* set attribute server context in the service context */  OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
(dvoid *) srvhp, (ub4) 0,
(ub4) OCI_ATTR_SERVER, (OCIError *) errhp);

 /* allocate a user context handle */
 OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION,
(size_t) 0, (dvoid **) 0);

 OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
(dvoid *)USER_NAME, (ub4)strlen(USER_NAME),
  OCI_ATTR_USERNAME, errhp);

 OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
(dvoid *)PASSWORD, (ub4)strlen(PASSWORD),
  OCI_ATTR_PASSWORD, errhp);

 ret = OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_DEFAULT);  if(ret) {
  if(print_error(ret,errhp,"OCISessionBegin"))    goto end;
 }

 OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
(dvoid *)usrhp, (ub4)0,

  OCI_ATTR_SESSION, errhp);

 ret = OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
(ub4) OCI_HTYPE_STMT, 0, 0);

 if(ret) {
   if(print_error(ret,errhp,"OCIHandleAlloc stmt hanale pointer"))     goto end;
  }

 ret = OCIStmtPrepare(stmthp, errhp,
  SQL_STR, strlen(SQL_STR), OCI_NTV_SYNTAX, OCI_DEFAULT);

 type = SQLT_PDN;

 if (type == SQLT_FLT)
  size = sizeof(double);
 else
  size = 6; /*size for packed decimal is width*2+1 */

 scale = 0;
 sprintf(pdfmt,"%d.00",(size-1)*2);

 ret = OCIDefineByPos(stmthp, &defhp, errhp ,1,

    data , size ,type, &indhp, 0, 0, OCI_DEFAULT);  if(ret) {
  if(print_error(ret,errhp,"Attrset PDSCL"))    OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);   goto end;
 }

 if (type == SQLT_PDN) {
  /*ret = OCIAttrSet(defhp, OCI_HTYPE_DEFINE,    &pdfmt, 5, OCI_ATTR_PDFMT, errhp);
  if(ret) {
   if(print_error(ret,errhp,"Attrset PDFMT"))     OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);     goto end;
  }
 */ ret = OCIAttrSet(defhp, OCI_HTYPE_DEFINE,    &scale, 2, OCI_ATTR_PDSCL, errhp);
  if(ret) {
   if(print_error(ret,errhp,"Attrset PDSCL"))     OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);     goto end;
  }
 }

 ret = OCIStmtExecute(svchp, stmthp,
  errhp, 1, 0, NULL, NULL, OCI_DEFAULT);  if(ret) {
  if(print_error(ret,errhp,"OCIStmtExecute"))    goto end;
 }
 ret = OCIStmtFetch(stmthp, errhp, 1,
  OCI_FETCH_NEXT, OCI_DEFAULT);
 if(ret) {
  if(print_error(ret,errhp,"OCIStmtFetch"))    goto end;
 }

 pfltdata = (double*)data;

 printf("indicator value := %d\n%f\nend of float data\n%c\n%c\n%c\n%c\n%c\n%c\n%c\n%c\n%c\n%c\nend of char data\n%x\n%x\n%x\n%x\n%x\n%x\n%x\n%x\n%x\nend of byte data\n",   indhp,
  *pfltdata,

  data[0],
  data[1],
  data[2],
  data[3],
  data[4],
  data[5],
  data[6],
  data[7],
  data[8],
  data[9],

  data[0],
  data[1],
  data[2],
  data[3],
  data[4],
  data[5],
  data[6],
  data[7],

  data[8],
  data[9]) ;

end:

 OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);  OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT );

 ret = OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
 ret = OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
 ret = OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);

 return 0;

}

long print_error(sword status,OCIError *errhp,char *context) {
 ub4 counter = 1;
 ub4 errcode;
 sword ret;
 char errbuf[512 + 1] = {'\0'};

 switch (status) {
 case OCI_NEED_DATA:
  strcpy(errbuf, "Error - OCI_NEED_DATA");   break;
 case OCI_NO_DATA:
  strcpy(errbuf, "Error - OCI_NO_DATA");   break;
 case OCI_ERROR:
  break;
 case OCI_INVALID_HANDLE:
  strcpy(errbuf, "Error - OCI_INVALID_HANDLE");   break;
 case OCI_STILL_EXECUTING:
  strcpy(errbuf, "Error - OCI_STILL_EXECUTE");   break;
 case OCI_CONTINUE:
  strcpy(errbuf, "Error - OCI_CONTINUE");   break;
 default:
  break;
 }

 if (status == OCI_ERROR) {
  while (1) {
   ret = OCIErrorGet (errhp, (ub4) counter, (text *) NULL, &errcode,     errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);    if (strstr(errbuf, "ORA-00000"))
    return 0;
   printf( "%serrcode - %d from %s\n", errbuf, errcode, context);

   if (ret = OCI_NO_DATA)
    break;
   counter++;
  }
 }
 else
  printf( "%s from %s\n", errbuf, context);

    return 1;
} Received on Thu Oct 29 1998 - 06:06:48 CST