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:
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
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.
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.
{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.