jknj72
asked on
Oracle error in procedure
I am cleaning up procedures before moving database to prod. One of the procs had an error and I thought I would post it while I try and figure it out as well...Here is the proc and I attached a picture so you can see the error too....
CREATE OR REPLACE PROCEDURE ANET.SP_OMNI_ENT_COLUMN_RE ALIGN
(iPAGEID IN NUMBER)
IS
BEGIN
DECLARE
icolPREV NUMBER;
icolNEXT NUMBER;
cursor cur is
select section_ordinal, section_table_cell
from omni_ent_page_content where page_id=iPAGEID
order by section_ordinal asc, section_table_cell asc;
BEGIN
icolPREV = 0; ------ERROR HERE
FOR section_ordinal in cur
LOOP
Case When cur.section_table_cell = 0 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_column = 0 where page_id=iPAGEID and section_ordinal=cur.sectio n_ordinal- 1;
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
When cur.section_table_cell = 1 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
SELECT section_table_cell into icolNEXT from omni_ent_page_content where page_id=iPAGEID and section_ordinal = cur.section_ordinal + 1;
if icoNEXT is NULL then
--donothing
else
--case when icoNEXT = 0 then
end if;
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
When cur.section_table_cell = 2 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_column = 0 where page_id=iPAGEID and section_ordinal=cur.sectio n_ordinal- 1;
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
When cur.section_table_cell = 3 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_column = 0 where page_id=iPAGEID and section_ordinal=cur.sectio n_ordinal- 1;
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
End;
END LOOP;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SP_OMNI_ENT_COLUMN_REALIGN ;
Cursor-Error.jpg
CREATE OR REPLACE PROCEDURE ANET.SP_OMNI_ENT_COLUMN_RE
(iPAGEID IN NUMBER)
IS
BEGIN
DECLARE
icolPREV NUMBER;
icolNEXT NUMBER;
cursor cur is
select section_ordinal, section_table_cell
from omni_ent_page_content where page_id=iPAGEID
order by section_ordinal asc, section_table_cell asc;
BEGIN
icolPREV = 0; ------ERROR HERE
FOR section_ordinal in cur
LOOP
Case When cur.section_table_cell = 0 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_column = 0 where page_id=iPAGEID and section_ordinal=cur.sectio
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
When cur.section_table_cell = 1 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
SELECT section_table_cell into icolNEXT from omni_ent_page_content where page_id=iPAGEID and section_ordinal = cur.section_ordinal + 1;
if icoNEXT is NULL then
--donothing
else
--case when icoNEXT = 0 then
end if;
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
When cur.section_table_cell = 2 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_column = 0 where page_id=iPAGEID and section_ordinal=cur.sectio
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
When cur.section_table_cell = 3 then
Case When icolPREV = 0 then
--donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_column = 0 where page_id=iPAGEID and section_ordinal=cur.sectio
when icolPREV = 2 then
--donothing
when icolPREV = 3 then
--donothing
end;
End;
END LOOP;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SP_OMNI_ENT_COLUMN_REALIGN
Cursor-Error.jpg
icolPREV := 0; -----FIX HERE
ASKER
I actually tried that prior to me asking the question. The error moves to the first CASE statement within the first CASE statement. I have attached a pic so you can see the error where the red is underlined...
Thanks
Cursor-Error.jpg
Thanks
Cursor-Error.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Welcome to my nightmare.... I didnt write this but I have to fix them before we go to Prod. It worked. Thanks for the help
Also, If you have a better way to do this Id be happy to post another question to make this better. The Cases that dont have anything in them will have logic before going to Prod so they are just reserved for the logic for now.
Thanks for your help and let me know if you want me to post the question to make this better...
Also, If you have a better way to do this Id be happy to post another question to make this better. The Cases that dont have anything in them will have logic before going to Prod so they are just reserved for the logic for now.
Thanks for your help and let me know if you want me to post the question to make this better...
ASKER
thanks
lol. I completely understand you.
Sure, you can post another question, but it may not be me answering it -there are too many talented developers here.
Sure, you can post another question, but it may not be me answering it -there are too many talented developers here.
ASKER
New question is ID:28406715
Thanks
Thanks