sysprocedure.source is null when using temporary procedure

[Former Member]

Hi,

We want to use temporary procedures in database procedures but when we do, the source column of the procedure's entry in the sysprocedure table is set to null. That makes it hard to edit the procedure using Sybase Central or other tools.

We are currently using SQL Anywhere version 16.0.0.1948.

Here is an example of a simple procedure that cause the issue :

create or replace procedure test_temp(in arg integer)
result (res_test char(1))
begin  
  drop procedure if exists temp_proc;
  create temporary procedure temp_proc()
  result (test char(1))
  begin
    select 'O'
  end;

  select * from temp_proc();
end

Is this a known issue or is there something we are missing to make this work as expected?

Thanks

[MarkCulp]

Short answer: Yes this is known behaviour and is the expected behaviour.

A temporary procedure only exists on the connection that created it and never gets added to the catalog, and as such there is no change made to the database. I.e. there is no permanent procedure source that needs to be added to the database catalog because the procedure is not permanent.

HTH

[Former Member]

Thanks for the fast answer. But I think I did not explain the issue clearly.

The problem we have is with the permanent procedure that contains the temporary procedure. In the previous example, the source column of the sysprocedure table is null for the "test_temp" permanent procedure entry.

That makes it impossible for us to use temporary procedures inside permanent procedure.

Is this normal?

[VolkerBarth]

What's the value of the "preserve_source_format" option? - That should decide whether the sysprocedure.source column is filled or not.

[chris_keating]

I have been able to reproduce this. Engineering case # 796353 has been opened.

You can work around this using an execute immediate. For example

create procedure foo() begin execute immediate( ' create procedure foobar() begin end;' ); end;

[Former Member]

The preserve_source_format option is "On" and the sysprocedure.source column is filled for every procedures except when we use temporary procedures.

[Former Member]

Using execute immediate works as a work around. How can we know when the opened engineering case is fixed?

Thanks.

[chris_keating]

It will be documented in the release notes for an SP. Release notes are available via the Info link for the SP on SAP Service Marketplace.

[VolkerBarth]

FWIW: In former Sybase times, I used the "Sybase Change Request" site to check the status of known CRs. Now, as that site has been made unavailable (which has been discussed here somewhat controversially), you can also check the newest SQL Anywhere EBF/SP Readme files here which does not require a SUSER ID.

Note, that "Readme site" does not tell whether the according EBFs/SPs have been made available for your particular platform yet, for that you still need to check within the Marketplace.

@Chris: If there are better ways to check the CR status, please let me know...