(copied from another forum)

asked 05 Jan '10, 08:01

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%

edited 05 Jan '10, 08:08


The default is 'ON' and IMO it should be left alone.

To set it, use one of these statements:

SET OPTION PUBLIC.PRESERVE_SOURCE_FORMAT = 'ON';

or

SET OPTION PUBLIC.PRESERVE_SOURCE_FORMAT = 'OFF';

Here is a dbisql demonstration of its full effect, which is to turn preservation of the SYSPROCEDURE.source column on and off (null). Note that SYSPROCEDURE.proc_defn is always maintained, and it is the one that is executed when you call the procedure; semantically it is identical.

SET OPTION PUBLIC.PRESERVE_SOURCE_FORMAT = 'OFF';

BEGIN
   DROP PROCEDURE p;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE PROCEDURE p()
BEGIN
   SET   x   =   1;
   SET       y       =      2;
END;

SELECT proc_defn,
       source
  FROM SYSPROCEDURE
 WHERE proc_name = 'p';

/*
proc_defn,source
'create procedure DBA.p()\x0abegin\x0a  set x = 1;\x0a  set y =
2\x0aend',(NULL)
*/

SET OPTION PUBLIC.PRESERVE_SOURCE_FORMAT = 'ON';

BEGIN
   DROP PROCEDURE p;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE PROCEDURE p()
BEGIN
   SET   x   =   1;
   SET       y       =      2;
END;

SELECT proc_defn,
       source
  FROM SYSPROCEDURE
 WHERE proc_name = 'p';

/*
proc_defn,source
'create procedure DBA.p()\x0abegin\x0a  set x = 1;\x0a  set y =
2\x0aend','create PROCEDURE p()\x0aBEGIN\x0a   SET   x   =   1;\x0a
SET       y       =      2;\x0aEND'
*/
permanent link

answered 05 Jan '10, 08:03

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%

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:

×14

question asked: 05 Jan '10, 08:01

question was seen: 760 times

last updated: 05 Jan '10, 08:08