Solved

Oracle procedure enhancement

Posted on 2014-04-07
9
774 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
ID: 39983370
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
ID: 39983478
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
ID: 39983548
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jknj72
ID: 39983657
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 39983906
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
ID: 39983993
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)
ID: 39984443
If you can dummy up some sample data and expected results, we can probably help.
0
 

Author Comment

by:jknj72
ID: 39994109
splitting points..... thanks for the help
0
 

Author Closing Comment

by:jknj72
ID: 39994110
thanks for the help
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

770 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