Re: HELP! ORA-0911 on ProC PREPARE statement

Stephen M. Deal (deal_at_tempus.Kodak.Com) wrote:
: I strcpy a query (e.g. select name from emp) into a character array
: (char select[100]). When it is parsed by the PREPARE statement I get
: ORA-0911, invalid character from sqlca.sqlerrm.sqlerrmc with the
: sqlca.sqlerrd array coming up all 0's (0,0,0,0,0,0). This implies that
: the parse error occurs on the first character.
 

: My select statement processes correctly in SQLplus but ProC cannot seem
: to digest the string. Has anyone experienced this problem and found the
: cause?
 

: Any help would be greatly appreciated!
:

You should be setting up any variables used in Oracle EXEC statements under the DECLARE SECTION at the beginning of your code. In the case of character arrays, define them as VARCHAR (ie VARCHAR select[100]). The Oracle precompiler will come along and change this to a structure of the form:

   struct {

      unsigned short len;
      unsigned char arr[100];
      } select;

You refer to structure name when used within EXEC statements (i.e :select). You refer to the individual elements when manipulating them with standard C. For example, you would refer to select.arr as the destination for your strcpy statement. Note that anytime you are responsible for initializing the value in {struct}.arr, you are also responsible for setting {struct}.len to the length of the string in {struct}.arr. A popular means of achieving this (rather than using strcpy) is to use the sprintf function which copies the string to its destination while returning the length of the string. (Example: select.len = sprintf(select.arr, "%s", "SELECT...").

Hope this helps.
Kevin

--
===========================================================================
Kevin J. Lukes  Programmer/Analyst   |  klukes_at_novatel.cuc.ab.ca
NovAtel Communications Ltd.          |  (403)295-4573
Calgary, Alberta CANADA              |  "Opinions expressed are my own"