Re: Pro*C: Max no of indicator variables?

go_brien_at_my-deja.com wrote:

> 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.

I think the max size of a VARCHAR type is the largest value that can be held in an unsigned int.

--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C. Received on Sun Jun 06 1999 - 23:57:58 CDT