Re: Pro*C and Oracle 8i, fetch coredumps

Christian,

I'm looking at your code. I haven't fixed it yet, but have you thought about rewriting the select/fetch portion so that you open a cursor, then fetch each record one at a time from within a loop, exiting the loop when NO DATA FOUND exception is hit? This is the standard practice, rather than doing one huge select into a finite structure. What happens when the data grows larger than your structure? Maybe this has already happened.

Paul Cluiss
Dallas, Texas

Christian Bantzer wrote:

> Hi,
>
> We are in the process of migrating from
> Oracle 7.3 to 8i. We are running into some
> problems with Pro*C programs. I have not much of a
> clue when it come to Pro*C, neither does our DBA.
>
> The problem manifests itself by coredumping, when a
> query result is fetched into a struc, only when the
> query returns more than a certain number of rows.
> The struc is large enough to hold all rows, which
> lead me to suspect some buffer size issue between Oracle
> and the C program. But again I don't really have a
> clue on Pro*C.
>
> If anyone has experienced similar problems, I would
> appreciate hearing about them. Also, on the off chance
> that somebody would be willing to look at the code
> I include it below.
> Compiled with cc: WorkShop Compilers 5.0 98/12/15 C 5.0
> on a Sun Solaris.
>
> It might be a compiler issue too, I am just using the
> sample makefile provided by Pro*C.
>
> Any hints where to look next would be great.
>
> Thanks
> Christian
>
> Pro*C code follows (posted via remarque, I hope they
> don't put in line breaks, just ignore if they do)
>
> #include <stdio.h>
> #include <string.h>
> #include <stdlib.h>
> #include <ctype.h>
> #include <sqlca.h>
>
> #define MAXREC 1200
> #define UIDLEN 20
>
> EXEC SQL BEGIN DECLARE SECTION;
> struct emp_stru
> { varchar ename[MAXREC][10];
> varchar job1[MAXREC][9];
> };
> varchar sqlstmt1[1000];
> EXEC SQL END DECLARE SECTION;
>
> struct emp_stru emprec;
>
> char logon_db_II();
> void sql_error(char *);
>
> /* MAIN PROGRAM */
> main(int argc, char *argv[])
> {
> logon_db_II(); /*logon to Oracle*/
>
> /* Hard code select statement. The program will core dump, if the number
> of rows is not limited
> to a number smaller than 136. The select statement is not supposed
> to make sense, it just
> retrieves two string constants for all matching rows. The point is
> to show that the core
> dumping depends on the number of rows returned.
> Change the rownum limit above 136 to see this program core dump!
> */
> sprintf(sqlstmt1.arr, "select 'asdf', 'ertt' from all_catalog where
> rownum < 136");
> sqlstmt1.len = strlen(sqlstmt1.arr);
> sqlstmt1.arr[sqlstmt1.len] = '\0';
>
> /* just showing select statement on the screen */
> printf("\nTrying to execute the following select statement:\n
> %s\n\n",sqlstmt1.arr);
>
> /* Print SQL error, should one occur */
> EXEC SQL WHENEVER SQLERROR DO sql_error("ar");
>
> /* Do the retrieval, do some debug printouts*/
> EXEC SQL PREPARE s1 FROM :sqlstmt1;
> EXEC SQL DECLARE c1 CURSOR FOR s1;
> EXEC SQL OPEN c1;
> printf("Cursor opened \n");
> EXEC SQL FETCH c1 INTO :emprec;
> printf("Cursor contents fetched into struc \n");
> EXEC SQL CLOSE c1;
> printf ("%-10s\n",emprec.ename[0].arr);
> printf("Cursor closed \n");
>
> EXEC SQL COMMIT WORK RELEASE;
>
> exit(0);
> }
>
> void sql_error(char *tname) /* Issue the Oracle error message */
> {
> char ora_msg[120];
> int tname_len, buf_len, msg_len;
>
> tname_len = strlen(tname);
> buf_len = sizeof(ora_msg);
> printf("\n\n***** Area Report terminating due to Oracle error on table
> %.*s :", tname_len,tname);
> sqlglm(ora_msg, &buf_len, &msg_len );
> printf("\n********* %.*s\n", msg_len, ora_msg );
> EXEC SQL WHENEVER SQLERROR CONTINUE;
> EXEC SQL ROLLBACK WORK RELEASE;
> exit(1);
> }
>
> char logon_db_II()
> /* Logon to Oracle */
> {
> EXEC SQL BEGIN DECLARE SECTION;
> varchar uid[UIDLEN];
> varchar pwd[UIDLEN];
> EXEC SQL END DECLARE SECTION;
>
> strcpy(uid.arr, "scott");
> strcpy(pwd.arr, "tiger");
> uid.len = strlen(uid.arr);
> pwd.len = strlen(pwd.arr);
>
> EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
> if (sqlca.sqlcode != 0)
> {
> if (sqlca.sqlcode == -1034)
> {
> printf("Oracle is not available\n"); exit(1);
> }
> else
> {
> printf("Error connecting to Oracle: %s\n", sqlca.sqlerrm.sqlerrmc);
> exit(1);
> }
> }
>
> return(printf("\nConnect to ORACLE as user %s\n", uid.arr));
> } /* End of logon_db_II */
>
> -**** Posted from RemarQ, http://www.remarq.com/?c ****-
> Search and Read Usenet Discussions in your Browser
Received on Mon Nov 15 1999 - 11:40:36 CST