This discussion is archived
9 Replies Latest reply: Oct 3, 2013 10:15 AM by ramya_162 RSS

Please help me to close the IF blocks.

ramya_162 Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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.
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points