9 Replies Latest reply: Oct 3, 2013 12:15 PM by ramya_162 RSS

    Please help me to close the IF blocks.

    ramya_162

      HI EXperts,

       

       

      Please help me on closing the IF blocks properly in the below procedure.

       

       

      CREATE OR REPLACE PROCEDURE sums_proc

      (

                      Id INTEGER,

        mtrname VARCHAR2

                      mtrtype INTEGER,  

      )

      AS

      state  VARCHAR2(10);

      accesslevel INTEGER;

      asts NUMBER;

      mtrtypesql LONG;

      sumsql LONG;

      BEGIN 

      SELECT st_name INTO state FROM states_info WHERE st_name  = 'KARNATAKA';

      SELECT acc_level INTO accesslevel FROM level_table WHERE level_id  = 4;

      IF (accesslevel = 4) THEN 

      BEGIN 

      asts :=4;

      END; 

      ELSIF (accesslevel = 3)  THEN

      BEGIN 

      IF (OBJECT_ID('CANN_TABLE') IS NOT NULL)  THEN

      BEGIN 

      EXECUTE IMMEDIATE 'DROP TABLE CANN_TABLE'; 

      END;

      EXECUTE IMMEDIATE 'CREATE TABLE CANN_TABLE'; 

      END;

      ELSE

      BEGIN  

      IF OBJECT_ID('MANN_TABLE') IS NOT NULL  THEN

      BEGIN 

      EXECUTE IMMEDIATE 'DROP TABLE MANN_TABLE'; 

      END;

      EXECUTE IMMEDIATE 'CREATE TABLE MANN_TABLE';

      END; 

      IF (mtrname IS NULL OR mtrname = '')  THEN

      mtrname := ''; 

      ELSE 

      mtrname := 'DEFAULT';

      IF (mtrtype > 2) THEN

      mtrtypesql := ' AND (mtr = 1) ' ; 

      ELSE  

      mtrtypesql := ' AND (mtr = 2) '; 

      END IF;

      IF (mtrtype = 1) THEN 

      BEGIN 

      mtrtypesql := 'SELECT SUM(amount) as amount FROM sales_table WHERE sales_type=''SALES''||mtrtypesql'; 

      END; 

      ELSIF (mtrtype = 2)

      mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN''||mtrtypesql';  

      END; 

      IF (mtrtype = 4  OR mtrtype = 3) THEN

      BEGIN  

      mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN''||mtrtypesql';

      END; 

      ELSIF (mtrtype = 10 OR mtrtype = 15) THEN

      BEGIN    

      mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE revenue_type=''REVENUE''||mtrtypesql';

      END;        

      ELSE 

      BEGIN 

      mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE all_type=''ALL''||mtrtypesql';

      END;

      sumsql := ' SELECT SUM(amount) FROM salesum_table';

      EXECUTE IMMEDIATE mtrtypesql || ' UNION all ' ||sumsql ;

      IF OBJECT_ID('CANN_TABLE') IS NOT NULL THEN

      BEGIN 

      EXECUTE IMMEDIATE 'DROP TABLE CANN_TABLE'; 

      END;

      END IF; 

      IF OBJECT_ID('MANN_TABLE') IS NOT NULL  THEN

      BEGIN 

      EXECUTE IMMEDIATE  'DROP TABLE MANN_TABLE'; 

      END;

      END;

       

       

      Please help me.

       

       

      Thanks.

        • 1. Re: Please help me to close the IF blocks.
          Ramin Hashimzadeh

          I think first you must read PL/SQL Tutorial  then you can start writing PL/SQL Code

          • 2. Re: Please help me to close the IF blocks.
            Paul  Horth

            What a mess. Why are you wrapping nearly every statement in begin end blocks?

             

            Why are you (mis)using dynamic SQL?

             

            Why are you dropping and re-creating tables?

             

            Why are you using convoluted PL/SQL logic instead of doing it directly in SQL using decode or case?

             

            Why are you CODING IN UPPER CASE?

             

            All of these things are not best practice.

             

            Please explain what you are trying to do and we can help better.

            • 3. Re: Please help me to close the IF blocks.
              Mahir M. Quluzade

              Hi,

               

              I  checked your code there have more problem with IF/END IF condition operators.

              Additionally you  creating table without column, creating select statement dynamically but executing with

              EXECUTE command and not return rows or not inserting to any  table.

               

              I think you can use other methods for solution of your problem.

               

              I want paste edited your code. May  this return error. But I think my changes can help you.

               

              CREATE OR REPLACE PROCEDURE sums_proc(

                  Id      INTEGER,

                  mtrname VARCHAR2 mtrtype INTEGER,

                )

              AS

                state       VARCHAR2(10);

                accesslevel INTEGER;

                asts        NUMBER;

                mtrtypesql LONG;

                sumsql LONG;

              BEGIN

                SELECT st_name INTO state FROM states_info WHERE st_name = 'KARNATAKA';

                SELECT acc_level INTO accesslevel FROM level_table WHERE level_id = 4;

               

                IF (accesslevel = 4) THEN asts :=4;

               

                ELSIF (accesslevel = 3) THEN

                  IF (OBJECT_ID('CANN_TABLE') IS NOT NULL) THEN   EXECUTE IMMEDIATE 'DROP TABLE CANN_TABLE';

                   ELSE EXECUTE IMMEDIATE 'CREATE TABLE CANN_TABLE(SM NUMBER)';

                  END IF;

               

                ELSE

                 IF OBJECT_ID('MANN_TABLE') IS NOT NULL THEN   EXECUTE IMMEDIATE 'DROP TABLE MANN_TABLE';

                  ELSE  EXECUTE IMMEDIATE 'CREATE TABLE MANN_TABLE(SM NUMBER)';

                 END IF;

               

                 IF (mtrname IS NULL OR mtrname = '') THEN  mtrname := '';

                 ELSE

                    mtrname      := 'DEFAULT';

                   IF (mtrtype   > 2) THEN

                      mtrtypesql := ' AND (mtr = 1) ' ;

                    ELSE

                      mtrtypesql := ' AND (mtr = 2) ';

                   END IF;

                   IF (mtrtype = 1) THEN

                      mtrtypesql := 'SELECT SUM(amount) as amount FROM sales_table WHERE sales_type=''SALES'''||mtrtypesql;

                    ELSIF (mtrtype = 2)

                      mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN'''||mtrtypesql;

                    ELSIF (mtrtype = 4 OR mtrtype = 3) THEN

                        mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN'''||mtrtypesql;

                    ELSIF (mtrtype = 10 OR mtrtype = 15) THEN

                        mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE revenue_type=''REVENUE'''||mtrtypesql;

                    ELSE

                        mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE all_type=''ALL'''||mtrtypesql;

                    END IF;

               

                    sumsql := ' SELECT SUM(amount) FROM salesum_table';

                 

                    EXECUTE IMMEDIATE 'BEGIN INSERT INTO CANN_TABLE '||mtrtypesql || ' UNION all ' ||sumsql ||'; COMMIT; END;';

              END;

              /

               

              Begin

              Sums_Proc;

              End;

               

              Select *  from CANN_TABLE ;

               

              Regards

              Mahir M. Quluzade

               

               

              • 4. Re: Please help me to close the IF blocks.
                Chris Hunt

                Please help me on closing the IF blocks properly

                 

                Sure.

                 

                First, get into the habit of indenting your code. Every IF statement must have an END IF, and may have an ELSIF and/or ELSE statement too. If you indent the commands between these statements by a fixed amout of whitespace, it makes it much easier to see what's going on, and to spot where you have missed an END or END IF:

                 

                IF condition THEN

                   statement;

                   statement;

                   statement;

                ELSIF condition THEN

                   statement;

                   statement;

                   statement;

                ELSE

                   statement;

                   statement;

                   statement;

                END IF;

                 

                Second, while anonymous BEGIN/END blocks have their uses, you don't need to wrap every PL/SQL statement in its own block. Removing all the excess BEGINs and ENDs from your code won't make any difference to Oracle, but will improve the signal-to-noise ratio for humans trying to understand your code (including you).

                • 5. Re: Please help me to close the IF blocks.
                  ramya_162

                  Hi All,

                   

                  Thanks you all for your suggestions.

                  I have one doubt could any body please clarify.

                  Will the below query send data to the .net?

                  EXECUTE IMMEDIATE 'BEGIN INSERT INTO CANN_TABLE '||mtrtypesql || ' UNION all ' ||sumsql ||'; COMMIT; END;';


                  else we have to use ref_cursor?.


                  Please help me.


                  Thanks.

                  • 6. Re: Please help me to close the IF blocks.
                    Paul  Horth

                    ramya_162 wrote:

                     

                    Hi All,

                     

                    Thanks you all for your suggestions.

                    I have one doubt could any body please clarify.

                    Will the below query send data to the .net?

                    EXECUTE IMMEDIATE 'BEGIN INSERT INTO CANN_TABLE '||mtrtypesql || ' UNION all ' ||sumsql ||'; COMMIT; END;';


                    else we have to use ref_cursor?.


                    Please help me.


                    Thanks.

                    Unfortunately you don't seemed to have listened to any of the advice.

                     

                    You need to completely scrap that code and start again without using dynamic SQL (execute immediate) or dropping and recreating tables.

                    • 7. Re: Please help me to close the IF blocks.
                      marcusafs

                      Check the comma's in your paramter list.  It is a good practice to enclose all your SQL statemetnes in begin/exception/end blocks for ease of troubleshooting.  Use SQL Developer which will format your code if not badly formed.  Break the task down into smaller chunks, completing on IF/END IF before starting another.  Take small bites.

                       

                      PL/SQL Articles in Oracle Magazine: Oracle Magazine Steven Feuerstein Article Index

                       

                      Marcus Bacon

                      • 9. Re: Please help me to close the IF blocks.
                        ramya_162

                        Hi,

                         

                         

                        Thanks for your help.

                         

                         

                        Please help on the error.

                         

                         

                        ORA-24338: statement handle not executed

                         

                         

                        CREATE OR REPLACE PROCEDURE sums_proc(

                            p_Id      INTEGER,

                            mtrname VARCHAR2 mtrtype INTEGER,

                            P_out OUT SYS_REFCURSOR

                          )

                        AS

                          state       VARCHAR2(10);

                          accesslevel INTEGER;

                          asts        NUMBER;

                          countval   NUMBER :=0;

                          mtrtypesql LONG;

                          sumsql LONG;

                          v_query LONG;

                         

                         

                          BEGIN

                          SELECT st_name INTO state FROM states_info WHERE st_name = 'KARNATAKA';

                         

                         

                          SELECT COUNT(*) INTO countval FROM  level_table WHERE level_id = p_id;

                        IF countval = 0 THEN

                        RETURN;

                            ELSE

                                SELECT access_id INTO accesslevel  level_table WHERE level_id = p_id;

                            IF (NVL(accesslevel, 0) = 0)  THEN

                            RETURN;

                            END IF;

                        END IF;

                         

                          IF (accesslevel = 4) THEN

                          asts :=4;

                         

                          ELSIF (accesslevel = 3) THEN

                            IF (OBJECT_ID('CANN_TABLE') IS NOT NULL) THEN   EXECUTE IMMEDIATE 'DROP TABLE CANN_TABLE';

                             ELSE EXECUTE IMMEDIATE 'CREATE TABLE CANN_TABLE(SM NUMBER)';

                            END IF;

                         

                          ELSE

                           IF OBJECT_ID('MANN_TABLE') IS NOT NULL THEN   EXECUTE IMMEDIATE 'DROP TABLE MANN_TABLE';

                            ELSE  EXECUTE IMMEDIATE 'CREATE TABLE MANN_TABLE(SM NUMBER)';

                           END IF;

                         

                           IF (mtrname IS NULL OR mtrname = '') THEN  mtrname := '';

                           ELSE

                              mtrname      := 'DEFAULT';

                             IF (mtrtype   > 2) THEN

                                mtrtypesql := ' AND (mtr = 1) ' ;

                              ELSE

                                mtrtypesql := ' AND (mtr = 2) ';

                             END IF;

                             IF (mtrtype = 1) THEN

                                mtrtypesql := 'SELECT SUM(amount) as amount FROM sales_table WHERE sales_type=''SALES'''||mtrtypesql;

                              ELSIF (mtrtype = 2)

                                mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN'''||mtrtypesql;

                              ELSIF (mtrtype = 4 OR mtrtype = 3) THEN

                                  mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN'''||mtrtypesql;

                              ELSIF (mtrtype = 10 OR mtrtype = 15) THEN

                                  mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE revenue_type=''REVENUE'''||mtrtypesql;

                              ELSE

                                  mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE all_type=''ALL'''||mtrtypesql;

                              END IF;

                         

                              sumsql := ' SELECT SUM(amount) FROM salesum_table';

                          

                              v_query :=||mtrtypesql || ' UNION all ' ||sumsql ||';

                             

                              OPEN P_out FOR v_query;

                         

                         

                              COMMIT;

                            END;

                        /

                         

                         

                         

                         

                         

                         

                        My requirement is

                         

                         

                        IF accesslevel = 0 or IF accesslevel is not existed the program should come out.

                         

                         

                        SO that I have included the below code

                         

                         

                        SELECT COUNT(*) INTO countval FROM  level_table WHERE level_id = p_id;

                        IF countval = 0 THEN

                        RETURN;

                            ELSE

                                SELECT access_id INTO accesslevel  level_table WHERE level_id = p_id;

                            IF (NVL(accesslevel, 0) = 0)  THEN

                            RETURN;

                            END IF;

                        END IF;

                         

                         

                        I want to send the result set to front so that I have replaced the below statement with

                         

                         

                        EXECUTE IMMEDIATE 'BEGIN INSERT INTO CANN_TABLE '||mtrtypesql || ' UNION all ' ||sumsql ||';

                         

                         

                        OPEN P_out FOR v_query;

                         

                         

                        If I get accesslevel as 0 I am getting error when executing thic procedure.

                         

                         

                        ORA-24338: statement handle not executed

                         

                         

                        Please help me.

                         

                         

                        Thanks.