Problem w/ Database Procedures

From: <davidc_at_alleleb.berkeley.edu>
Date: 6 Apr 1994 15:36:58 GMT
Message-ID: <2nukuq$m1h_at_agate.berkeley.edu>

I've run into an odd problem here and I hope someone in the newsgroup can give me a word of advice. The environment is ORACLE7 server release 7.0.16.4.0 with procedural, distributed, and parallel server options, running under SunOS 4.1.2 on Sun4's. It seems that select statements in our database procedures have stopped working.

The following procedure is an example.

create or replace procedure is_supvr (is_ok in out number) as

    cursor ROLES is select role from session_roles; begin

    for r in ROLES loop

        if r.role = 'SUPERVISOR' then
            is_ok := 1;
            exit;
        end if;

    end loop;
end;

Until last week, the code worked, and it still works if you enter it at the SQL*Plus prompt; that is, the 'select role from session_roles' loads the cursor with the user's roles, which might be 'DEVELOPER', 'SUPERVISOR', and so forth, and the loop detects the appropriate role. However, in the DB procedure, the 'select' loads the cursor with a single row of data, apparently corrupted -- it contains 'SU' and nothing else.

It sort of looks as if the PL/SQL parser still works but the compiler's broken. Our DBA is scratching his head. Any ideas?

Thanks for your attention.

David Clement

---

davidc | "One should, each day, try to hear a little song, read a good _at_alleleb | poem, see a fine picture, and, if it is possible, speak a few
.berkeley | reasonable words."
.edu | --Goethe

---

Received on Wed Apr 06 1994 - 17:36:58 CEST