Link to home
Start Free TrialLog in
Avatar of jknj72
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_REALIGN
(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.section_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.section_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.section_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;
/
Avatar of Devinder Singh Virdi
Devinder Singh Virdi
Flag of United States of America image

How many times are you calling this procedure OR How may rows are you going to run above procedure for?
SOLUTION
Avatar of Wasim Akram Shaik
Wasim Akram Shaik
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72
jknj72

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

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.
Avatar of jknj72

ASKER

splitting points..... thanks for the help
Avatar of jknj72

ASKER

thanks for the help