似乎无法在PLSQL函数中减去两个数字 [英] Can't seem to subract two numbers in a PLSQL function

问题描述

以下函数旨在将定界的CLOB划分为字符串数组:

The following function is intended to divide up a delimited CLOB into a string array:

FUNCTION SPLIT_CLOB(sText IN clob, sDel IN VARCHAR2 := ',') RETURN CLOB_ARRAY IS
         nStartIdx PLS_INTEGER := 1;
         nEndIdx PLS_INTEGER := 1;
         oRet CLOB_ARRAY := CLOB_ARRAY();
     BEGIN
         IF sText IS NULL THEN RETURN oRet; END IF;
         IF DBMS_LOB.getlength(sText) = 0 THEN RETURN oRet; END IF;

         LOOP

            nEndIdx := DBMS_LOB.INSTR(sText, sDel, nStartIdx);

            IF nEndIdx > 0 THEN
               oRet.Extend;
               /* compiler error on this statement: */
               oRet(oRet.LAST) := DBMS_LOB.SUBSTR(sText, (nEndIdx – nStartIdx), nStartIdx);
               nStartIdx := nEndIdx + LENGTH(sDel);
            ELSE
               oRet.Extend();
               oRet(oRet.LAST) := DBMS_LOB.SUBSTR(lob_loc => sText, offset => nStartIdx);
               EXIT;
            END IF;
         END LOOP;

         RETURN oRet;

     END SPLIT_CLOB;

该行:

oRet(oRet.LAST) := DBMS_LOB.SUBSTR(sText, (nEndIdx – nStartIdx), nStartIdx);

引发PLS-00103编译器错误.但是，如果我将呼叫更改为:

throws PLS-00103 compiler errors. But if I change the call to:

oRet(oRet.LAST) := DBMS_LOB.SUBSTR(sText, 5, nStartIdx);

一切都很好.我尝试过创建另一个变量来提前进行减法，但是遇到了同样的PLS-00103错误.

everything is fine. I've tried creating another variable to do the subtraction ahead of time, but ran into the same PLS-00103 error.

我失去联系了吗?我忘了如何将两个数字相减吗?

Have I lose my touch? Did I forget how to subtract two numbers or something?

请帮助.谢谢.

编辑

好吧，最奇怪的事情刚刚发生了……在此软件包的其余部分中，我知道我在其他函数中的其他地方减去了一些PLS_INTEGER....所以我找到了这样一个示例，然后是COPY&粘贴在我的其他函数中找到的减号，然后事情就会编译...

Okay, the WEIRDEST thing just happened... in the rest of this package, I know I was subtracting some PLS_INTEGERs somewhere else in a different function.... so I found such an example, then COPY & PASTE the minus sign found in my other function, and the thing compiles...

感谢您的帮助...

推荐答案

将模式作为参数的操作(例如COMPARE，INSTR和SUBSTR)不支持正则表达式或特殊的匹配字符(例如LIKE运算符中的％ SQL)在模式参数或子字符串中." http://download.oracle.com/docs/cd /B19306_01/appdev.102/b14258/d_lob.htm

"Operations involving patterns as parameters, such as COMPARE, INSTR, and SUBSTR do not support regular expressions or special matching characters (such as % in the LIKE operator in SQL) in the pattern parameter or substrings." http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm

我认为您应该在SUBSTR函数之外计算"nEndIdx – nStartIdx"

I think you should calculate "nEndIdx – nStartIdx" outside the SUBSTR Function

Substr Ref. http://download.oracle.com/docs /cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349

Substr Ref. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349

这篇关于似乎无法在PLSQL函数中减去两个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！