Visit Our UNIX and Linux User Community


Problem FETCHing Long data type using CURSOR


 
Thread Tools Search this Thread
Top Forums Programming Problem FETCHing Long data type using CURSOR
# 1  
Old 10-29-2009
Problem FETCHing Long data type using CURSOR

Currently my Pro*c program is fetching a cloumn which is defined as LONG in oracle db. The data in the column is around 65k. But when I am FETCHing it to a varchar[200kb] variable, I am only getting 22751 bytes of data using cursor.

Is there any limitation on the data which is fetched by a cursor in pro*C or do I need to do some thing more in coding to get all 65k of data present in the table column.

RVSN_DESC_LEN = 200*1024+1;
ETT_RVSN_TEXT.RVSN_DESC column contains 65k size data

Here is the code snippet:
Code:
VARCHAR rvsnLablName[RVSN_LABL_NAME_LEN];
    short rvsnLablName_ind = 0;
    VARCHAR rvsnLablText[RVSN_LABL_TEXT_LEN];
    short rvsnLablText_ind = 0;
    VARCHAR rvsnDesc[RVSN_DESC_LEN];
    short rvsnDesc_ind = 0;
 
:
:
 
EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO error;
  EXEC SQL DECLARE sbdvRvsnDescInfoCur CURSOR FOR
 SELECT 
  ETT_CELL_HDR.CELL_HDR_CODE, 
  ETT_CELL_HDR.CELL_HDR_TTL, 
  ETT_RVSN_TEXT.RVSN_DESC
 FROM
  ETT_CELL_HDR,
  ETT_RVSN_TEXT
 WHERE
  ETT_RVSN_TEXT.GENL_ORD_ID = :genlOrdID_i AND
  ETT_CELL_HDR.CELL_HDR_ID = ETT_RVSN_TEXT.CELL_HDR_ID(+)
 ORDER BY 
  ETT_CELL_HDR.PRNT_SEQ_NBR, ETT_RVSN_TEXT.PRNT_SEQ_NBR;
  EXEC SQL OPEN sbdvRvsnDescInfoCur;
  EXEC SQL WHENEVER NOT FOUND DO break;
  i = 0;
  *rvsnDescInfo_iop = NULL;
  for (;;)
  {
#ifdef DEBUG
    fprintf(outFile_gp, "reallocing, i = %d\n", i);
    fprintf(outFile_gp, "sizeof(RvsnDescInfoType) = %d\n", sizeof(RvsnDescInfoType));
    fflush(outFile_gp);
#endif /* DEBUG */
    *rvsnDescInfo_iop = (RvsnDescInfoType*) realloc(*rvsnDescInfo_iop, 
    (i+1)*sizeof(RvsnDescInfoType));
    if (NULL == *rvsnDescInfo_iop)
    {
      fprintf(stderr, "db_retrieveAddRvsnDescInfo: realloc() failed. errno = %d\n", errno);
      fflush(stderr);
      return FAIL;
    }
    memset(&rvsnDescInfo_iop[0][i], '\0', sizeof(RvsnDescInfoType));
    EXEC SQL FETCH sbdvRvsnDescInfoCur
    INTO
 :rvsnLablName:rvsnLablName_ind,
 :rvsnLablText:rvsnLablText_ind,
 :rvsnDesc:rvsnDesc_ind;
    rvsnLablName.arr[rvsnLablName.len] = '\0';
    rvsnLablText.arr[rvsnLablText.len] = '\0';
    if (rvsnDesc.len < RVSN_DESC_LEN)
    {
      rvsnDesc.arr[rvsnDesc.len] = '\0';
    }
    else
    {
      rvsnDesc.arr[RVSN_DESC_LEN - 1] = '\0';
    }

#ifdef DEBUG
    fprintf(outFile_gp, "rvsnLablName.len = %d\n", rvsnLablName.len);
    fprintf(outFile_gp, "rvsnLablText.len = %d\n", rvsnLablText.len);
    fprintf(outFile_gp, "rvsnDesc.len = %d\n", rvsnDesc.len);
    fflush(outFile_gp);
#endif /* DEBUG */
    if (-1 != rvsnLablName_ind)
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablName, rvsnLablName.arr);
    else
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablName, " ");
    if (-1 != rvsnLablText_ind)
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablText, rvsnLablText.arr);
    else
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablText, " ");
    if (-1 != rvsnDesc_ind)
      strcpy(rvsnDescInfo_iop[0][i].rvsnDesc, rvsnDesc.arr);
    else
      strcpy(rvsnDescInfo_iop[0][i].rvsnDesc, " ");
    i++;
  }
  EXEC SQL CLOSE sbdvRvsnDescInfoCur;
  return i;

