如何解决ORA-01722无效数字错误？执行存储过程报错求助

Let's break down why you're hitting this error and fix it right away.

The Root Cause #

ORA-01722 pops up when Oracle tries to convert a string to a number and can't—which is exactly what's happening here. Let's compare your INSERT column order to the values you're passing:

Your target columns are ordered as:
"NAME", "CREATEDATE", "TAG","STATUS", "MONEY","PHOTOID","OWNID","EXPLAIN"

But in your VALUES clause, you have:
p_name,CURRENT_DATE,p_tag,'T',p_money,p_mail,(SELECT ID FROM USERS WHERE MAIL=p_mail),p_explain

See the mismatch? The 6th position should map to PHOTOID (a numeric column, since it's defined as QUESTION.PHOTOID%TYPE), but you're passing p_mail—a string value like 'cerate' from your test call. That string-to-number conversion fails, triggering the ORA-01722 error. You meant to use p_img here instead.

Corrected Stored Procedure Code #

Here's the fixed version with values aligned correctly to their columns:

create or replace PROCEDURE INSERTQUESTION(
    p_name IN QUESTION.NAME%TYPE,
    p_tag IN QUESTION.TAG%TYPE,
    p_money IN QUESTION.MONEY%TYPE,
    p_explain IN QUESTION.EXPLAIN%TYPE,
    p_img IN QUESTION.PHOTOID%TYPE,
    p_mail IN USERS.MAIL%TYPE
) IS
BEGIN
    INSERT INTO QUESTION ("NAME", "CREATEDATE", "TAG","STATUS", "MONEY","PHOTOID","OWNID","EXPLAIN")
    VALUES (
        p_name,
        CURRENT_DATE,
        p_tag,
        'T',
        p_money,
        p_img, -- Replaced p_mail with the correct parameter for PHOTOID
        (SELECT ID FROM USERS WHERE MAIL=p_mail),
        p_explain
    );
    COMMIT;
END;

Bonus Tip to Prevent This in the Future #

To avoid column-order mixups like this, use the explicit column-value assignment syntax for INSERT. It's a bit more typing, but it's far safer—especially if your table schema changes later:

INSERT INTO QUESTION (
    "NAME",
    "CREATEDATE",
    "TAG",
    "STATUS",
    "MONEY",
    "PHOTOID",
    "OWNID",
    "EXPLAIN"
) VALUES (
    NAME => p_name,
    CREATEDATE => CURRENT_DATE,
    TAG => p_tag,
    STATUS => 'T',
    MONEY => p_money,
    PHOTOID => p_img,
    OWNID => (SELECT ID FROM USERS WHERE MAIL=p_mail),
    "EXPLAIN" => p_explain
);

This way, even if you reorder columns in the INSERT clause, your values will always map to the right columns.

内容的提问来源于stack exchange，提问作者user9796004