Re: How to display status in long PL/SQL jobs?

zlm101_at_psu.edu (Zernan Martinez) wrote:
>I have a PL/SQL or SQL block that executes a fair amount of time.
>It goes something like this. . .
>.
>select A, sum(B)
>from TABLE1
>group by A
 

>There are about 100 different A's out in TABLE1
>and it takes about 30 minutes for it to finish.
>
>Can anybody show me a simple way of reporting the status of the query
>as it processes. Some thing like . . .
>
>A=1 done
>A=2 done
>.
>Job finished.
>

Hi there,

You can try the following in Pro*C

select_records()
{
EXEC SQL BEGIN DECLARE SECTION;
 VARCHAR a[10];
 int b=0;
EXEC SQL END DECLARE SECTION;    EXEC SQL DECLARE CUR1 CURSOR FOR
   SELECT A, SUM(B) FROM TABLE1 GROUP BY A;    EXEC SQL OPEN CUR1;    while(1)
   {
     EXEC SQL FETCH cur1 into :a, :b;

     if (sqlca.sqlcode <0) printf("\nError in sql %s",sqlca.sqlerrm.sqlerrmc);

     If (sqlca.sqlcode == 1403) break;

     a.arr[a.len] = '\0';

     printf("\nProcessed group %s Sum %d",a.arr,b);

   } /*end of while */  

} /* End of function */
Hope this routing helps to solve your problem

Regards

N.Prabhakar Received on Sun Dec 01 1996 - 00:00:00 CST