When I display rvsnDesc.arr, I get 22751 as size.

Any help in this issue will be highly appreciated.

Thanks!

Last edited by pludi; 10-29-2009 at 11:31 AM.. Reason: code tags, please...
# 2  
Old 10-29-2009
It may be a data problem - if there is an ASCII NUL ( ASC(0)) character embedded in the data, C will terminate the string at that point. You are using strcpy() which will do that for example.

FWIW: avoid LONG, it has numerous issues; use CLOB datatype instead.
Oracle LONG CLOB Convert
# 3  
Old 10-29-2009
Thanks Jim for replying.

This is an old application I am maintaining so people dont want to move it to CLOB.

Regarding the data problem, it does not seems to be it as the data which is getting cut is a single word.

eg. at 22751 bytes data there is this word "...DOWN RESPONSE" and I am getting till "...DOW". So it does not seem to be ascii(0) issue.

Cheers!

Previous Thread | Next Thread
Test Your Knowledge in Computers #860
Difficulty: Medium
Vue.js is an open-source JavaScript framework for building user interfaces and single-page applications.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extracting LONG Data Type from DB via UNIX Script

Hi, I want to extract a XML file which is stored in the database having a data Type as "LONG" via UNIX Scripting. But when i am triggering the SQL via UNIX it is fetching only the first Line and not the complete XML. Can you please suggest if the parameters that i have used needs any... (2 Replies)
Discussion started by: Barbara1234
2 Replies

2. Shell Programming and Scripting

Fetching the required data out of a tabular form

Hello Gurus, I am trying to fetch a required number of lines from an output of a command which is in tabular form. Below is the command for reference along with how the result is being shown on UNIX shell. /usr/openv/volmgr/bin/vmquery -b -p 5 The result of the above command is as... (6 Replies)
Discussion started by: Ali Sarwar
6 Replies

3. Red Hat

Double data type C Red Hat platform problem

I'm converting a binary file to ASCII using c code. The folllowing block of code prints correct double value 00000.000000000 on HPUNIX platform. longi double; /* C79 - Price Per Minute */ memcpy(&longi,&rbuff,8); fprintf(wfp,"%015.9f ",longi); prints : 00000.000000000 ... (6 Replies)
Discussion started by: krk
6 Replies

4. Shell Programming and Scripting

Fetching data particular data from the file.

Hi All, I have one fine which contaning the record as below, I need to fetch the number which is in Bold and from second line only URL need to pick. :ABCD C7QyzBXIs58k 1 fdmlksdlkfn ... (4 Replies)
Discussion started by: Riverstone
4 Replies

5. Shell Programming and Scripting

Fetching data from file

Hi All, I'm facing issue while using script. Chk_Etl_Status=`cat /dstage/questnet/qnetdv/input/Etl_Status.dat|cut -d"," -f1` echo Chk_Etl_Status=$Chk_Etl_Status above result is giving me sometime value as 1 and sometime error message as Chk_Etl_Status= dsjoblaunch.sh: test: Specify a... (5 Replies)
Discussion started by: Amit786
5 Replies

6. Shell Programming and Scripting

Fetching data from .csv file

Hi Experts, I have created a table with columns as empname,empid,phone,shiftname. Now I am having a .csv file format which contains the shift datas of the employees. I have to fetch this file and compare with the table I created to send an alert to the specified user. (2 Replies)
Discussion started by: micky3112
2 Replies

7. Shell Programming and Scripting

Extracting LONG Data Type from DB via UNIX Script

Hi, I want to extract a XML file which is stored in the database having a data Type as "LONG" via UNIX Scripting. But when i am triggering the SQL via UNIX it is fetching only the first Line and not the complete XML. Can you please suggest if the parameters that i have used needs any... (0 Replies)
Discussion started by: dear_abhi2007
0 Replies

8. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies

9. Shell Programming and Scripting

fetching data from sybase using perl

How can I fetch and retreive data(of each result set) of multiple result set from sybase by calling a stored procedure using perl DBI module on an unix solaris environment????? Please help (3 Replies)
Discussion started by: wadhwa.pooja
3 Replies

10. Programming

enable 64bit long type for gcc

hey, I believe I once saw a post in this forum, about enable an GCC option to enable long types. I simply cannot find it any more. Can anybody give me a hint? I am on 32bit Ubuntu, and I would like my int be really long. Also I need malloc() take long int argument too. I found it is necessary to... (6 Replies)
Discussion started by: patiobarbecue
6 Replies

Featured Tech Videos