并发程序报错：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, '&nbsp') || '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' ||
                        nvl(c_fa_vendors.parent_company_name || '&nbsp',
                            '&nbsp') || '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' ||
                        nvl(c_fa_vendors.trx_type_meaning, '&nbsp') ||
                        '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' ||
                        nvl(c_fa_vendors.org_name, '&nbsp') || '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' ||
                        nvl(to_char(c_fa_vendors.gl_date, 'yyyy-mm-dd'),
                            '&nbsp') || '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' ||
                        nvl(to_char(c_fa_vendors.trx_date, 'yyyy-mm-dd'),
                            '&nbsp') || '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap x:str>' ||
                        nvl(c_fa_vendors.trx_number, '&nbsp') || '</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, '&nbsp') ||
                        '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' || nvl(c_fa_vendors.cr_conn, '&nbsp') ||
                        '</td>');
      fnd_file.put_line(fnd_file.output,
                        '<td nowrap>' ||
                        nvl(c_fa_vendors.comments, '&nbsp') || '</td>');
      v_records_count := v_records_count + 1;
    
    END LOOP;
    CLOSE defcursor1;