Skip to Content

Calling web service client procedure from within DB trigger

Hello, all

I get "Illegal cursor operation attempt" when calling web service client procedure form within a trigger.

I found no reference to this limitation in the documentation or in the web search.

Basically, I have a table with several data fields and a "send" flag. When "send" flag becomes 1, trigger fires, which calls web service client procedure.

The procedure executes successfully, but the operation stops with the error and the "send" field is not updated.

Can you please, tell me if there is a limitation of launching web service client procedure from a trigger?

Thank you

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 30, 2017 at 06:50 PM

    This is an after column update trigger.

    The only code in there is the call to the web client procedure.

    Version is 16

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 01, 2017 at 10:14 AM

    With v16.0.0.2546 I certainly can call web client functions or procedures within a trigger.

    ----

    When you call a web client procedure, how do you "consume" the result set / return value in your trigger? Note that within a trigger each result set of a select or call statement must be consumed because the trigger cannot "return it" itself. You dan do so by using "select into" or the like or by using a cursor loop to fetch each row of the returned result set.

    The error message "Illegal cursor operation attempt" seems to imply a return set is not correctly consumed. I.e. I do also get this message when I simply "call" the web client procedure.

    Here's a sample using the ECB's Euro exchange rates:

    -- Web client function for Euro exchange rates as published by the ECB
    create function WCF_ECB_DailyExchangeRates() returns XML
    URL 'HTTP://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml' 
    type 'HTTP:GET';
    select WCF_ECB_DailyExchangeRates();
    
    -- Web client function for Euro exchange rates as published by the ECB
    create or replace procedure WCP_ECB_DailyExchangeRates()
    result (Attribute long varchar, Value long varchar)
    URL 'HTTP://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml' 
    type 'HTTP:GET';
    
    
    -- sample table with trigger
    create table T_Test
    (
      pk_Test  int default autoincrement,
      flagSend  bit default 0
    );
    
    create or replace trigger TIU_Test
      before insert, update on T_Test
      referencing new as N
      for each row 
    begin
      if N.flagSend = 1 then
         -- first attempt: Call procedure without consuming the result set
         call WCP_ECB_DailyExchangeRates();
      end if;
    end;
    
    insert T_Test(flagSend) values (0); -- ok: No procedure call
    insert T_Test(flagSend) values (1);
     -- error SQLCODE -187: "Illegal cursor operation attempt"
    
    
    -- modified trigger: consume procedure result set
    create or replace trigger TIU_Test
      before insert, update on T_Test
      referencing new as N
      for each row 
    begin
      if N.flagSend = 1 then
         -- second attempt: Consume result set via FOR loop
         for crs as crsLoop cursor for
           call WCP_ECB_DailyExchangeRates()
         do
            message 'Attribute ' || Attribute || '...' to client; 
         end for;
      end if;
    end;
    
    insert T_Test(flagSend) values (1); -- OK now
    
    -- re-modified trigger: consume function return value
    create or replace trigger TIU_Test
      before insert, update on T_Test
      referencing new as N
      for each row 
    begin
      if N.flagSend = 1 then
         -- third attempt: Consume function return value
         begin
             declare xmlValue xml;
             set xmlValue = WCF_ECB_DailyExchangeRates();
         end;
      end if;
    end;
    
    insert T_Test(flagSend) values (1); -- >OK, as well
    Add comment
    10|10000 characters needed characters exceeded

  • Dec 04, 2017 at 11:25 AM

    My web call is a POST, not a GET.

    However, this is now a purely academic question. I decided to implement a C# based WS and a application web client, so I do not use the triggers anymore.

    Thank you for your time

    Arcady

    Add comment
    10|10000 characters needed characters exceeded

    • Well, I don't think the behaviour would be different for a POST method call - AFAIK, each web client function/procedure does return "something" simply because it has to supply the response of the web server to the caller, and that "something" has to be consumed within the trigger.