SAP SQL Anywhere

Question

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

Answer 1

0

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

permanent link

answered 01 Mar '16, 14:53

Mark Culp
24.9k●10●141●297
accept rate: 41%

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?

(01 Mar '16, 15:01) louisjoelhc

Replies hidden

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

(02 Mar '16, 03:01) Volker Barth

1

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;

(02 Mar '16, 08:58) Chris Keating

1

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

(02 Mar '16, 10:37) louisjoelhc

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

Thanks.

(02 Mar '16, 10:46) louisjoelhc

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.

(02 Mar '16, 12:34) Chris Keating

Replies hidden

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

(03 Mar '16, 03:54) Volker Barth

showing 4 of 7 show all flat view

SAP SQL Anywhere

sysprocedure.source is null when using temporary procedure

Follow this question

Related questions