Topic
  • 1 reply
  • Latest Post - ‏2014-02-03T15:15:07Z by nivanov1
robbiii
robbiii
2 Posts

Pinned topic Problems to execute StoredProcedure within an StoredProcedure

‏2014-01-31T10:25:30Z |

Hi

I use a SP wich modifies outages in different tables.
This SP named S1 works fine without the CALL S2 Statement.

The SP S2 works well too, if I execute it separate with the needed Parameters.

Now I want to call the SP S2 within the SP S1 and this doesn't work.
The first modification from SP S1 were made and then the SP S2 will be called.
The modifications from SP S2 works fine but after doing the Job it seems that the SP S1 could not finish ist work.

I get the following error message

SQL0501N  Der in der Anweisung FETCH oder CLOSE angegebene Cursor wurde noch
nicht geöffnet.  SQLSTATE=24501

It Looks like the Cursors are closed and if the SP S2 Returns back to SP S1 it could not continue.
Could you please help me to let this work?

 

Thanks
robbiii

Here are my 2 SPs

S1:

CREATE PROCEDURE S1 (
 IN p_solutionname   VARCHAR(152),
 IN p_jahrmonat    CHAR(7),
 IN p_lastmodtime   VARCHAR(20),
 IN p_kommentar    VARCHAR(1024),
 IN p_userid    VARCHAR(20),
 IN p_username   VARCHAR(100),
 IN p_kommentar_datum VARCHAR(30))

 RESULT SETS 1
 LANGUAGE SQL
 BEGIN
  
 DECLARE SQLSTATE   CHAR(5);
 DECLARE v_solutionname VARCHAR(152);
 DECLARE v_jahrmonat  CHAR(7);
 DECLARE v_lastmodtime  VARCHAR(20);
 DECLARE v_anzahl_sla  INTEGER;
 DECLARE v_outage_state CHAR(1);
 DECLARE at_end    SMALLINT DEFAULT 0;
 DECLARE v_jahrwoche  CHAR(7);
 DECLARE v_jahrquartal CHAR(6);

 DECLARE not_found
  CONDITION FOR SQLSTATE '02000';

 -- Query für alle Ausfälle in Monatstabelle
 DECLARE CUR_MON1 CURSOR FOR
  SELECT SOLUTIONNAME, JAHRMONAT, LASTMODTIME, ANZAHL_SLA, OUTAGE_STATE
  FROM A_SOLUTION_UNAVAILABLE_M
  WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime;

 -- Query um Cognos geänderte Daten wieder retour zu geben
 DECLARE CUR_MON1_RES CURSOR WITH RETURN FOR
  SELECT *
  FROM A_SOLUTION_UNAVAILABLE_M
  WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime AND JAHRMONAT = p_jahrmonat;
  
 -- Query für alle Ausfälle in Wochentabelle
 DECLARE CUR_WEEK1 CURSOR FOR
  SELECT SOLUTIONNAME, JAHRWOCHE, LASTMODTIME, ANZAHL_SLA, OUTAGE_STATE
  FROM A_SOLUTION_UNAVAILABLE_W
  WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime;

 -- Query für alle Ausfälle in Quartalstabelle
 DECLARE CUR_QUAR1 CURSOR FOR
  SELECT SOLUTIONNAME, JAHRQUARTAL, LASTMODTIME, ANZAHL_SLA, OUTAGE_STATE
  FROM A_SOLUTION_UNAVAILABLE_Q
  WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime;


 
 --------------------------------------------------------------------------------------------------------
 --
 --   MONAT
 --
 --------------------------------------------------------------------------------------------------------
 DECLARE CONTINUE HANDLER FOR not_found
  SET at_end = 1;

 OPEN CUR_MON1;
 LOOP_CUR_MON1:
 LOOP
  FETCH CUR_MON1 INTO
   v_solutionname, v_jahrmonat, v_lastmodtime, v_anzahl_sla, v_outage_state;

  -- exit Loop wenn nichts mehr da ist
  IF at_end <> 0 THEN
   LEAVE LOOP_CUR_MON1;
  End IF;

  -- exit wenn LASTMODTIME 0 ist
  IF v_lastmodtime = '0' THEN
   LEAVE LOOP_CUR_MON1;
  End IF;

  -- exit wenn Ausfall schon mal gelöscht wurde
  IF v_outage_state = '2' THEN
   LEAVE LOOP_CUR_MON1;
  End IF;

  UPDATE A_SOLUTION_UNAVAILABLE_M
   SET KOMMENTAR = p_kommentar, USERID = p_userid, USERNAME = p_username, KOMMENTAR_DATUM = p_kommentar_datum, OUTAGE_STATE = '2'
   WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime AND JAHRMONAT = v_jahrmonat;
  
  -- call der externen SP
  CALL S2(p_solutionname,v_jahrmonat,v_anzahl_sla);
  
 END LOOP LOOP_CUR_MON1;
 CLOSE CUR_MON1;
 COMMIT;
 
 --------------------------------------------------------------------------------------------------------
 --
 --   WOCHE
 --
 --------------------------------------------------------------------------------------------------------

 SET at_end = 0;  

 OPEN CUR_WEEK1;
 LOOP_CUR_WEEK1:
 LOOP
  FETCH CUR_WEEK1 INTO
   v_solutionname, v_jahrwoche, v_lastmodtime, v_anzahl_sla, v_outage_state;

  IF at_end <> 0 THEN
   LEAVE LOOP_CUR_WEEK1;
  End IF;

  IF v_lastmodtime = '0' THEN
   LEAVE LOOP_CUR_WEEK1;
  End IF;

  IF v_outage_state = '2' THEN
   LEAVE LOOP_CUR_WEEK1;
  End IF;

  UPDATE A_SOLUTION_UNAVAILABLE_W
   SET KOMMENTAR = p_kommentar, USERID = p_userid, USERNAME = p_username, KOMMENTAR_DATUM = p_kommentar_datum, OUTAGE_STATE = '2'
   WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime;

 END LOOP LOOP_CUR_WEEK1;
 CLOSE CUR_WEEK1;
 COMMIT;
 
 --------------------------------------------------------------------------------------------------------
 --
 --   Quartal
 --
 --------------------------------------------------------------------------------------------------------

 SET at_end = 0;  

 OPEN CUR_QUAR1;
 LOOP_CUR_QUAR1:
 LOOP
  FETCH CUR_QUAR1 INTO
   v_solutionname, v_jahrquartal, v_lastmodtime, v_anzahl_sla, v_outage_state;

  IF at_end <> 0 THEN
   LEAVE LOOP_CUR_QUAR1;
  End IF;

  IF v_lastmodtime = '0' THEN
   LEAVE LOOP_CUR_QUAR1;
  End IF;

  IF v_outage_state = '2' THEN
   LEAVE LOOP_CUR_QUAR1;
  End IF;

  UPDATE A_SOLUTION_UNAVAILABLE_Q
   SET KOMMENTAR = p_kommentar, USERID = p_userid, USERNAME = p_username, KOMMENTAR_DATUM = p_kommentar_datum, OUTAGE_STATE = '2'
   WHERE SOLUTIONNAME = p_solutionname AND LASTMODTIME = p_lastmodtime;

 END LOOP LOOP_CUR_QUAR1;
 CLOSE CUR_QUAR1;
 COMMIT;
 
 
 OPEN CUR_MON1_RES;
