SQL Developer log has no source/message

User_K2AC3

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;

Unknown

Oracle SQL server (11g).

Just to avoid any confusion SQL server is a Microsoft product.

all of the functionality of this works,

You might want to add some validation of the parameters, some exception handling and a commit statement after a delete.

when running an UPDATE or DELETE I receive a log from SQL Developer that has no source or message.

What version of sql developer are you using?

What 'log' are you talking about and/or expecting?

Tell us what you are doing in sql developer that would create a log.

user8167598

Hi

While I don't believe this is the problem -- avoid using GOTO at all costs ! Use nested if then else.....

Jim Smith

I see nothing in that piece of code which would produce any output.  What are you expecting?