Read OUTPUT Parameter from Stored Procedure
I'm not sure what I am doing wrong, but I always get a null when I try to assign to an output parameter. Basically I'm trying to return a message to the client application from the procedure.
Here p_Status never returns a value. What am I doing wrong?
PROCEDURE proc_ins_NewsReleaseEvent(p_ReferencePeriod IN news_release_events.reference_period%TYPE,
p_NewsReleaseID IN news_release_events.news_release_id%TYPE,
p_ReleaseDate IN news_release_events.release_date%TYPE,
p_UpdatedBy IN news_release_events.updated_by%TYPE,
p_Status OUT varchar2)
IS
v_count PLS_INTEGER;
BEGIN
SELECT COUNT(news_release_id)
INTO v_count
FROM labstat.news_release_events
WHERE reference_period = p_ReferencePeriod
AND news_release_id = p_NewsReleaseID
AND TO_CHAR(release_date,'WW') = TO_CHAR(p_ReleaseDate,'WW');
IF v_count = 0 THEN
INSERT INTO labstat.news_release_events
(news_release_id, reference_period, release_date, created_on,
updated_on, updated_by )
VALUES
(p_NewsReleaseID, p_ReferencePeriod,
p_ReleaseDate,sysdate, sysdate, p_UpdatedBy);
COMMIT;
p_Status := 'News release entry inserted';
END IF;
END;