动态SQL 参数为字符变量报ORA-06512，请问如何解决

Question

V_CATEGORY_NODE_CONFIG := REPLACE(REPLACE(REPLACE(IN_KPI_CONFIG(I)
                                                          .CATEGORY_NODE_CONFIG,
                                                          '][',
                                                          ','),
                                                  '[',
                                                  ''),
                                          ']',
                                          '');
这段主要是想把字段里[-1030038][-1030][-0038]变成-1030038,-1030,-0038然后下面用字符替换的形式传入动态SQL参数
      
        V_SQL := 'SELECT *
                      FROM (SELECT *
                              FROM ODS.ODS_PRODUCT_PRICE_PLAN
                             WHERE EXISTS (SELECT 1
                                      FROM ODS_PP_2_CATEGORY_NODE
                                     WHERE CATEGORY_NODE_ID IN
                                           (:a))
                               AND PROD_ID = :b
                               AND ODS_CITY = :c
                            UNION ALL
                            SELECT *
                              FROM ODS.ODS_PRODUCT_PRICE_PLAN_HIS
                             WHERE EXISTS (SELECT 1
                                      FROM ODS_PP_2_CATEGORY_NODE
                                     WHERE CATEGORY_NODE_ID IN
                                           (:a))
                               AND PROD_ID = :b
                               AND ODS_CITY = :c)
                     ORDER BY ODS_MODIFY_DT DESC';
      
这个V_SQL报错，当：a为-1030038,-1030,-0038时就抱ORA-06512错，为一个值时不报错，请问大虾应该怎么修改。

Answer 1

给个参考：http://hi.baidu.com/forhh/blog/item/a6a48326357e34128a82a1f2.html

Answer 2

ORA-06502 PL/SQL: numeric or value error stringCause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
--类型的转换有问题!!!

Answer 3

发现了，这个是我v_sql定义长度太小了，谢谢楼上，现在又报新的错误这句是v_sql下一句，
        OPEN REC FOR V_SQL
          USING V_CATEGORY_NODE_CONFIG, IN_PROD_ID, I_ODS_CITY;
在这个语句中报ORA-01008，并非所有变量已经关联是什么意思？

Answer 4

        V_SQL := 'SELECT * 
                      FROM (SELECT * 
                              FROM ODS.ODS_PRODUCT_PRICE_PLAN 
                            WHERE EXISTS (SELECT 1 
                                      FROM ODS_PP_2_CATEGORY_NODE 
                                    WHERE CATEGORY_NODE_ID IN 
                                          (:a)) 
                              AND PROD_ID = :b 
                              AND ODS_CITY = :c 
                            UNION ALL 
                            SELECT * 
                              FROM ODS.ODS_PRODUCT_PRICE_PLAN_HIS 
                            WHERE EXISTS (SELECT 1 
                                      FROM ODS_PP_2_CATEGORY_NODE 
                                    WHERE CATEGORY_NODE_ID IN 
                                          (:a)) 
                              AND PROD_ID = :b 
                              AND ODS_CITY = :c) 
                    ORDER BY ODS_MODIFY_DT DESC'; 请问下这里的:a :b :c，在动态SQL用using语句时是3个变量还是6个？