Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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?

asked 15 Sep '23, 04:03

tedfroehlich's gravatar image

tedfroehlich
38071123
accept rate: 18%


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.

permanent link

answered 18 Sep '23, 02:26

ArcoW's gravatar image

ArcoW
2613415
accept rate: 0%

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.

(18 Sep '23, 03:39) tedfroehlich

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

(29 Nov '23, 03:18) tedfroehlich

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)

(29 Nov '23, 03:28) ArcoW
Replies hidden

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...

(29 Nov '23, 04:27) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×248
×125

question asked: 15 Sep '23, 04:03

question was seen: 492 times

last updated: 29 Nov '23, 04:27