END @

 


 S2:

CREATE PROCEDURE S2 (
 IN p_solutionname   VARCHAR(152),
 IN p_jahrmonat    CHAR(7),
 IN p_anzahl_sla   INTEGER)

 LANGUAGE SQL
 BEGIN
  
 DECLARE SQLSTATE      CHAR(5);
 DECLARE v_solutionname    VARCHAR(152);
 DECLARE v_jahrmonat     CHAR(7);
 DECLARE v_man_deleted_outages   INTEGER;
 DECLARE v_man_deleted_outages_min  INTEGER;
 DECLARE at_end       SMALLINT DEFAULT 0;

 DECLARE not_found
  CONDITION FOR SQLSTATE '02000';

 
 DECLARE CUR_MON1_UPDATE CURSOR WITH RETURN TO CALLER FOR
  SELECT SOLUTIONNAME, JAHRMONAT, MAN_DELETED_OUTAGES, MAN_DELETED_OUTAGES_MIN
  FROM A_SOLUTION_OVERVIEW_M
  WHERE SOLUTIONNAME = p_solutionname AND JAHRMONAT = p_jahrmonat;
  
  
 DECLARE CONTINUE HANDLER FOR not_found
  SET at_end = 1;

 OPEN CUR_MON1_UPDATE;
  FETCH CUR_MON1_UPDATE INTO
   v_solutionname, v_jahrmonat, v_man_deleted_outages, v_man_deleted_outages_min;

  SET v_man_deleted_outages  = v_man_deleted_outages + 1;
  SET v_man_deleted_outages_min = v_man_deleted_outages_min + p_anzahl_sla;
  
  UPDATE A_SOLUTION_OVERVIEW_M
  SET MAN_EDITED = '1', MAN_DELETED_OUTAGES = v_man_deleted_outages, MAN_DELETED_OUTAGES_MIN = v_man_deleted_outages_min
  WHERE SOLUTIONNAME = p_solutionname AND JAHRMONAT = p_jahrmonat;

 CLOSE CUR_MON1_UPDATE;
 COMMIT;
 
END @

 

  • nivanov1
    nivanov1
    231 Posts

    Re: Problems to execute StoredProcedure within an StoredProcedure

    ‏2014-02-03T15:15:07Z  

    The COMMIT statement in S2 will close all session cursors, including those in the caller procedure. Generally speaking you should not issue commits from within stored procedures, precisely for this reason -- it affects the behaviour of programs using them. If you insist on committing in the procedure, try opening cursors in S1 using the WITH HOLD option.