Q: purity level vs sqlerrm

Hi All!

  // I've already sent this to the newsgroup but I don't see my article so I try it again...

  I have a serious problem, I need any solutions about it:

  We are developing a big application in Oracle 7.3.4, so user error codes between -20000 and -20999 are not enough. My solution for this problem was using a general error code and making a new raise_error() procedure which generates this general error code with different error text:

  procedure my_proc(prm number) is
  begin
    ...
    if ... then

• when error: my_error_package.raise_error('an_error'); end if; .. end;

  When I have to catch one of my exceptions I do the following:

  begin
    my_proc(...);
  exception
    if my_error_package.is_last_error('an_error') then

• handle error 'an_error' ... elsif my_error_package.is_last_error('another_error') then
• handle error 'another_error' ... else raise; end if; end;

  It's clear that I need to store last error in my_error_package.raise_error(). In an earlier version I store it into a package variable. But I _wanna_ use my stored procedures in select statements and my stored procedures use error handling, so I have to provide suitable purity level of my procedures. (pragma restrict_references, proc_name, WNDS, WNPS);)   My problem is that I can't store this. It's clear that I couldn't store it into a package variable because it would violate WNPS, I couldn't write it to database because it would violate WNDS. I wanted to store it into 'sqlerrm' issuing a raise_application_error with a suitable text parameter, but sqlerrm doesn't have suitable purity level.   There is an intresting phenomena with purity level. Referencing a global package variable in right side context is accepted when I have to provide (WNDS, WNPS) purity. But referencing a global variable of _another_ package in right side (!) context violates it. I don't know why. But I can write a public function with (WNDS, WNPS) purity in this another package to get this variable.
  So my idea is making a get_sqlerrm() function in STANDARD package which could provide me value of sqlerrm. How can I do it? Anyway, is this possible?

So, I need any advice, bye:
--

    .---,                            G o m b o s  B e r t a l a n
    :   :
    :---'-. ,---. ,--. ,-.-.       rendszerfejleszto, Oracle mernok
    :     : :--   :      ;          mailto:bgombos_at_freemail.c3.hu
   ,' '---' '---' '      `-'
-- : ------------------------- Az elet megis egy habostorta? --- '10^2-1