5 Replies Latest reply: Mar 14, 2013 6:30 AM by 930918 RSS

    Oracle 11g Heterogeneous Error

    930918
      Info :
      - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      - OS Windwos 2008 R2
      - System DSN called kotvall which is pointing to my Excel file.
      - initkotvall.ora looks like this :

      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC

      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = kotvall
      HS_FDS_TRACE_LEVEL = DEBUG
      HS_FDS_SHAREABLE_NAME = c:\TEMP


      #
      # Environment variables required for the non-Oracle system
      #
      #set <envvar>=<value>

      - listener.ora  :

      # listener.ora Network Configuration File: E:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      )
      (SID_DESC=
      (SID_NAME=kotvall)
      (ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1)
      (PROGRAM=dg4odbc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORION.apvrt.hu)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = E:\oracle

      -tnsnames.ora :

      # tnsnames.ora Network Configuration File: E:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.

      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
      )
      )

      SATPROD =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORION.apvrt.hu)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = satprod)
      )
      )

      kotvall =
      (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.254.227)(PORT=1521))
      (CONNECT_DATA=(SID=kotvall))
      (HS=OK)
      )

      - kotvall dblink :
      create database link KOTVALL using 'kotvall';

      I did manage to configure HS,

      select * from "SQL Results$"@kotvall gives me back the correct records,
      but still having a problem :

      I want to have a job which is running every 3 minutes and insert all record from this Excel file to a table called biusr.int_kvkig_kotvall but every time the job runs it gives me the following error message :

      "ORA-28513: internal error in heterogeneous remote agent ORA-02063: preceding line from KOTVALL"

      begin
      sys.dbms_job.submit(job => :job,
      what => 'DECLARE
      l_holiday VARCHAR2(1);
      l_errmsg VARCHAR2(400);
      BEGIN
      SELECT holiday INTO l_holiday FROM biusr.mnvdate@centprod WHERE datum = TRUNC(SYSDATE);
      IF l_holiday = ''M'' THEN
      biusr.kotvall_betolt;
      END IF;
      EXCEPTION WHEN OTHERS THEN
      l_errmsg:=SQLERRM;
      biusr.send_mail@centprod(''adatvagyon@mnv.hu'',''JOB hiba - KOTVALL töltés'', l_errmsg);
      END;',
      next_date => to_date('13-03-2013', 'dd-mm-yyyy'),
      interval => 'sysdate+1/(24*20)');
      commit;
      end;
      /

      Package biusr.kotvall_betolt :
      create or replace procedure kotvall_betolt
      is
      begin
      delete from biusr.int_kvkig_kotvall;
      insert into biusr.int_kvkig_kotvall select id,ceg,srszam,megnevezes from "SQL Results$"@kotvall;
      commit;
      end;

      Please help!

      Regards,
      Akos
        • 1. Re: Oracle 11g Heterogeneous Error
          Mkirtley-Oracle
          Akos,
          Have you tried the SQL in the procedure directly in SQLPLUS -

          delete from biusr.int_kvkig_kotvall;
          insert into biusr.int_kvkig_kotvall select id,ceg,srszam,megnevezes from "SQL Results$"@kotvall;
          commit ;

          Does it give the same error ?

          Whayt happens if you change it -

          delete from biusr.int_kvkig_kotvall;
          commit ;
          insert into biusr.int_kvkig_kotvall select id,ceg,srszam,megnevezes from "SQL Results$"@kotvall;
          commit ;

          Regards,
          Mike
          • 2. Re: Oracle 11g Heterogeneous Error
            930918
            Hi Mike,

            First of all, thank you for the quick reply but I do not get any error when i try to insert into my table using the above SQL but I'v copied the result from sqlplus for you :

            SQL> delete from biusr.int_kvkig_kotvall;

            480 rows deleted.

            SQL> commit;

            Commit complete.

            SQL> insert into biusr.int_kvkig_kotvall select id,ceg,srszam,megnevezes from "S
            ql Results$"@KOTVALL;

            480 rows created.

            SQL> commit;

            Commit complete.

            SQL>

            I do get the "ORA-28513: internal error in heterogeneous remote agent ORA-02063: preceding line from KOTVALL" message only when the job tries to issue the DML commands.

            Regards,
            Akos
            • 3. Re: Oracle 11g Heterogeneous Error
              Mkirtley-Oracle
              Akos,
              In the SQLPLUS example you have used a 'commit' between the delete and the insert. Does the procedure run successfully if you use a commit between the delete and insert in the procedure code ?

              Regards,
              Mike
              • 4. Re: Oracle 11g Heterogeneous Error
                930918
                Hi Mike,

                The procedure runs successfully either way, with or without the commit between the delete and insert statement. The problem occurs only when I try to run the procedure (or the actual code) inside the job.
                And one more thing, it seems like dg4odbc.exe puts an exclusive lock on the Excel file, so i even tried to commit and close the dblink after the insert statement without luck.

                Regards,
                Akos
                • 5. Re: Oracle 11g Heterogeneous Error
                  930918
                  The problem is resolved. I had to recreate the dblink using a windows username and password.