How does a procedure get invalid? How can invalid procedures be found?

[former_SQLA_member1694901]

We have a problem with one specific procedure which often gets invalidated for unknown reasons. If the procedure is called from ISQL the message reported is "Procedure 'xxxx' is no longer valid".

When this status is reported, we usually call "ALTER PROCEDURE xxxx RECOMPILE", then everything is fine for some time.

I can't see things in this procedure which we don't do the same way in other procedures, which do not give us these troubles. Things we are using only occasionally include:

• The code selects a remote view into a local temporary table
• In the result set of the procedure one column is declared using the %TYPE clause

So the procedure looks like this:

CREATE PROCEDURE "MyUser"."PRC_MyProcedure"
(IN MyParameter integer)
RESULT
(
  ResultCode INTEGER,
  MyCategory MyUser.LocalTable.MyCategory%TYPE
)
BEGIN
  DECLARE LOCAL TEMPORARY TABLE MyResult
  ( 
    ResultCode INTEGER,
    MyCategory MyUser.LocalTable.MyCategory%TYPE
  );

  INSERT INTO MyResult(ResultCode, MyCategory)
    SELECT RemoteResult, RemoteCategory FROM MyUser.RemoteView;

  SELECT * FROM MyResult;
END

The first question is, what can get such a procedure into an invalid status?

The second question is, how can we detect that this problem has happened (beside calling the procedure end encountering an error)?

For views we use a statement like

  SELECT U.user_name + '.' + T.table_name cName
    FROM sysobject O, systab T, sysuser U 
   WHERE T.object_id = O.object_id 
     AND U.user_id = T.creator  
     AND O.status = 2      /* status=2 - Invalid */   
     AND O.object_type = 2 /* views */

to detect invalid views for recompilation, but the procedure in question does have status 1 in the SYSOBJECT table, like all other procedures, even when the error message is shown.

Is this intended? Are there any alternatives?

[awitter · Accepted solution]

Looking at the code I can only assume that it might be because MyUser.LocalTable.MyCategory has been changed or someone calling the procedure has no rights to MyUser.LocalTable.MyCategory. You could try to use the real type of MyUser.LocalTable.MyCategory for result column MyCategory and see if that changes anything.

[former_SQLA_member1694901 · Accepted solution]

I've replaced the %TYPE declaration by the standard declaration, I'm now waiting if the problem occurs again.

I'm quite sure that the type of the basic table has not changed, but I can confirm that %TYPE declarations were causes for ... surprising error messages in other circumstances.

[former_SQLA_member1694901 · Accepted solution]

FYI, the problem did not occur again, so I guess using %TYPE in result set declaration is indeed dangerous...

[awitter · Accepted solution]

As with other types (domains) it seems that any type other than the simple ones are just replaced when then procedure gets compiled. so %TYPE will be replaced with the real type. When that real type would change, there is a discerpancy between those two and the machine comes to a stop violently: create domain TAanm ROW(first integer, second integer); Create procedure SomeProcedure() begin declare @T TAanm; select @T.first, @T.second; end; This will work. However, changing TAanm WITHOUT replacing the procedure will invalidate the procedure. (Errors when executing)

So: Be careful with domains (%TYPE is domain too)

[VolkerBarth · Accepted solution]

As with other types (domains) it seems that any type other than the simple ones are just replaced when then procedure gets compiled.

Hm, the docs tell otherwise, unless a procedure would be compiled each time it is executed:

This is because %TYPE and %ROWTYPE are evaluated when the procedure is executed, not when it is created.

---

Note, I have rarely used those %TYPE and %ROWTYPE attributes msyself, so I have not stumbled upon the mentioned issues. In contrast, when trying to change an existing DOMAIN/DATATYPE, I do have stumbled upon the additional need to alter all existing columns that were declared with that DOMAIN/DATATYPE...