并发程序报错:ORA-01007: variable not in select list(选择列表中没有变量) 以及相关动态指针写法
网上的解决方法:
Cause: | A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error. |
Action: | Determine which of the problems listed caused the problem and take appropriate action. |
select语句本身没有问题,错误原因是:在构造游标时数据个数不一致。比如,一个表TA有3个域A,B,C。我们构造查询语句为:SELECT A FROM TA WHERE B='12' AND C='12'。但是在构造游标时,使用了EXEC SQL FETCH table_cursor INTO :A,:B,:C;,而不是EXEC SQL FETCH table_cursor INTO :A;。这样变量数目就不一致,会导致错误。
实际的问题:在使用动态指针的时候,将资料指到对应的SQL 并且fetch into 相对应的 record 类型,但是此记录定义的变量与SQL指针的变量不一致导致的。
TYPE r_fa_vendor IS RECORD( company_id NUMBER, company_code VARCHAR2(30), company_name VARCHAR2(240), company_type VARCHAR2(80), juristic_person VARCHAR2(80), register_address VARCHAR2(240), parent_company_id NUMBER, parent_company_name VARCHAR2(240), org_code VARCHAR2(10), application_id NUMBER, set_of_books_id NUMBER, org_id NUMBER, org_name VARCHAR2(240), trx_hdr_id NUMBER, trx_number VARCHAR2(240), trx_date DATE, trx_type VARCHAR2(150), trx_type_meaning VARCHAR2(240), guanlian_sum_flag VARCHAR2(240), gl_date DATE, dr_conn VARCHAR2(240), cr_conn VARCHAR2(240), acctd_amount NUMBER, comments VARCHAR2(1800)); TYPE defcursor IS REF CURSOR; c_fa_vendors r_fa_vendor; defcursor1 defcursor; p_where VARCHAR2(1000) := ''; v_sql VARCHAR2(5000) := ''; BEGIN /* fnd_file.put_line(fnd_file.log, '开始日期为:' || p_date_from); fnd_file.put_line(fnd_file.log, '结束日期为:' || p_date_to);*/ IF p_company IS NOT NULL THEN p_where := p_where || ' (parent_company_id = ' || p_company || ' or company_id = ' || p_company || ') and '; ELSE p_where := p_where || ' 1 = 1 and '; END IF; IF p_trx_type IS NOT NULL THEN p_where := p_where || ' trx_type = ''' || p_trx_type || ''' and '; ELSE p_where := p_where || ' 1 = 1 and'; END IF; IF p_date_from IS NOT NULL THEN p_where := p_where || ' gl_date >= to_date(substr(''' || p_date_from || ''', 1, 10), ''YYYY-MM-DD'') and '; ELSE p_where := p_where || ' 1 = 1 and '; END IF; IF p_date_to IS NOT NULL THEN p_where := p_where || ' gl_date <= to_date(substr(''' || p_date_to || ''', 1, 10), ''YYYY-MM-DD'') and '; ELSE p_where := p_where || ' 1 = 1 and '; END IF; IF p_guanlian_sum_flag IS NOT NULL THEN p_where := p_where || ' guanlian_sum_flag = ' || p_guanlian_sum_flag || ''; ELSE p_where := p_where || ' 1 = 1'; END IF; v_sql := ' select company_id,company_code,company_name,company_type,juristic_person,register_address,parent_company_id,parent_company_name,org_code, application_id,set_of_books_id,org_id,org_name,trx_hdr_id,trx_number,trx_date,trx_type,trx_type_meaning,guanlian_sum_flag,gl_date,replace(dr_conn,'';''), replace(cr_conn,'';''),acctd_amount,comments FROM transactions_v1 WHERE application_id = 200 AND set_of_books_id = ' ||p_set_of_books_id || ' and ' || p_where || ' ORDER BY company_name, trx_type, org_id, gl_date, trx_number '; OPEN defcursor1 FOR v_sql; LOOP FETCH defcursor1 INTO c_fa_vendors; EXIT WHEN defcursor1%NOTFOUND; v_sum := v_sum + c_fa_vendors.acctd_amount; v_num := v_num + 1; fnd_file.put_line(fnd_file.output, '<tr>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.company_name, ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.parent_company_name || ' ', ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.trx_type_meaning, ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.org_name, ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(to_char(c_fa_vendors.gl_date, 'yyyy-mm-dd'), ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(to_char(c_fa_vendors.trx_date, 'yyyy-mm-dd'), ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap x:str>' || nvl(c_fa_vendors.trx_number, ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || to_char(nvl(round(c_fa_vendors.acctd_amount, 2), '0'), 'FM9,999,999,999,990.00') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.dr_conn, ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.cr_conn, ' ') || '</td>'); fnd_file.put_line(fnd_file.output, '<td nowrap>' || nvl(c_fa_vendors.comments, ' ') || '</td>'); v_records_count := v_records_count + 1; END LOOP; CLOSE defcursor1;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步