How do I use the PRESERVE_SOURCE_FORMAT option?

[Breck_Carter]

(copied from another forum)

[Breck_Carter]

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()\
begin\
  set x = 1;\
  set y =
2\
end',(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()\
begin\
  set x = 1;\
  set y =
2\
end','create PROCEDURE p()\
BEGIN\
   SET   x   =   1;\

SET       y       =      2;\
END'
*/