I have problem with the following code:

create procedure MyDummy()

begin

select 'mydummy' from dummy;

end;

create procedure MyDummyWrapper()

begin

select * from MyDummy();

end;


call MyDummy(); -->mydummy (As Expected)

call MyDummyWrapper();-->mydummy (As Expected)


alter procedure MyDummy()

begin

select 'mydummy' from dummy where 1 = 0;

end;


call MyDummy()-->empty (As Expected)

call MyDummyWrapper()-->mydummy (NOT As Expected!!!)

select * from MyDummyWrapper()-->empty (As Expected)

I have here 2 Questions:

  1. what is the difference between select and call? Why I have the problem only with call?
  2. how can I overcome the problem with call? is there any alternative for sp_recompile (as with Adaptive Server)?

asked 15 Sep, 05:18

Baron's gravatar image

Baron
1.2k4770110
accept rate: 28%

edited 15 Sep, 07:47


FWIW, it would be helpful to state more than just OK/NOK, I had expected the "NOK" would mean an error message and not a wrong result set. In my tests with 16.0.0.2798 and 17.0.10.6175 "call MyDummyWrapper()" returns the result "mydummy" whereas the other procedure invoocations return an empty result set as expected.


In my humble opinion this looks like a bug.

The behaviour can be fixed via

alter procedure MyDummyWrapper recompile;

Note, that in my tests both procedures (lacking a result set clause) are described via sysprocparm as having one result set column with type CHAR(7), which is appropriate.

select SP.proc_name, SPP.* from SYSPROCEDURE SP key join SYSPROCPARM SPP
where proc_name in ('MyDummy', 'MyDummyWrapper')

BTW, there's more to this:

If you drop the first procedure via "drop procedure MyDummy" and do not afterwards recomplie the second procedure, the second procedure still can be called successfully without an error, which is totally unexpected in my book.

So I do have to assume the wrapper procedure somehow interally caches the result set, also the attempt to disable plan caching via "set temporary option max_plans_cached = 0;" does not make a difference in my tests...

permanent link

answered 15 Sep, 07:41

Volker%20Barth's gravatar image

Volker Barth
36.7k343505761
accept rate: 34%

edited 15 Sep, 08:11

Yes, your fix works, I have tried it before! Moreover It also works if I restart the database (even without the mentioned fix).

The problem with the mentioned fix is, how can I know (not in this specific case, but generally) how many wrappers has the procedure MyDummy, and how many other procedures are affected of the last alter procedure MyDummy()

(15 Sep, 07:50) Baron
Replies hidden

As to "procedure dependencies": I do not know how to recognize those automatically, in contrast to the "View dependency feature".

(15 Sep, 08:10) Volker Barth

Yes, you have right, even after dropping the first procedure, then the wrapper is still functioning correct!

I don't think that this is related to caching the last result set, because after calling sa_flush_cache everything is still working as if nothing is happened!

(16 Sep, 03:15) Baron

Well, there are several types of caching, and I was relating to plan caching which has nothing to do with the database server's page caching that would be influenced by sa_flush_cache. But disabling plan caching did not matter here, either, as stated.

Furthermore, the noticed effect might be due to "inlining simple procedures", but again, that sould only happen for using procedures in the FROM clause, not for call statements, and this does not explain the effect, either.

(16 Sep, 03:56) 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:

×58
×19
×2

question asked: 15 Sep, 05:18

question was seen: 93 times

last updated: 16 Sep, 03:56