我直接写insert into aa select * from xx语句执行就问题，但是我用游标执行就报错，请大家帮忙看看，谢谢！
错误提示：exception*M182220080300882ORA-01858: a non-numeric character was found where a numeric was expected语句：
Procedure ConvertSaveamt
IS
CURSOR cur_abcsaveamt IS
select
a.c_rcpt_no ,--ERP_PAYMENT_ID ,
'Available',--RECORD_STATUS ,
sysdate ,--T_RECORD_STATUS ,
'202' ,--PAYMENT_TYPE_ID ,
'205' ,--C_RP_TYPE ,
'3' ,--PAYMENT_METHOD_TYPE_ID ,
b.c_customer_accounts ,--PAYMENT_ACCOUNTS ,付款方银行账号？
b.c_bank_accounts,--DEPOSIT_BANK_NAME ,
b.c_accounts_name ,--DEPOSIT_ACCOUNTS_NAME ,
b.c_customer_accounts ,--DEPOSIT_ACCOUNTS ,
b.C_Provinces ,--DEPOSIT_PROVINCE ,
b.c_city ,--DEPOSIT_CITY ,
b.C_Mobile_Phone ,--DEPOSIT_MOBILE ,
b.C_mail ,--DEPOSIT_EMAIL ,
'0' ,--CITY_FLAG ,
'N' ,--PRIORITY_FLAG ,
'3' ,--OPERATION_TYPE ,
b.C_Receive_no ,--BANK_NUMBER ,
a.n_pay_amt ,--AMOUNT ,
null ,--BUDGET_PLAN_ID ,
null ,--BUDGET_ITEM_ID ,
'付' ,--PURPOSE ,
c.c_company_cde ,--C_SEGMENT1 ,
c.c_department_cde ,--C_SEGMENT2 ,
null,--C_SEGMENT3 ,、
null ,--C_SEGMENT4 ,--明细科目段付款方银行编码？
a.c_prod_no ,--C_SEGMENT5 ,
null ,--C_SEGMENT6 ,
null ,--C_SEGMENT7 ,
null ,--C_SEGMENT8 ,
null ,--C_table_flag ,
a.T_crt_tm ,--T_crt_tm ,
null ,--C_edr_typ ,
null ,--C_FEETYP_CDE ,
a.C_CLM_TYP ,--C_CLM_TYP ,
null ,--C_doc_flag ,
'212' ,--C_FLOW_NO 以现金支付的,
a.c_clm_no ,--c_clm_no ,
a.c_ply_no ,--c_ply_no ,
a.C_dpt_CDE --C_dpt_CDE
from t_fin_clmdue a,t_fin_rpclmcustomer b,t_department c
WHERE a.c_rcpt_no=b.c_rcpt_no
and a.c_dpt_cde=c.c_dpt_cde
AND not exists (SELECT 1 FROM t_fin_iabc WHERE ERP_PAYMENT_ID =a.c_rcpt_no)
and b.c_bala_mrk='4' --通过审核的信息
and a.c_rcpt_no='M182220080300882';
v_erp_payment_id t_fin_iabc.erp_payment_id%type;
v_record_status t_fin_iabc.record_status%type;
v_trecord_status t_fin_iabc.t_record_status%type;
v_payment_type_id t_fin_iabc.payment_type_id%type;
v_rp_type t_fin_iabc.c_rp_type%type;
v_payment_method_type_id t_fin_iabc.payment_method_type_id%type;
v_payment_accounts t_fin_iabc.payment_accounts%type;
v_deposit_bank_name t_fin_iabc.deposit_bank_name%type;
v_deposit_accounts_name t_fin_iabc.deposit_accounts_name%type;
v_deposit_accounts t_fin_iabc.deposit_accounts%type;
v_deposit_province t_fin_iabc.deposit_province%type;
v_deposit_city t_fin_iabc.deposit_city%type;
v_deposit_mobile t_fin_iabc.deposit_mobile%type;
v_deposit_email t_fin_iabc.deposit_email%type;
v_city_flag t_fin_iabc.city_flag%type;
v_priority_flag t_fin_iabc.priority_flag%type;
v_operation_type t_fin_iabc.operation_type%type;
v_bank_number t_fin_iabc.bank_number%type;
v_amount t_fin_iabc.amount%type;
v_budget_plan_id t_fin_iabc.budget_plan_id%type;
v_budget_item_id t_fin_iabc.budget_item_id%type;
v_purpose t_fin_iabc.purpose%type;
v_segment1 t_fin_iabc.c_segment1%type;
v_segment2 t_fin_iabc.c_segment2%type;
v_segment3 t_fin_iabc.c_segment3%type;
v_segment4 t_fin_iabc.c_segment4%type;
v_segment5 t_fin_iabc.c_segment5%type;
v_segment6 t_fin_iabc.c_segment6%type;
v_segment7 t_fin_iabc.c_segment7%type;
v_segment8 t_fin_iabc.c_segment8%type;
v_table_flag t_fin_iabc.c_table_flag%type;
v_crt_tm t_fin_iabc.t_crt_tm%type;
v_edr_typ t_fin_iabc.c_edr_typ%type;
v_feetyp_cde t_fin_iabc.c_feetyp_cde%type;
v_clm_typ t_fin_iabc.c_clm_typ%type;
v_doc_flag t_fin_iabc.c_doc_flag%type;
v_flow_no t_fin_iabc.c_flow_no%type;
v_clm_no t_fin_iabc.c_clm_no%type;
v_ply_no t_fin_iabc.c_ply_no%type;
v_dpt_cde t_fin_iabc.c_dpt_cde%type;
v_rcpt_no t_fin_plyedr_coldue.c_rcpt_no%type;
v_err_content t_fin_errorlog.C_ERR_CONTENT%type; BEGIN/*
--1.能够成功的插入语句
INSERT INTO t_fin_Iabc
(
...
)
select
from t_fin_clmdue a,t_fin_rpclmcustomer b,t_department c
WHERE a.c_rcpt_no=b.c_rcpt_no
and a.c_dpt_cde=c.c_dpt_cde
AND not exists (SELECT 1 FROM t_fin_iabc WHERE ERP_PAYMENT_ID =a.c_rcpt_no)
and b.c_bala_mrk='4' --通过审核的信息
and a.c_rcpt_no='M040120080337354';
*/
/*通过游标咨询报错的语句exception*M182220080300882ORA-01858: a non-numeric character was found where a numeric was expected */
OPEN cur_abcsaveamt;
LOOP FETCH cur_abcsaveamt
INTO
v_rcpt_no ,
v_record_status ,
v_trecord_status ,
v_payment_type_id ,
v_rp_type ,
v_payment_method_type_id ,
v_payment_accounts ,
v_deposit_bank_name ,
v_deposit_accounts_name ,
v_deposit_accounts ,
v_deposit_province ,
v_deposit_city ,
v_deposit_mobile ,
v_deposit_email ,
v_city_flag ,
v_priority_flag ,
v_operation_type ,
v_bank_number ,
v_amount ,
v_budget_plan_id ,
v_budget_item_id ,
v_purpose ,
v_segment1 ,
v_segment2 ,
v_segment3 ,
v_segment4 ,
v_segment5 ,
v_segment6 ,
v_segment7 ,
v_segment8 ,
v_table_flag ,
v_crt_tm ,
v_edr_typ ,
v_feetyp_cde ,
v_clm_typ ,
v_doc_flag ,
v_flow_no ,
v_clm_no ,
v_ply_no ,
v_dpt_cde ;
EXIT WHEN cur_abcsaveamt%NOTFOUND; --1.付理（仅包括、、垫付，不包括理赔费用和追偿）
INSERT INTO t_fin_Iabc
(
ERP_PAYMENT_ID ,
RECORD_STATUS ,
T_RECORD_STATUS ,
PAYMENT_TYPE_ID ,
C_RP_TYPE ,
PAYMENT_METHOD_TYPE_ID ,
PAYMENT_ACCOUNTS ,
DEPOSIT_BANK_NAME ,
DEPOSIT_ACCOUNTS_NAME ,
DEPOSIT_ACCOUNTS ,
DEPOSIT_PROVINCE ,
DEPOSIT_CITY ,
DEPOSIT_MOBILE ,
DEPOSIT_EMAIL ,
CITY_FLAG ,
PRIORITY_FLAG ,
OPERATION_TYPE ,
BANK_NUMBER ,
AMOUNT ,
BUDGET_PLAN_ID ,
BUDGET_ITEM_ID ,
PURPOSE ,
C_SEGMENT1 ,
C_SEGMENT2 ,
C_SEGMENT3 ,
C_SEGMENT4 ,--明细科目段付款方银行编码？
C_SEGMENT5 ,
C_SEGMENT6 ,
C_SEGMENT7 ,
C_SEGMENT8 ,
C_table_flag ,
T_crt_tm ,
C_edr_typ ,
C_FEETYP_CDE ,
C_CLM_TYP ,
C_doc_flag ,
C_FLOW_NO ,
c_clm_no ,
c_ply_no ,
C_dpt_CDE,
VERSION,CHECK_CODE
)
VALUES
(
v_rcpt_no ,
v_record_status ,
v_record_status ,
v_payment_type_id ,
v_rp_type ,
v_payment_method_type_id ,
v_payment_accounts ,
v_deposit_bank_name ,
v_deposit_accounts_name ,
v_deposit_accounts ,
v_deposit_province ,
v_deposit_city ,
v_deposit_mobile ,
v_deposit_email ,
v_city_flag ,
v_priority_flag ,
v_operation_type ,
v_bank_number ,
v_amount ,
v_budget_plan_id ,
v_budget_item_id ,
v_purpose ,
v_segment1 ,
v_segment2 ,
v_segment3 ,
v_segment4 ,
v_segment5 ,
v_segment6 ,
v_segment7 ,
v_segment8 ,
v_table_flag ,
v_crt_tm ,
v_edr_typ ,
v_feetyp_cde ,
v_clm_typ ,
v_doc_flag ,
v_flow_no ,
v_clm_no ,
v_ply_no ,
v_dpt_cde,
'0','000'
); COMMIT;
END LOOP;
CLOSE cur_abcsaveamt;
EXCEPTION
WHEN OTHERS THEN
BEGIN
--RAISE;
dbms_output.put_line('exception' || '*' || v_rcpt_no || SQLERRM);
ROLLBACK;
END;
END;
错误提示：exception*M182220080300882ORA-01858: a non-numeric character was found where a numeric was expected语句：
Procedure ConvertSaveamt
IS
CURSOR cur_abcsaveamt IS
select
a.c_rcpt_no ,--ERP_PAYMENT_ID ,
'Available',--RECORD_STATUS ,
sysdate ,--T_RECORD_STATUS ,
'202' ,--PAYMENT_TYPE_ID ,
'205' ,--C_RP_TYPE ,
'3' ,--PAYMENT_METHOD_TYPE_ID ,
b.c_customer_accounts ,--PAYMENT_ACCOUNTS ,付款方银行账号？
b.c_bank_accounts,--DEPOSIT_BANK_NAME ,
b.c_accounts_name ,--DEPOSIT_ACCOUNTS_NAME ,
b.c_customer_accounts ,--DEPOSIT_ACCOUNTS ,
b.C_Provinces ,--DEPOSIT_PROVINCE ,
b.c_city ,--DEPOSIT_CITY ,
b.C_Mobile_Phone ,--DEPOSIT_MOBILE ,
b.C_mail ,--DEPOSIT_EMAIL ,
'0' ,--CITY_FLAG ,
'N' ,--PRIORITY_FLAG ,
'3' ,--OPERATION_TYPE ,
b.C_Receive_no ,--BANK_NUMBER ,
a.n_pay_amt ,--AMOUNT ,
null ,--BUDGET_PLAN_ID ,
null ,--BUDGET_ITEM_ID ,
'付' ,--PURPOSE ,
c.c_company_cde ,--C_SEGMENT1 ,
c.c_department_cde ,--C_SEGMENT2 ,
null,--C_SEGMENT3 ,、
null ,--C_SEGMENT4 ,--明细科目段付款方银行编码？
a.c_prod_no ,--C_SEGMENT5 ,
null ,--C_SEGMENT6 ,
null ,--C_SEGMENT7 ,
null ,--C_SEGMENT8 ,
null ,--C_table_flag ,
a.T_crt_tm ,--T_crt_tm ,
null ,--C_edr_typ ,
null ,--C_FEETYP_CDE ,
a.C_CLM_TYP ,--C_CLM_TYP ,
null ,--C_doc_flag ,
'212' ,--C_FLOW_NO 以现金支付的,
a.c_clm_no ,--c_clm_no ,
a.c_ply_no ,--c_ply_no ,
a.C_dpt_CDE --C_dpt_CDE
from t_fin_clmdue a,t_fin_rpclmcustomer b,t_department c
WHERE a.c_rcpt_no=b.c_rcpt_no
and a.c_dpt_cde=c.c_dpt_cde
AND not exists (SELECT 1 FROM t_fin_iabc WHERE ERP_PAYMENT_ID =a.c_rcpt_no)
and b.c_bala_mrk='4' --通过审核的信息
and a.c_rcpt_no='M182220080300882';
v_erp_payment_id t_fin_iabc.erp_payment_id%type;
v_record_status t_fin_iabc.record_status%type;
v_trecord_status t_fin_iabc.t_record_status%type;
v_payment_type_id t_fin_iabc.payment_type_id%type;
v_rp_type t_fin_iabc.c_rp_type%type;
v_payment_method_type_id t_fin_iabc.payment_method_type_id%type;
v_payment_accounts t_fin_iabc.payment_accounts%type;
v_deposit_bank_name t_fin_iabc.deposit_bank_name%type;
v_deposit_accounts_name t_fin_iabc.deposit_accounts_name%type;
v_deposit_accounts t_fin_iabc.deposit_accounts%type;
v_deposit_province t_fin_iabc.deposit_province%type;
v_deposit_city t_fin_iabc.deposit_city%type;
v_deposit_mobile t_fin_iabc.deposit_mobile%type;
v_deposit_email t_fin_iabc.deposit_email%type;
v_city_flag t_fin_iabc.city_flag%type;
v_priority_flag t_fin_iabc.priority_flag%type;
v_operation_type t_fin_iabc.operation_type%type;
v_bank_number t_fin_iabc.bank_number%type;
v_amount t_fin_iabc.amount%type;
v_budget_plan_id t_fin_iabc.budget_plan_id%type;
v_budget_item_id t_fin_iabc.budget_item_id%type;
v_purpose t_fin_iabc.purpose%type;
v_segment1 t_fin_iabc.c_segment1%type;
v_segment2 t_fin_iabc.c_segment2%type;
v_segment3 t_fin_iabc.c_segment3%type;
v_segment4 t_fin_iabc.c_segment4%type;
v_segment5 t_fin_iabc.c_segment5%type;
v_segment6 t_fin_iabc.c_segment6%type;
v_segment7 t_fin_iabc.c_segment7%type;
v_segment8 t_fin_iabc.c_segment8%type;
v_table_flag t_fin_iabc.c_table_flag%type;
v_crt_tm t_fin_iabc.t_crt_tm%type;
v_edr_typ t_fin_iabc.c_edr_typ%type;
v_feetyp_cde t_fin_iabc.c_feetyp_cde%type;
v_clm_typ t_fin_iabc.c_clm_typ%type;
v_doc_flag t_fin_iabc.c_doc_flag%type;
v_flow_no t_fin_iabc.c_flow_no%type;
v_clm_no t_fin_iabc.c_clm_no%type;
v_ply_no t_fin_iabc.c_ply_no%type;
v_dpt_cde t_fin_iabc.c_dpt_cde%type;
v_rcpt_no t_fin_plyedr_coldue.c_rcpt_no%type;
v_err_content t_fin_errorlog.C_ERR_CONTENT%type; BEGIN/*
--1.能够成功的插入语句
INSERT INTO t_fin_Iabc
(
...
)
select
from t_fin_clmdue a,t_fin_rpclmcustomer b,t_department c
WHERE a.c_rcpt_no=b.c_rcpt_no
and a.c_dpt_cde=c.c_dpt_cde
AND not exists (SELECT 1 FROM t_fin_iabc WHERE ERP_PAYMENT_ID =a.c_rcpt_no)
and b.c_bala_mrk='4' --通过审核的信息
and a.c_rcpt_no='M040120080337354';
*/
/*通过游标咨询报错的语句exception*M182220080300882ORA-01858: a non-numeric character was found where a numeric was expected */
OPEN cur_abcsaveamt;
LOOP FETCH cur_abcsaveamt
INTO
v_rcpt_no ,
v_record_status ,
v_trecord_status ,
v_payment_type_id ,
v_rp_type ,
v_payment_method_type_id ,
v_payment_accounts ,
v_deposit_bank_name ,
v_deposit_accounts_name ,
v_deposit_accounts ,
v_deposit_province ,
v_deposit_city ,
v_deposit_mobile ,
v_deposit_email ,
v_city_flag ,
v_priority_flag ,
v_operation_type ,
v_bank_number ,
v_amount ,
v_budget_plan_id ,
v_budget_item_id ,
v_purpose ,
v_segment1 ,
v_segment2 ,
v_segment3 ,
v_segment4 ,
v_segment5 ,
v_segment6 ,
v_segment7 ,
v_segment8 ,
v_table_flag ,
v_crt_tm ,
v_edr_typ ,
v_feetyp_cde ,
v_clm_typ ,
v_doc_flag ,
v_flow_no ,
v_clm_no ,
v_ply_no ,
v_dpt_cde ;
EXIT WHEN cur_abcsaveamt%NOTFOUND; --1.付理（仅包括、、垫付，不包括理赔费用和追偿）
INSERT INTO t_fin_Iabc
(
ERP_PAYMENT_ID ,
RECORD_STATUS ,
T_RECORD_STATUS ,
PAYMENT_TYPE_ID ,
C_RP_TYPE ,
PAYMENT_METHOD_TYPE_ID ,
PAYMENT_ACCOUNTS ,
DEPOSIT_BANK_NAME ,
DEPOSIT_ACCOUNTS_NAME ,
DEPOSIT_ACCOUNTS ,
DEPOSIT_PROVINCE ,
DEPOSIT_CITY ,
DEPOSIT_MOBILE ,
DEPOSIT_EMAIL ,
CITY_FLAG ,
PRIORITY_FLAG ,
OPERATION_TYPE ,
BANK_NUMBER ,
AMOUNT ,
BUDGET_PLAN_ID ,
BUDGET_ITEM_ID ,
PURPOSE ,
C_SEGMENT1 ,
C_SEGMENT2 ,
C_SEGMENT3 ,
C_SEGMENT4 ,--明细科目段付款方银行编码？
C_SEGMENT5 ,
C_SEGMENT6 ,
C_SEGMENT7 ,
C_SEGMENT8 ,
C_table_flag ,
T_crt_tm ,
C_edr_typ ,
C_FEETYP_CDE ,
C_CLM_TYP ,
C_doc_flag ,
C_FLOW_NO ,
c_clm_no ,
c_ply_no ,
C_dpt_CDE,
VERSION,CHECK_CODE
)
VALUES
(
v_rcpt_no ,
v_record_status ,
v_record_status ,
v_payment_type_id ,
v_rp_type ,
v_payment_method_type_id ,
v_payment_accounts ,
v_deposit_bank_name ,
v_deposit_accounts_name ,
v_deposit_accounts ,
v_deposit_province ,
v_deposit_city ,
v_deposit_mobile ,
v_deposit_email ,
v_city_flag ,
v_priority_flag ,
v_operation_type ,
v_bank_number ,
v_amount ,
v_budget_plan_id ,
v_budget_item_id ,
v_purpose ,
v_segment1 ,
v_segment2 ,
v_segment3 ,
v_segment4 ,
v_segment5 ,
v_segment6 ,
v_segment7 ,
v_segment8 ,
v_table_flag ,
v_crt_tm ,
v_edr_typ ,
v_feetyp_cde ,
v_clm_typ ,
v_doc_flag ,
v_flow_no ,
v_clm_no ,
v_ply_no ,
v_dpt_cde,
'0','000'
); COMMIT;
END LOOP;
CLOSE cur_abcsaveamt;
EXCEPTION
WHEN OTHERS THEN
BEGIN
--RAISE;
dbms_output.put_line('exception' || '*' || v_rcpt_no || SQLERRM);
ROLLBACK;
END;
END;
解决方案 »
- oracle游标使用在where中
- oracle 遇到权限问题,疑惑
- 【求助】oracle接口表、临时表、中间表？
- 急求答案分不够可以再给 在workbench 从sqlserver2000 导入到Oracle 的问题
- 表空间问题
- 如何选择使用数据库（sql server 和 Oralce ），它们之间的差别是什么，请各位数据库大虾参与讨论，公司要我出个方案，急啊！
- where与order by的执行顺序?
- 还有一个问题，在linux下，要对oracle9i进行数据备份，一定要启动Intelligent Agent吗？还有其他方式吗？
- oracle错误的含义
- 简单的问题！在线等待！分不够再加！
- 求一个SQL
- 大家看看这条SQL语句是什么意思啊？select可以嵌套吗
仔细检查下就行了
INSERT INTO t_fin_Iabc
(...)select from
就可以，类型应该没有问题，整个表只有两个数字型的数据，AMOUNT肯定是数字，而且我最后的'0','000'
修改成0，'000',还是报同样的问题。AMOUNT NUMBER(16,2) NOT NULL,
VERSION INTEGER NOT NULL,