Solved

Oracle procedure enhancement

Posted on 2014-04-07
9
760 Views
Last Modified: 2014-04-11
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;
/
0
Comment
Question by:jknj72
9 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
How many times are you calling this procedure OR How may rows are you going to run above procedure for?
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 167 total points
Comment Utility
i dont see how icolprev and icolnext are getting values, more over you are doing nothing in most of the cases, so if you combine those case conditions you can get rid of nested case statements



CREATE PROCEDURE sp_omni_ent_column_realign (ipageid IN NUMBER)
IS
   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
   BEGIN
      icolprev := 0;

      FOR rec IN cur
      LOOP
         CASE
            WHEN rec.section_table_cell = 0 AND 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 rec.section_table_cell = 1 AND 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;
            WHEN rec.section_table_cell = 2 AND 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 rec.section_table_cell = 3 AND icolprev = 1
            THEN
               UPDATE omni_ent_page_content
                  SET section_table_cell = 0
                WHERE page_id = ipageid
                  AND section_ordinal = rec.section_ordinal - 1;
         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;
/
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 166 total points
Comment Utility
I agree, try to remove as many nested CASE statements as possible. Normally, the best way to tune your code is to change the FOR LOOP to a FORALL, but in your case, I don't think is possible, so you'll have to stay with the row by row approach.

Something like this:

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;
type nn is table of cur%rowtype;
l_nn nn;

BEGIN
   BEGIN
      icolprev := 0;

fetch cur bulk collect into l_nn;
forall i in l_nn.first..l_nn.last

    UPDATE omni_ent_page_content
                  SET section_table_cell = 0
                WHERE page_id = ipageid
                  AND section_ordinal = l_nn(i).section_ordinal - 1;
0
 

Author Comment

by:jknj72
Comment Utility
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
Any reason you left the MERGE examples from your previous questions:
http://www.experts-exchange.com/Database/Oracle/Q_27869212.html
http://www.experts-exchange.com/Database/Oracle/Q_27865679.html

I still think this can all be done in a single statement.

As I asked for in those questions and never received:
Need some sample data and expected results or some examples where the code no longer works and an explanation of why.
0
 

Author Comment

by:jknj72
Comment Utility
I actually did use the Merge statement for a similar procedure and will see what I can do.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
If you can dummy up some sample data and expected results, we can probably help.
0
 

Author Comment

by:jknj72
Comment Utility
splitting points..... thanks for the help
0
 

Author Closing Comment

by:jknj72
Comment Utility
thanks for the help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now