英雄帖:Oracle在procedures中读取sql server表的值变为Null

Question

问题描述：
在使用透明网关建立Oracle与Sqlserver的连接，在Oracle中使用单独一句SQL读Sqlserver表的值可以正常显示，然后我把SQL放到procedure中运行，发现返回来的值为“NULL”,非常困扰！广发英雄帖求救。

Answer 1

以下是语句：
 cursor checkmeal(s_date date, e_date date) is
    select to_number(c."card_sequ"),
           c."card_id",
           c."sign_time",
           c."clock_id",
           c."emp_id",
           c."card_consume",
           c."kind",
           c."mealtype",
           c."card_balance",
           c."card_times"
      into t_card_sequ,
           t_card_id,
           t_sign_time,
           t_clock_id,
           t_emp_id,
           t_card_consume,
           t_kind,
           t_mealtype,
           t_card_balance,
           t_card_times
      FROM MealRecords@eastriver C,
           employee@eastriver    E,
           Departs@eastriver     D
     where E."depart_id" = D."depart_id"(+)
       and E."emp_id" = C."emp_id"(+)
       and c."mealtype" = '0'
       and (c."flag" = 0 or c."flag" = 7)
       And E."depart_id" Like '%'
       And E."emp_id" Like '82%'
       and ("transfer_flag" is null or "transfer_flag" = 0)
       and c."op_ymd" <= e_date
       and C."op_ymd" >= s_date;
begin
  for c1 in checkmeal(i_sdate, i_edate) loop
    --check this meal has downloaded or not
    select count(sn)
      into v_cnt
      from con_record
     where rdatetime = t_sign_time
       and sn = t_card_sequ
       and cardno = t_card_id
       and card_times = t_card_times
       and balance = t_card_balance;
    if v_cnt = 0 then
      begin
        --select sysdate into v_sysdate from dual;
        insert into con_record
          (sn,
           cardno,
           rdatetime,
           clock_id,
           personnelid,
           conmoney,
           kind,
           mealtype,
           creation_date,
           balance,
           card_times)
        values
          (t_card_sequ,
           t_card_id,
           t_sign_time,
           t_clock_id,
           t_emp_id,
           t_card_consume * 100,
           t_kind,
           t_mealtype,
           sysdate,
           t_card_balance,
           t_card_times);
      exception
        when others then
          v_except := 2;
      end;
      v_download_count := v_download_count + 1;
    end if;
  end loop;
  commit;