Getting PL/SQL: numeric or value error: character to number conversion error

Question

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"

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?

Aaron · Accepted Answer · 2011-12-16 18:42:59Z

9

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:

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

Your first parameter "CriteriaSequenceId" is DECLAREd as:

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.

answered Dec 16, 2011 at 18:42

Aaron

58k11 gold badges123 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Khushbu Lil · Accepted Answer · 2021-07-29 11:29:39Z

1

Make sure for maintain SP's parameters sequence and function that you call for SP, both parameters sequence must be same.

for example : if your SP's first param is @p_ID than your function call first param is must be p_ID

answered Jul 29, 2021 at 11:29

Khushbu Lil

111 bronze badge

Comments

Strauteka · Accepted Answer · 2014-06-05 06:33:05Z

0

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.. ;) –

answered Jun 5, 2014 at 6:33

Strauteka

1981 silver badge10 bronze badges

Collectives™ on Stack Overflow

Getting PL/SQL: numeric or value error: character to number conversion error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related