Re: Pro*C -- PL/SQL - Problem

Is it performing an EXEC SQL CONNECT each time? If not, then whenever you get the error you should somehow indicate to the program that it needs to log on again before it tries another operation.

What I'd really suggest is that the daemon itself only do minimal setup work and then spawn a child which actually performs the work against the remote database. This child process should perform the EXEC SQL CONNECT and should conclude with something like EXEC SQL COMMIT WORK RELEASE; and the child would have to ensure that if it gets a connection that it also performs the RELEASE.

That way if you get a failure on an operation, the child will report the error, the child will exit and the daemon itself can be preparing other children. I suppose there is a minor problem of race conditions here, but that is just something that you would have to solve within the context of your particular operational environment. I once wrote a daemon that could have as many as 15 active child processes at any moment. I had to do a lot of fancy footwork with SIGCHLD and waitpid() combined with some polling of the active children (because my version of unix does not support signal queueing), but it works quite well and while occasionally errors do occur when the remote database is down, the original request is simply requeued until it is handled successfully.

Vo Thuan wrote:

> Program
> -----------
> I created a daemon which retrieve, when requested, data on DB (v7.3.4) (on
> another unix server). I used Pro*C(v.2.2.4) and Sql*Net (v.2.3.4) and PL/SQL
> (we have some packages on db server). Everything goes well when DB is up o
> another server.
>
> Problem
> -----------
> When db is down...
> At first requested, from daemon, we got this message...
>
> ORA-01034: ORACLE not available
> ORA-07429: smsgsg: shmget() failed to get segment.
> Digital Unix Error: 2: No such file or direct
>
> FINE!
>
> All other subsequent requests gave us this message...
>
> ORA-12154: TNS:could not resolve service name
>
> Even if DB turn ON, we got the same message... I have to stop (kill) my
> daemon and restart it to do more request.
Received on Fri Jun 18 1999 - 08:19:46 CDT