Pro*C: Max no of indicator variables?

From: <go_brien_at_my-deja.com>
Date: Thu, 03 Jun 1999 15:14:54 GMT
Message-ID: <7j6656$hld$1@nnrp1.deja.com>

Does anyone know if there is maximum number of host variables or indicator
variables that can be used in 1 sql statement in a Pro*C program?

I seem to be running into a limit at 22. When I use 22 I get the error message:
ORA-01036: Illegal variable name/number

Here are some code snippets to clarify my question:

  /* Put strings from include file in the queries */   data_query.len = sprintf(data_query.arr,adhoc_pos);   lookup_query.len = sprintf(lookup_query.arr,alook_pos);

  /* Check resulting lengths */
  if (data_query.len > 1880 || lookup_query.len > 1024) {     printf("Error: query too long for string definition.\n");     printf(" Data query: %d characters, lookup query: %d characters. \n",

        data_query.len,lookup_query.len);     return(FAIL);
  }
/* PREPARE statements */
EXEC SQL PREPARE STMT FROM :data_query;

EXEC SQL OPEN spc_data USING
:posin:v9,:posbm:v10,:posbm:v10,:stdate_in:v1,:endate_in:v2,:deptin:v3,
:merge_in:v4,:merge_in:v4,:lotin:v5,:lotin:v5,:operin:v6,:operin:v6,:shi
ftin
:v7,
:shiftin:v7,:machin:v8,:machin:v8,
:posin:v9,:posin:v9,:posin:v9,:posin:v9,:posin:v9,:posin:v9,:posbm:v10,:
posb
m:v10;

........................................................................

....
........
/* SQL statement used in prepare, */

char *adhoc_pos =
"select "

  "merge||'_'||mach||'_'||DECODE (SUBSTR(:v9,1,1),"
              "'B','B'||TO_CHAR( FLOOR ( (mach_pos-1)/:v10 ) + 1 ),"
              "'P','P'||TO_CHAR( FLOOR ( (mach_pos-1)/:v10 ) + 1 ),"
              "lpad(mach_pos,2,'0') ),"
  "to_char(max(bar_date),'MM/DD/YYYY HH24:MI:SS'),"
  "avg(test_value),"
  "max(test_value)-min(test_value),"

  "stddev(test_value),"
  "count(test_value) "
"from dnr_samples samp,dnr_bars bar "
"where bar_date between "
  "to_date(:v1,'MM/DD/YYYY ') AND to_date(:v2,'MM/DD/YYYY ') +86399/86400 AND
"

  "bar.bar_no = samp.bar_no and "
  "bar.status = 'COMPLETE' and "
  "dept = :v3 and "
  "samp.status||'' = 'COMPLETE' and "
  "(merge = upper(:v4) or :v4 = '*') and "
  "(lot = :v5 or :v5 = '*' ) and "
  "(oper = upper(:v6)  or :v6 = '*'  ) and "
  "(shift = upper(:v7) or :v7 = '*' ) and "
  "(mach||'' = upper(:v8) or :v8 = '*' ) and "
  "((:v9='B1' AND samp.mach_pos BETWEEN  1 "
  "AND  8 OR "
  "(:v9='B*') OR (:v9='P*') OR (:v9='*') OR "
  "(samp.mach_pos = upper(:v9)))"

"group by "

  "merge||'_'||mach||'_'||DECODE (SUBSTR(:v9,1,1),"
              "'B','B'||TO_CHAR( FLOOR ( (mach_pos-1)/:v10 ) + 1 ),"
              "'P','P'||TO_CHAR( FLOOR ( (mach_pos-1)/:v10 ) + 1 ),"
              "lpad(mach_pos,2,'0') ),"

  "merge_group";

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 03 1999 - 10:14:54 CDT