jknj72
asked on
Oracle procedure enhancement
Im looking to rewrite this procedure. If anyone has a btter way of me doing this Id appreciate it...
Thanks
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;
FOR rec in cur
LOOP
Case When rec.section_table_cell = 0 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.sectio n_ordinal- 1;
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
When rec.section_table_cell = 1 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
SELECT section_table_cell into icolNEXT from omni_ent_page_content where page_id=iPAGEID and section_ordinal = rec.section_ordinal + 1;
if icolNEXT is NULL then
NULL; --donothing
else
NULL; --case when icolNEXT = 0 then
end if;
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
When rec.section_table_cell = 2 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.sectio n_ordinal- 1;
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
When rec.section_table_cell = 3 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.sectio n_ordinal- 1;
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
End case;
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 ;
/
Thanks
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;
FOR rec in cur
LOOP
Case When rec.section_table_cell = 0 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.sectio
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
When rec.section_table_cell = 1 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
SELECT section_table_cell into icolNEXT from omni_ent_page_content where page_id=iPAGEID and section_ordinal = rec.section_ordinal + 1;
if icolNEXT is NULL then
NULL; --donothing
else
NULL; --case when icolNEXT = 0 then
end if;
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
When rec.section_table_cell = 2 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.sectio
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
When rec.section_table_cell = 3 then
Case When icolPREV = 0 then
NULL; --donothing
when icolPrev = 1 then
UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.sectio
when icolPREV = 2 then
NULL; --donothing
when icolPREV = 3 then
NULL; --donothing
end case;
End case;
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
/
How many times are you calling this procedure OR How may rows are you going to run above procedure for?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I just found out that the proc is not complete(pretty obvious). The purpose of the proc is to set the section_ordinal position so when the app loads it will know the position to put the 'objects'. This is called when these objects are moved to the right or to the left on the page. so if we had 3 objects(image, links, etc...) on a page on the same row and I clicked to move the 2nd object to the left which means now it would be the 1st object and the 1st object becomes the 2nd object in the row of objects...
Thats the objective...
I hope it makes sense. Let me know if you have any questions.
Thanks all!!!
FYI, just to explain a little more...The app is letting the users create their own web pages. They can add objects(images, links, paragraphs, etc...) and we save the position of these objects so we know how to load the page with the positions where they were saved.
Thats the objective...
I hope it makes sense. Let me know if you have any questions.
Thanks all!!!
FYI, just to explain a little more...The app is letting the users create their own web pages. They can add objects(images, links, paragraphs, etc...) and we save the position of these objects so we know how to load the page with the positions where they were saved.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually did use the Merge statement for a similar procedure and will see what I can do.
If you can dummy up some sample data and expected results, we can probably help.
ASKER
splitting points..... thanks for the help
ASKER
thanks for the help