I'm not sure if I'm missing something obvious here. I have a stored procedure for my Oracle SQL server (11g).
I'm using flow control to keep my INSERT, UPDATE, DELETE functionality within 1 stored procedure. The weird issue I'm running into is that all of the functionality of this works, but when running an UPDATE or DELETE I receive a log from SQL Developer that has no source or message. The proper changes are committed to the database, but I receive an annoying blank log and I am just confused at this point.
Any info would be great! Thanks
create or replace PROCEDURE DRAWINGOPERATION
(
P_CURSOR OUT SYS_REFCURSOR
, P_OPERATION IN VARCHAR2
, P_DRAWINGID IN NUMBER
, P_DRAWINGDATE IN DATE
, P_PRIZEAMOUNT IN VARCHAR2
, P_GAMEID IN NUMBER
) AS
BEGIN
DECLARE
return_drawingid NUMBER;
BEGIN
CASE p_operation
WHEN 'INSERT' THEN GOTO c_Insert;
WHEN 'UPDATE' THEN GOTO c_Update;
WHEN 'DELETE' THEN GOTO c_Delete;
ELSE GOTO end_sec;
END CASE;
<<c_Insert>>
INSERT INTO DRAWING(GAMEID,DRAWINGDATE,PRIZEAMOUNT) VALUES (p_gameid,p_drawingdate,p_prizeamount)
RETURNING DRAWINGID
INTO return_drawingid;
COMMIT;
OPEN P_CURSOR FOR SELECT * FROM DRAWING WHERE DRAWINGID = return_drawingid;
GOTO end_sec;
<<c_Update>>
IF p_drawingdate IS NOT NULL THEN UPDATE DRAWING SET DRAWINGDATE = P_DRAWINGDATE WHERE DRAWINGID = P_DRAWINGID; END IF;
IF p_prizeamount IS NOT NULL THEN UPDATE DRAWING SET PRIZEAMOUNT = P_PRIZEAMOUNT WHERE DRAWINGID = P_DRAWINGID; END IF;
COMMIT;
GOTO end_sec;
<<c_Delete>>
--PRIMARY KEY DELETE
DELETE
FROM DRAWING
WHERE drawingid = P_DRAWINGID;
GOTO end_sec;
<<end_sec>>
NULL;
END;
END DRAWINGOPERATION;