Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle error in procedure

Posted on 2014-04-07
7
Medium Priority
?
814 Views
Last Modified: 2014-04-07
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_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;  ------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.section_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.section_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.section_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
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983156
icolPREV := 0;  -----FIX HERE
0
 

Author Comment

by:jknj72
ID: 39983181
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
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 2000 total points
ID: 39983217
That's because the whole freaking procedure is full of errors, typos, bad syntax, etc, etc. Are you serious about this procedure being moved to production?

I'm not 100% sure it will compile, but try the below version and let me know.

Among the fixes are:

"--donothing" does nothing for sure -that's a comment, so you need to put at least a NULL there.

End your CASEs with "end case;" not just "end;"

What the heck is this --> FOR section_ordinal in cur?

And this --> cur.section_table_cell ?

 icolNEXT <> icoNEXT




CREATE OR REPLACE PROCEDURE 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;  ------ERROR was here
     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_column = 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_column = 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_column = 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:jknj72
ID: 39983309
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...
0
 

Author Closing Comment

by:jknj72
ID: 39983313
thanks
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983314
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.
0
 

Author Comment

by:jknj72
ID: 39983347
New question is ID:28406715

Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 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