Stored Procedures - Output value disappears when switching type?

[CognosPaul]

I have a stored procedure, let's call it UPD_METRIC_VALUES. The parameters are all set correctly with prompt macros. There is an output value called SID' When it's run as type "Data Query" it seems to work, the SID seems to be correct, except not updating the database.

When I change the type to Data Modification the output value disappears. The SP validates with "Stored procedure executed successfully", but no values are modified in the database with the test, and because the output disappeared it is not referenceable from RS.

There are conflicting historical posts on wether or not a data modification SP can be run from RS, and I can't seem to find anything anywhere else that describes the problem I'm having.

The full code of the SP is this:

Code Select Expand

CREATE OR REPLACE PROCEDURE ILOPCPM.upd_metric_values(ticket            user_session.ticket_id%type,
                                              i_kpi_sid         kpi.kpi_sid%type,
                                              i_time_period_sid time_periods.time_period_sid%type,
                                              i_currency        currency.currency_cd%type,
                                              i_actual          kpi_period_history.actual_value%type DEFAULT NULL,
                                              i_target          kpi_period_history.target_value%type DEFAULT NULL,
                                              i_tolerance       kpi_period_history.tolerance_value%type DEFAULT NULL,
                                              i_benchmark       kpi_period_history.benchmark_value%type DEFAULT NULL,
                                              i_benchmark2      kpi_period_history.benchmark2_value%type DEFAULT NULL,
                                              i_benchmark3      kpi_period_history.benchmark3_value%type DEFAULT NULL,
                                              i_benchmark4      kpi_period_history.benchmark4_value%type DEFAULT NULL,
                                              i_benchmark5      kpi_period_history.benchmark5_value%type DEFAULT NULL,
                                              o_cur1            OUT TYPES.cursortype) AS
  l_user_value_sid kpi_user_value.kpi_user_value_sid%TYPE;
  kph_rec          kpi_period_history%ROWTYPE;
BEGIN
  BEGIN

          -- See if there is a user value entry for this kpi, currency & time period
          select kpi_user_value_sid
      into l_user_value_sid
                  from kpi_user_value
                          inner join kpi kpi on kpi.kpi_sid=kpi_user_value.kpi_sid
                          inner join kpi_class_currency kcc on kpi.kpi_class_sid=kcc.kpi_class_sid
                                  and kcc.currency_cd=i_currency
                                  and kpi_user_value.unit_lookup_id=kcc.unit_lookup_id
                  where time_period_sid = i_time_period_sid and kpi_user_value.kpi_sid = i_kpi_sid;

    INSERT INTO user_value_archive
      (kpi_user_value_sid,
       kpi_sid,
       time_period_sid,
       unit_lookup_id,
       actual_value,
       target_value,
       tolerance_raw,
       score_value,
       benchmark_value,
       benchmark2_value,
       benchmark3_value,
       benchmark4_value,
       benchmark5_value,
       security_item_sid,
       created_dt)
      SELECT kpi_user_value_sid,
             kpi_sid,
             time_period_sid,
             unit_lookup_id,
             actual_value,
             target_value,
             tolerance_raw,
             score_value,
             benchmark_value,
             benchmark2_value,
             benchmark3_value,
             benchmark4_value,
             benchmark5_value,
             security_item_sid,
             created_dt
        FROM kpi_user_value
       WHERE kpi_user_value_sid = l_user_value_sid;

    UPDATE kpi_user_value
                          SET actual_value = i_actual,
                             target_value = i_target,
                             tolerance_raw = i_tolerance,
                             benchmark_value = i_benchmark,
                             benchmark2_value = i_benchmark2,
                             benchmark3_value = i_benchmark3,
                             benchmark4_value = i_benchmark4,
                             benchmark5_value = i_benchmark5,
           security_item_sid = (SELECT app_user_sid FROM user_session WHERE ticket_id = ticket),
           created_dt        = SYSDATE
     WHERE kpi_user_value_sid = l_user_value_sid;

    IF SQL%ROWCOUNT = 0 THEN
      debug.raise_app_error('NO_ROW_UPD', ticket);
    END IF;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN

      -- When there are no entries insert the new user values
      select s_content_object.NEXTVAL into l_user_value_sid from dual;

      INSERT INTO kpi_user_value
        (kpi_user_value_sid,
         kpi_sid,
         time_period_sid,
         unit_lookup_id,
         actual_value,
         target_value,
         tolerance_raw,
         benchmark_value,
         benchmark2_value,
         benchmark3_value,
         benchmark4_value,
         benchmark5_value,
         security_item_sid,
         created_dt,
         processed)
        SELECT l_user_value_sid,
               kpi.kpi_sid,
               i_time_period_sid,
               kcc.unit_lookup_id,
                                 i_actual,
                                 i_target,
                                 i_tolerance,
                                       i_benchmark,
                                       i_benchmark2,
                                       i_benchmark3,
                                       i_benchmark4,
                                       i_benchmark5,
               (SELECT app_user_sid FROM user_session WHERE ticket_id = ticket),
               SYSDATE,
               'N'
          FROM kpi kpi
          INNER JOIN kpi_class_currency kcc ON kpi.kpi_class_sid = kcc.kpi_class_sid
                AND kcc.currency_cd = i_currency
         WHERE kpi.kpi_sid = i_kpi_sid;
  END;

  -- return the status in a result set
  OPEN o_cur1 FOR
    SELECT l_user_value_sid AS SID FROM DUAL;

EXCEPTION
  WHEN OTHERS THEN
    DEBUG.raise_app_error('INS_KPI_VR_FAILED', ticket);
END;
/

Thanks

[MFGF]

Hi Paul,

If memory serves, Data Modification SPs are only supported via calls from Event Studio.  I have heard stories of people having success in calling SPs to update data from Report Studio, but I don't know details, sorry - not sure whether they were defined as Data Retrieval or Data Modification.

One off-the-wall thought - how is the data source connection defined in C8 in terms of its isolation level?  If it has been manually specified as "read committed", for example, it may be utilising read-only db transactions?  Probably a complete red herring, but possibly worth a quick check?

MF.

[CognosPaul]

Well, I've had luck before with running SPs from within report studio. The following will work in an SQL item on a report on top of an SQL Server.

Code Select Expand

{exec dbo.upd_metric_values(#sq(CAMPassport())#,#sq(prompt('KPI','integer'))#,...)}

The idea to check the connection may not be such a red herring after all. I don't have access to the data source connections at this site so it could be that. I had one of the other guys try to get the SP to run through Toad with no luck. We're going to be talking to the DBA to see if it's an issue with the provider.

Another thing I'm going to try is writing a simple SP and seeing how Cognos handles importing that to FM. It could just be the way the SP is structured. If I can get an extremely simple insert statement to work then it's just a matter of building it up to what I need.

Thanks for the suggestion.