Re: Precompiler LOCK TABLE doesn't work

In article <1994Sep13.172827.21844_at_icf.hrb.com> mmm_at_icf.hrb.com (MAX M. MAGLIARO) writes:

> I have a PROFORTRAN application, under ORACLE 7.0.16.6.2, that does
> the following:
>
> EXEC SQL LOCK TABLE FRED IN EXCLUSIVE MODE
>
> There is no COMMIT, the code goes to sleep (but keeps running... it
> does NOT terminate) after this...
> There are no errors...
>
> but...
>
> the table isn't locked. I can still update/insert/delete from
> SQLPLUS in another window.
>
> This works if I issue the lock right at a SQLPLUS prompt.
>
> What's the problem?
>
> Yes, I have called ORACLE support. They are scratching their heads and
> looking at my code and I don't expect to hear from them any time soon,
> except what they already told me... "Gee, that SHOULD work..."
>
> So, if anyone has experienced this problem, please tell me what I
> am doing wrong.

From the statement "This works if I issue the lock right at a SQLPLUS prompt", it sounds like you have a share lock on the table within your SQL*Plus session. Try reconnecting in SQL*Plus (thus releasing the lock) and rerun the program.

A much more elegant solution would look something like this (pardon me I don't know FORTRAN and have no intention to learn):

/* tries is the number of times you want to try */ /* sleep_time is idle time between tries */

EXEC SQL whenever sqlerror continue;
while (1) {

    EXEC SQL lock table fred in exclusive mode nowait;

    if (sqlca.sqlcode == 54) {

       /* ORA-00054: resource busy and acquire with NOWAIT specified */
       if (i < tries) {
          sleep(sleep_time);
          i++;
       } else {
          /* we've waited enough... exit with error */
          fprintf(stderr, "%s\n", sqlca.sqlerrm);
          goto sqlerror;
       }
    } else if (sqlca.sqlcode && (sqlca.sqlcode != NO_ROW_FOUND))
       goto sqlerror;

/* now that table is locked, do whatever you want to do */

--
 Alvin W. Law .............................................. Oracle Corporation
 Project Leader, Cost Management System ........... 300 Oracle Parkway, Floor 6
 Manufacturing Applications .......................... Redwood Shores, CA 94065
 Email: alaw_at_us.oracle.com ............ Voice: 415.506.3390 . Fax: 415.506.7299

ORA-03113: end-of-file on communication channel