CODEWITHSUNDEEP
oracle-database
Adam Mrozek
Adam Mrozek

Reputation: 1480

Adding function to Oracle is not permament

I am experiencing weird behavior of Oracle Database 19c. I created user-defined function named raw_to_guid in SqlDeveloper for user 'TEST'. Content of this function is not important really because it's compiling successfully but just in case I share with code below.

create or replace function raw_to_guid( raw_guid in raw ) return varchar2
is
  hex varchar2(32);
begin

  hex := rawtohex(raw_guid);

  return substr(hex, 7, 2) 
      || substr(hex, 5, 2) 
      || substr(hex, 3, 2) 
      || substr(hex, 1, 2) 
      || '-'
      || substr(hex, 11, 2) 
      || substr(hex, 9, 2) 
      || '-'
      || substr(hex, 15, 2) 
      || substr(hex, 13, 2) 
      || '-'
      || substr(hex, 17, 4) 
      || '-'
      || substr(hex, 21, 12);

end

Next step is establish connection to Oracle database from my app and provide login data for user 'TEST'. I have several SELECT statements which use function raw_to_guid and all works perfectly fine.

Now is the clue of my problem: For first 3-4 calls my SELECT statements works but then (during the same operations) Oracle returns an error Package or function %s is in an invalid state. Again I execute CREATE OR REPLACE FUNCTION raw_to_guid... in SqlDeveloper and everything is working fine for short time but after few minutes the same exception occurs. It seems like Oracle automatically drops my user-defined function and I do not understand why because I have not any DROP FUNCTION statement in my code. The same problem occurs when I close database session in my app and create it again.

Is there a way to add this function permamently?

Upvotes: 0

Views: 49

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

It isn't dropped - you'd get a different error in that case. It is invalidated. Why? No idea.

Here's an example which shows what might have happened (only if your code looked like this):

Procedure (you don't have):

SQL> create or replace procedure p_test is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

Procedure is called from the function (line #5):

SQL> create or replace function raw_to_guid( raw_guid in raw ) return varchar2
  2  is
  3    hex varchar2(32);
  4  begin
  5    p_test;
  6    hex := rawtohex(raw_guid);
  7
  8    return substr(hex, 7, 2)
  9        || substr(hex, 5, 2)
 10        || substr(hex, 3, 2)
 11        || substr(hex, 1, 2)
 12        || '-'
 13        || substr(hex, 11, 2)
 14        || substr(hex, 9, 2)
 15        || '-'
 16        || substr(hex, 15, 2)
 17        || substr(hex, 13, 2)
 18        || '-'
 19        || substr(hex, 17, 4)
 20        || '-'
 21        || substr(hex, 21, 12);
 22
 23  end;
 24  /

Function created.

Does it work? Yes:

SQL> select raw_to_guid('abc') res from dual;

RES
----------
BC0A----

SQL>

But, if I drop the procedure, function - which references it - gets invalidated (error you got):

SQL> drop procedure p_test;

Procedure dropped.

SQL> select raw_to_guid('abc') res from dual;
select raw_to_guid('abc') res from dual
       *
ERROR at line 1:
ORA-06575: Package or function RAW_TO_GUID is in an invalid state


SQL>

If I recreate the procedure, function recompiles automatically and everything is OK again:

SQL> create or replace procedure p_test is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> select raw_to_guid('abc') res from dual;

RES
----------
BC0A----

SQL>

Code you posted (if that's the whole code) probably doesn't suffer from that; substr and rawtohex certainly aren't dropped or were somehow invalidated so that it would cause your function to also be invalidated (as a collateral victim).

What to do? When it fails again, don't just recreate it - check what's wrong by querying user_errors. For example:

SQL> drop procedure p_test;

Procedure dropped.

SQL> select raw_to_guid('abc') res from dual;
select raw_to_guid('abc') res from dual
       *
ERROR at line 1:
ORA-06575: Package or function RAW_TO_GUID is in an invalid state


SQL> select line, position, text from user_errors where name = 'RAW_TO_GUID';

      LINE   POSITION TEXT
---------- ---------- --------------------------------------------------
         5          3 PLS-00201: identifier 'P_TEST' must be declared
         5          3 PL/SQL: Statement ignored

SQL>

Hopefully, you'll find the culprit.

Upvotes: 1

Related Questions