oracle 获取 PL/SQL：数字或值错误：字符到数字转换错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8538516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting PL/SQL: numeric or value error: character to number conversion error
提问by user906153
I'm getting this error:
我收到此错误：
ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 146 06502. 00000 - "PL/SQL: numeric or value error%s"
ORA-06502：PL/SQL：数字或值错误：字符到数字转换错误 ORA-06512：在第 146 行 06502。00000 -“PL/SQL：数字或值错误 %s”
And here's my code:
这是我的代码：
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;
The error is occuring on the line with **. I'm not quite too sure as to what is causing this error, any help?
错误发生在带有**的行上。我不太确定是什么导致了这个错误，有什么帮助吗？
回答by Aaron
Given your FUNCTION definition:
鉴于您的功能定义：
FUNCTION InsertSrchCriteria
(
SearchMethod IN VARCHAR2,
CritExpression IN VARCHAR2,
SubstringStartPosition IN NUMBER,
SubstringLength IN NUMBER,
CritDescription IN VARCHAR2,
CriteriaSequenceId IN NUMBER,
SearchId IN NUMBER
)
And then how you're calling InsertSrchCriteria:
然后你如何调用 InsertSrchCriteria：
CriteriaId := InsertSrchCriteria ****
(
CriteriaSequenceId,
SearchId,
SearchMethod,
CritExpression,
SubstringStartPosition,
SubstringLength,
CritDescription
);
Your first parameter "CriteriaSequenceId" is DECLAREd as:
您的第一个参数“CriteriaSequenceId”被声明为：
CriteriaSequenceId NUMBER := 1;
So your function is expecting a VARCHAR2 as the first parameter, but you're sending it a number. By the looks of it, your parameters are just in the wrong order.
所以你的函数需要一个 VARCHAR2 作为第一个参数，但你发送的是一个数字。从它的外观来看，您的参数只是顺序错误。
回答by Strauteka
please use this syntax,
请使用此语法，
CriteriaId := InsertSrchCriteria
(
SearchMethod =>test1,
CritExpression =>test2,
SubstringStartPosition =>null,
SubstringLength =>test3,
CritDescription =>...,
CriteriaSequenceId =>...,
SearchId =>..
);
You will know for sure where each variable go and its easier to read code later.. ;) –
您将确切地知道每个变量的位置，并且以后更容易阅读代码.. ;) –
