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

asked 01 Mar '16, 14:34

louisjoelhc's gravatar image

louisjoelhc
111348
accept rate: 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%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

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

×106

question asked: 01 Mar '16, 14:34

question was seen: 281 times

last updated: 03 Mar '16, 03:55