[20250527]奇怪的sql macros.txt

--//oracle 不知道从那个版本支持sql macros操作，想实现一个算式计算结果的功能，结果遇到问题。
--//做一个记录。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.建立宏：
CREATE OR REPLACE FUNCTION js_op (n1 NUMBER, op varchar2, n2 NUMBER)
   RETURN VARCHAR2 sql_macro(scalar)
as
begin
   return 'n1 '|| op ||' n2';
end;
/

SCOTT@book01p> select js_op(1,'+',2) from dual ;
select js_op(1,'+',2) from dual
*
ERROR at line 1:
ORA-64626: invalid SQL text returned from SQL macro:
ORA-00907: missing right parenthesis

--//而这样写可以：
CREATE OR REPLACE FUNCTION js_op1 (n1 NUMBER,  n2 NUMBER)
   RETURN VARCHAR2 sql_macro(scalar)
as
begin
      return 'n1 + n2';
end;
/

SCOTT@book01p> select js_op1(1,2) from dual ;
JS_OP1(1,2)
-----------
          3

--//改写如下，这样应该可以了吧。
CREATE OR REPLACE FUNCTION js_op (n1 NUMBER, op varchar2, n2 NUMBER)
   RETURN VARCHAR2 sql_macro(scalar)
as
vc varchar2(32);
begin
   select decode ( op ,'+','n1+n2','-','n1-n2','*','n1*n2','/','n1/n2') into vc from dual ;
   return vc;
end;
/

SCOTT@book01p> select js_op(1,'+',2) from dual ;
select js_op(1,'+',2) from dual
                   *
ERROR at line 1:
ORA-62565: The SQL Macro method failed with error(s).
ORA-01405: fetched column value is NULL


--//似乎不允许出现多种情况，改写如下：

CREATE OR REPLACE FUNCTION js_op (n1 NUMBER, op varchar2, n2 NUMBER)
   RETURN VARCHAR2 sql_macro(scalar)
as
vc varchar2(32);
begin
   --select decode ( op ,'+','n1+n2','-','n1-n2','*','n1*n2','/','n1/n2') into vc from dual ;
   --select decode ( op ,'+','n1+n2') into vc from dual ;
   --vc := '(n1 '|| op ||'n2)';
   vc := 'n1 +  n2';
   return vc;
end;
/