2 Replies Latest reply: Jun 14, 2012 1:46 AM by 943368 RSS

    DYNAMIC SQL

    943368
      Hi,

      I have written a dynamic sql query in PRO*C.
      varchar queryStr[1000];
      ...
      string sSqlStmt = "select distinct e.emp.id ..... from table e...where empid="23'..................................................................
      ....................................................................................................................................................................
      ....................................................................................";
      memset(queryStr.arr,0,sizeof(queryStr.arr));
      queryStr.len = sSqlStmt.length();
      sprintf ((char*)queryStr.arr, "%s", sSqlStmt.c_str());
      log_msg(DEBUG,1, "GPON QUERY: queryStr = [%s]", queryStr.arr);
      EXEC SQL WHENEVER SQLERROR goto errexit;
      EXEC SQL WHENEVER NOT FOUND continue;

      EXEC SQL PREPARE abc FROM :queryStr;
      EXEC SQL DECLARE cdf CURSOR FOR abc;


      log_msg(DEBUG,1, "in DB handler");

      EXEC SQL OPEN cdf;

      while(sqlca.sqlcode != SQLNOTFOUND)
      {
      EXEC SQL FETCH getDcsCurs INTO :.....

      }



      i am getting the ORA-00911: invalid character when this code is getting excetuted from application. I tried printing the queryStr in log file and executed it in sqlplus prompt. It does not generate any errors. I am fighting this for a long time now and did not finnd solution anywhere.

      p.s the way i have written sSqlStmt . its a mutliline string. I have added it alll at on shot. hope thats right.

      Another observation is the same select stmt works fine if I create the cursor with embedded SQL statement.
      Any help will be greatly appreciated.

      Thanks,
      Sahana

      Edited by: 940365 on Jun 13, 2012 2:57 AM

      Edited by: 940365 on Jun 13, 2012 3:05 AM