3 Replies Latest reply: Mar 16, 2009 3:56 AM by 650470 RSS

    Multithreaded EXTPROC.EXE and APEX

    650470
      Hi there. There's my problem:
      I've deployed c# function into DB and if I execute it directly (from SQL Developer by example) it works well.
      But asap as I wrap it around with "begin" and "end;" => trying to call it from pl/sql procedure it works as intended, but afterwards leaves extproc.exe in memory.
      so after many calls I've got lots of extproc.exe in my tray... and soon system slows down)

      what's the cause? pls help!

      Edited by: imbashamba on Feb 20, 2009 3:37 AM

      Edited by: imbashamba on Mar 16, 2009 1:56 AM
        • 1. Re: Calling stored .net function from within PL/SQL procedure - EXTPROC.EXE
          650470
          I've found the source of multiple extproc.exe! It's because of new session for each function call from APEX (mod_plsql connection pool). So now I intend to use multithreaded extproc.

          But my new problem, is that multithreaded extproc.exe doesn't execute my function. It receives a call, starts working somehow(in tray processor usage rises to 13%, memory usage to 100+ Kb) but does nothing(
          How can I make it work?
          • 2. Re: Calling stored .net function from within PL/SQL procedure - EXTPROC.EXE
            502182
            How did you start the multithreaded extproc? Was it done using OraClrAgnt service? Are you indeed using .NET stored procedure deployed using Oracle Developer Tools for .NET (ODT)?
            • 3. Re: Calling stored .net function from within PL/SQL procedure - EXTPROC.EXE
              650470
              Yep, I started multithreaded extproc via OraClrAgent, the procedure was deployed by ODT for .NET.
              The problem was in parameter shutdown_address: "KEY=listener_sid || agent_sid", I've used only listener_sid instead of union.
              now it works)

              my new problem, is ORA-28576: lost RPC connection to external procedure agent started appearing too often
              I suppose it's because of new session for each procedure call

              I've made a process - a loop of 10 iterations to try executing my stored procedure, with an exception for ORA-28576 wich executes the procedure once again:

              BEGIN

              v_seq := analytics.reportinput.nextval();
              v_counter :=0;
              while v_counter<10 loop
              BEGIN
              ANALYTICS.INTERNALL_CALL(:P13_SELECT_DAY,:P13_SELECT_MONTH,:P13_SELECT_YEAR,v_seq);
              :P7_X := v_seq;
              RETURN;

              EXCEPTION
              WHEN RPC_LOST THEN
              v_counter:=v_counter+1;
              END;
              end loop;

              END;

              my intention was to make that process 'scan' connection pool for an active session
              it works fine, but is there a better way to solve it?