ORA-02019

ORA-02019: Connection Descriiiption for Remote database not found

CREATE OR REPLACE procedure SP_UPDATEADDR_ATLAS is
  TYPE ref_cursor IS REF CURSOR;
  rc               ref_cursor;
  stats_record     varchar2(8);
  tabname          varchar2(256);
  filename         varchar2(256);
  v_stmt           varchar2(512);
  created_temp     date;
  lastname_temp    varchar2(30);
  firstname_temp   varchar2(30);
  addr1_temp       varchar2(80);
  addr2_temp       varchar2(80);
  homephone_temp   varchar2(30);
  city_temp        varchar2(30);
  state_temp       varchar2(30);
  zip_temp         varchar2(30);
  country_temp     varchar2(50);
  email_temp       varchar2(256);
  passthrough_temp varchar2(256);
  ip_temp          varchar2(30);
  source_temp      varchar2(50);
  owner_temp       varchar2(50);
  fulltbl_temp     varchar2(50);
  accountid_temp   number(11);
  siteid_temp      number(11);
  campaignid_temp  number(11);
  loop_ctr         number(11);
  programid_temp   number(11);
  output_file      utl_file.file_type;
  CURSOR Get_Atlas_Email IS
    select email_addr
      from atlas@EMHP
     where active = 9
       and source_id = 1430;
  CURSOR Get_Table_Name IS
    select distinct (tablename) tablename, owner
      from phoenix.table_data td, dba_tab_columns dtc
     where td.tablename = dtc.table_name
       and status = 1
       and dtc.column_name in ('ADDRESS1', 'ZIP', 'CITY', 'STATE')
       and owner = 'SALES'
     order by tablename;
BEGIN
  --Open file for Write
  filename    := 'atlas' || to_char(sysdate, 'YYYYMMDD') || '.txt';
  output_file := utl_file.fopen('/oradata/u99/',
                                'atlas_' || to_char(sysdate, 'YYYYMMDD') ||
                                '.txt',
                                'W');
  -- FOR tab_rec IN Get_Table_Name LOOP
  -- loop_ctr := 0;
  -- tabname := tab_rec.owner || '.' || tab_rec.tablename;
  OPEN Get_Table_Name;
  LOOP
    loop_ctr := 0;
    FETCH Get_Table_Name
      INTO tabname, owner_temp;
    fulltbl_temp := owner_temp || '.' || tabname;
    OPEN Get_Atlas_Email;
    LOOP
      loop_ctr := loop_ctr + 1;
      FETCH Get_Atlas_Email
        INTO email_temp;
      v_stmt := 'Select address1,address2,city,state,zip, ipaddress,created from sales.' ||
                tabname || ' where upper(email) = ' || email_temp;
      -- open cursor
      OPEN rc FOR v_stmt;
      LOOP
        loop_ctr := loop_ctr + 1;
        FETCH rc
          INTO addr1_temp, addr2_temp, city_temp, state_temp;
        EXIT WHEN rc%NOTFOUND or rc%NOTFOUND is null;
        utl_file.put_line(output_file,
                          '"' || email_temp || '"' || CHR(9) || '"' ||
                          firstname_temp || '"' || CHR(9) || '"' ||
                          lastname_temp || '"' || CHR(9) || '"' ||
                          rtrim(ip_temp) || '"' || CHR(9) || '"' ||
                          created_temp || '"');
      END LOOP;
      --END LOOP;
      EXIT WHEN Get_Atlas_Email%NOTFOUND or Get_Atlas_Email%NOTFOUND is null;
    END LOOP;
  END LOOP;
  -- Close output file
  utl_file.fclose(output_file);
END;
/

问题在于

v_stmt := 'Select address1,address2,city,state,zip, ipaddress,created from sales.' || tabname || ' where upper(email) = '|| email_temp;

OPEN rc FOR v_stmt;

应该为：

v_stmt := 'Select address1,address2,city,state,zip, ipaddress,created from sales.' || tabname || ' where upper(email) = :b1';

OPEN rc FOR v_stmt USING email_temp;

首先需要使用绑定变量。

其次，EMAIL中包含@字符，因此Oracle会执行SELECT ... FROM table@fred where

email=[e-mail]；

需要注意，在Oracle中，@是特殊字符，需要使用ASCII替代。