oracle - 获取 PL/SQL : numeric or value error: character to number conversion error

Question

我收到此错误:

ORA-06502:PL/SQL:数字或值错误:字符到数字的转换错误
ORA-06512:在第 146 行
06502. 00000 - “PL/SQL:数字或值错误 %s”

这是我的代码:

   SET SERVEROUTPUT ON;

DECLARE
    SearchId NUMBER := 0;
    SearchMethod VARCHAR2(100) := '';
    CritExpression VARCHAR2(100) := '';
    SubstringStartPosition NUMBER := 0;
    SubstringLength NUMBER := 0;
    CritDescription VARCHAR2(100) := '';
    CriteriaSequenceId NUMBER := 1;
    CriteriaId NUMBER := 0;
    CritCount NUMBER := 0;

FUNCTION InsertSrchCriteria
(
    SearchMethod IN VARCHAR2,
    CritExpression IN VARCHAR2,
    SubstringStartPosition IN NUMBER,
    SubstringLength IN NUMBER,
    CritDescription IN VARCHAR2,
    CriteriaSequenceId IN NUMBER,
    SearchId IN NUMBER
)
    RETURN NUMBER
IS
    C_Id NUMBER := 0;
BEGIN
    SELECT COUNT (*) INTO CritCount FROM criteria_table WHERE search_id = SearchId AND criteria_sequence_id = CriteriaSequenceId;

    IF CritCount = 0
    THEN
        INSERT INTO criteria_table
            (
                criteria_sequence_id,
                search_id,
                search_method,
                expression,
                substring_start_position,
                substring_length,
                description
            )
            VALUES
            (
                CriteriaSequenceId,
                SearchId,
                SearchMethod,
                CritExpression,
                SubstringStartPosition,
                SubstringLength,
                CritDescription
            )
            RETURNING criteria_id INTO C_Id;
        IF C_Id > 0
        THEN
            DBMS_OUTPUT.PUT_LINE ('Inserted ' || 'SearchId: ' || SearchId || ' @' || CriteriaSequenceId || ' successfully');
        ELSE
            DBMS_OUTPUT.PUT_LINE ('Not Inserted ' || 'SearchId: ' || SearchId || ' @' || CriteriaSequenceId);
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE ('Already exists ' || 'SearchId: ' || SearchId || ' @' || CriteriaSequenceId);
    END IF;
    RETURN C_Id;
END InsertSrchCriteria;

BEGIN
        SearchId = 5;
        CriteriaSequenceId := 1;
        SearchMethod := 'XPath';
        CritExpression := '//Expression/text()';
        SubstringStartPosition := null;
        SubstringLength := null;
        CritDescription := '';

        CriteriaId := InsertSrchCriteria  ****
        (
            CriteriaSequenceId,
            SearchId,
            SearchMethod,
            CritExpression,
            SubstringStartPosition,
            SubstringLength,
            CritDescription
        );
END;

错误发生在带有 ** 的行上。我不太确定是什么导致了这个错误，有什么帮助吗？

Answer 1

鉴于您的功能定义:

FUNCTION InsertSrchCriteria
(
    SearchMethod IN VARCHAR2,
    CritExpression IN VARCHAR2,
    SubstringStartPosition IN NUMBER,
    SubstringLength IN NUMBER,
    CritDescription IN VARCHAR2,
    CriteriaSequenceId IN NUMBER,
    SearchId IN NUMBER
)

然后你如何调用 InsertSrchCriteria:

    CriteriaId := InsertSrchCriteria  ****
    (
        CriteriaSequenceId,
        SearchId,
        SearchMethod,
        CritExpression,
        SubstringStartPosition,
        SubstringLength,
        CritDescription
    );

您的第一个参数“CriteriaSequenceId”被声明为:

CriteriaSequenceId NUMBER := 1;

所以你的函数需要一个 VARCHAR2 作为第一个参数，但你给它发送了一个数字。从它的外观来看，您的参数只是顺序错误。