ORA-01722:无效数字(存储过程中出这个错误,比较蹊跷)

冷月宫主

CREATE OR REPLACE PROCEDURE "P_INSERTT_USER"(IUSERNAME    VARCHAR2,
                                             IUSERDEF     VARCHAR2,
                                             IPASSWORD    VARCHAR2,
                                             IUSERTYPE    NUMBER,
                                             ICORPID      NUMBER,
                                             IADMINERID   NUMBER,
                                             ICUSTOMERID  NUMBER,
                                             ITEAMID      VARCHAR2, --车队ID，以逗号分隔                                             ILINKTEL     VARCHAR2,
                                             IEMAIL       VARCHAR2,
                                             ILINKADDERSS VARCHAR2,
                                             IHYCAR       NUMBER,
                                             IPARENTID    NUMBER,
                                             OPRO_CODE    OUT NUMBER, --错误吗
                                             OPRO_DESC    OUT VARCHAR2 --错误描述
                                             ) AS
BEGIN
  DECLARE
    IUSERID  NUMBER;
    IUNITIDS CLOB; --以逗号分隔的车台ID字符串
  BEGIN
    OPRO_CODE := -1;
    OPRO_DESC := '';
    IF (IUSERNAME IS NULL OR IUSERDEF IS NULL OR IPASSWORD IS NULL OR
       IUSERTYPE IS NULL) THEN
      OPRO_CODE := -1;
      OPRO_DESC := '传输数据不合法!';
    ELSE
      SELECT SNT_USER.NEXTVAL INTO IUSERID FROM DUAL;
      INSERT INTO T_USER
        (USERID,
         USERNAME,
         USERDEF,
         PASSWORD,
         USERTYPE,
         CORPID,
         ADMINERID,
         STAMP,
         LINKTEL,
         EMAIL,
         LINKADDERSS,
         HYCAR,
         PARENTID)
      VALUES
        (IUSERID,
         IUSERNAME,
         IUSERDEF,
         IPASSWORD,
         IUSERTYPE,
         ICORPID,
         IADMINERID,
         SYSDATE,
         ILINKTEL,
         IEMAIL,
         ILINKADDERSS,
         IHYCAR,
         IPARENTID);
   
      IF ((ITEAMID IS NOT NULL) AND (ITEAMID <> '-1')) THEN
        IUNITIDS := '';
        FOR V IN (SELECT DISTINCT T.UNITID, T.TEAMID
                    FROM T_USER_CAR T
                   WHERE T.TEAMID IN (ITEAMID)) LOOP
          IUNITIDS := IUNITIDS || V.UNITID || ',';
        END LOOP;
        IF (IUNITIDS <> '') THEN
          IUNITIDS := SUBSTR(IUNITIDS, 1, LENGTH(IUNITIDS) - 1);
        END IF;
      
        P_INSERTT_CUSTOMER_UNIT_HERIT(IUSERID, IUNITIDS, 1, IPARENTID);
      
      END IF;
   
      COMMIT;
      OPRO_CODE := IUSERID;
      OPRO_DESC := '添加用户成功!';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      BEGIN
        ROLLBACK;
        OPRO_CODE := -1;
        OPRO_DESC := SQLERRM || ',' || OPRO_DESC;
      END;
  END;
END;


说明：T_USER_CAR 表中的TEAMID是NUMBER类型。

我直接在PL/SQL中执行
SELECT DISTINCT T.UNITID, T.TEAMID
                    FROM T_USER_CAR T
                   WHERE T.TEAMID IN (1300,1850)
是事以成功执行的，但我通过存储过程传过来就不行了，谁能告诉我为什么，怎么处理呀？谢谢



[ 本帖最后由 冷月宫主 于 2011-5-29 17:16 编辑 ]

dingjun123

你的sql里是静态字段，两个值，在pl/sql里是字符串，一个值啊，字符串遇到数字，转为数字，但是转不了，所以报错
T.TEAMID IN (ITEAMID)
类似于t.teadmid in ('1,2')了，当然和sql不一样

这个问题比较典型，很多方法，一般用自定义函数+table函数搞定
http://www.itpub.net/viewthread.php?tid=1228883&extra=&highlight=sql%D6%D0%D2%BB%B8%F6%D7%D6%B7%FB%B4%AE%B5%C4in-list%C5%D0%B6%CF%CE%CA%CC%E2&page=1

xgghxkhuang

CREATE TABLE TEST_CHAR2
(
A VARCHAR2(100)
);
INSERT INTO TEST_CHAR2
VALUES ('1300,1850');
COMMIT;
SELECT * FROM TEST_CHAR2
WHERE 1300 IN A ;
--报错，转换错误
SELECT to_number(CASE WHEN LEVEL=1 THEN SUBSTR(a,1,Instr(A, ',', 1, 1)-1)
       ELSE  SUBSTR(a,Instr(A, ',', 1, LEVEL-1)+1,Instr(A, ',', 1, LEVEL)-Instr(A, ',', 1, LEVEL-1)-1) END) FROM
(SELECT a||',' a FROM TEST_CHAR2) TEST_CHAR2
CONNECT BY LEVEL<= LENGTH(A)-LENGTH(Replace(A, ',', ''));
--现写了一个，用在逗号小于200的情况
with va as
( select 1300 id from dual
union all select 1850 from dual
)
select id from va where id in
(SELECT to_number(CASE WHEN LEVEL=1 THEN SUBSTR(a,1,Instr(A, ',', 1, 1)-1)
       ELSE  SUBSTR(a,Instr(A, ',', 1, LEVEL-1)+1,Instr(A, ',', 1, LEVEL)-Instr(A, ',', 1, LEVEL-1)-1) END) FROM
(SELECT a||',' a FROM TEST_CHAR2) TEST_CHAR2
CONNECT BY LEVEL<= LENGTH(A)-LENGTH(Replace(A, ',', '')))
--这样就不报错了

冷月宫主

非常感谢，我看看，看样子是我根本没有弄明白，我先看看，好象有人处理过这种问题！

http://macrochen.iteye.com/blog/260249
http://**/viewthread.php?tid=1533
http://blog.csdn.net/wh62592855/archive/2011/01/05/6118673.aspx

dingjun123

1.首先搞清楚你的pl/sql的语句和你的sql语句根本就是不同的。pl/sql里是一个字符串，而不是value list
2.想办法把字符串切分为value list:方法很多，正则，自定义函数，sys_context,instr+substr配合一些hint

冷月宫主

问题已解决，感谢楼上朋友！

http://blog.csdn.net/E_wsq/archive/2011/05/29/6453359.aspx