CODEWITHSUNDEEP
oracle-databasecursor
Davees John Baclay
Davees John Baclay

Reputation: 89

How to execute Stored Procedure inside Stored Procedure with Cursor in Oracle

I am new in Oracle, In SQL Server I can easily execute a stored procedure inside storedprocedure even using a cursor.

now I can't figure it out in Oracle here is my code below.

CREATE OR REPLACE PROCEDURE ZSP_INSMASTERDATASTM
AS
   l_total        INTEGER := 10000;

   CURSOR c1
   IS
      SELECT DISTINCT PRODFROMTO FROM DJ_P9945LINKS;

   l_PRODFROMTO   c1%ROWTYPE;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 INTO l_PRODFROMTO;

      EXIT WHEN c1%NOTFOUND;

      EXECUTE ZSP_GETMASTERDATASTM (l_PRODFROMTO);

      EXIT WHEN l_total <= 0;
   END LOOP;

   CLOSE c1;
END ZSP_INSMASTERDATASTM;

i got error on execute ZSP_GETMASTERDATASTM (l_PRODFROMTO);

Upvotes: 2

Views: 2018

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

Just remove EXECUTE. However, note that your loop will NEVER exit because L_TOTAL is never going to be less than zero - you should fix that.

Otherwise, consider using cursor FOR loop as it is simpler to maintain - you don't have to declare a cursor variable, open cursor, fetch, take care about exiting the loop, close the cursor. Have a look at this example:

CREATE OR REPLACE PROCEDURE zsp_insmasterdatastm
AS
BEGIN
   FOR cur_r IN (SELECT DISTINCT prodfromto FROM dj_p9945links)
   LOOP
      zsp_getmasterdatastm (cur_r.prodfromto);
   END LOOP;
END;

Quite simpler, isn't it?

Upvotes: 4

Related Questions