Forum Stats

  • 3,759,174 Users
  • 2,251,510 Discussions
  • 7,870,525 Comments

Discussions

SQL Developer log has no source/message

User_K2AC3
User_K2AC3 Member Posts: 1 Red Ribbon
edited May 23, 2014 2:20AM in SQL Developer

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;
Tagged:

Answers

  • 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
    user8167598 Member Posts: 114

    Hi

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

  • Jim Smith
    Jim Smith Member Posts: 2,865 Silver Trophy

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

This discussion has been closed.