11 Replies Latest reply on Jun 6, 2012 12:07 PM by BluShadow

    missing expression error  during EXECUTE IMMEDIATE

    user468
      HI all
      CREATE TABLE  "T_LIDER" 
         (     "LIDER" NUMBER(5,0), 
           "TRR" NUMBER(8,0), 
                      "SUG_I"  NUMBER(2,0),
            "LIDER_TAY" NUMBER(2),
            CONSTRAINT "T_LIDER_PK" PRIMARY KEY ("LIDER", "SUG_I") ENABLE)
      CREATE TABLE B_LIDER 
        (     "LIDER" NUMBER(5,0), 
           "TRR" NUMBER(8,0), 
                      "SUG_I"  NUMBER(2,0),
            "LIDER_BAY" NUMBER(2),
            CONSTRAINT "B_LIDER_PK" PRIMARY KEY ("LIDER", "SUG_I") ENABLE)
      CREATE SYNONYM LIDERS FOR T_LIDER ;
      UPDATE IND_TAB SET IND = 'TAY';
      commit;
      create or replace function return_lider
      (ind_in char,lider_in number) return number  is  
      l_lider number(5);    
      l_lider_in varchar2(10);  
      begin 
      L_LIDER_IN :='lideR_'||ind_in;
      EXECUTE IMMEDIATE 'SELECT '||L_LIDER_IN ||' '||'INTO'||' '||l_lider ||' '||'FROM T_HSCHOZ WHERE LIDER = '||lider_in;
      return L_LIDER ;
      end return_lider;
      
      sql > 
      SELECT LIDER,RETURN_LIDER((SELECT ind from ind_tab),LIDER)  FROM liders ;
       ORA-00936: missing expression , 
      i've tried to simplify the problem and i saw that the function returns this error
      what seems to be the problem ?
      Thanks In Advanced
      Naama
        • 1. Re: missing expression error  during EXECUTE IMMEDIATE
          908002
          into should not be part of quoted string... should be after query statement

          EXECUTE IMMEDIATE 'SELECT '||L_LIDER_IN || 'FROM T_HSCHOZ WHERE LIDER = '||lider_in into l_lider;
          • 2. Re: missing expression error  during EXECUTE IMMEDIATE
            ShankarViji
            Hi Naama,

            The into clause cannot be used like that..

            Just see this..
              1  CREATE OR REPLACE FUNCTION return_count
              2     RETURN NUMBER
              3  IS
              4     l_lider      NUMBER (5);
              5     l_lider_in   VARCHAR2 (10);
              6  BEGIN
              7     EXECUTE IMMEDIATE    'SELECT count(*) from emp' into l_lider;
              8     RETURN l_lider;
              9 END return_count;
            SQL> /
            Thanks,
            Shankar
            • 3. Re: missing expression error  during EXECUTE IMMEDIATE
              user468
              HI
              in the case of my query the expression that you've wrote is wrong .
              i have a where clause , in the way that i've wrote the query the into should be right there ,
              your expression is right to this type of query :
              begin 
              stmt : 'select * from liders where lider  = :lider'; 
              execute immediate stmt into liderec using lider;
              let's try what you've wrote : 
              EXECUTE IMMEDIATE 'SELECT '||L_LIDER_IN || 'FROM LIDERS WHERE LIDER = '||lider_in into l_lider;
              we accept the error : 
              ORA-00923: FROM keyword not found where expected  
              please noticed that those are two different things 
              Naama

              Edited by: naama on Jun 6, 2012 3:35 AM

              Edited by: naama on Jun 6, 2012 3:42 AM
              • 4. Re: missing expression error  during EXECUTE IMMEDIATE
                Prabodh
                Hi,

                Your error message says
                >
                ORA-00936: missing expression ,
                >

                Note the comma at the end.
                The cause appears to be in this
                >
                (SELECT ind from ind_tab) <b><font color="red">,</font></b> LIDER
                >

                The last word, LIDER is expected to be an ALIAS for the column so should not have that comma (highlighted in red, if you can see it).

                Problem appears to be in select you use to test !

                Regards,
                • 5. Re: missing expression error  during EXECUTE IMMEDIATE
                  user468
                  HI
                  Please look at my first post :
                  sql > 
                  SELECT LIDER,RETURN_LIDER((SELECT ind from ind_tab),LIDER)  FROM liders ;
                  as you can see there is a comma 
                  Naama
                  • 6. Re: missing expression error  during EXECUTE IMMEDIATE
                    BluShadow
                    What's the real problem you are trying to solve with this code?

                    Using dynamic SQL simply to access a couple of different columns based on a parameter just seems completely wrong.

                    What's wrong with having optional SQL executed in the code?
                    create or replace function return_lider (p_ind in varchar2, p_lider in number) return number is
                      l_lider number(5);    
                    begin 
                      if p_ind = 'TAY' then
                        select lider_tay into l_lider from t_lider where lider = p_lider;
                      else
                        select lider_bay into l_lider from b_lider where lider = p_lider;
                      end if;
                      return l_lider;
                    end return_lider;
                    99.9% of the time, people use dynamic SQL statements where they are not really needed. They they try and justify it as somehow making their code "flexible" or "generic", but that just shows that either a) their database design is flawed or b) they don't understand PL/SQL and SQL coding.
                    • 7. Re: missing expression error  during EXECUTE IMMEDIATE
                      user468
                      HI
                      I Can;t be more agree with you that using dynamic sql is not the best solution , but there are few cases that it is a good choice. in my query this is not the only column that i selected there are few columns ,(i just didn't write them
                      down in the query) , now i have something like 4 tables (i've posted 2 of them ) as i posted in the first thread and in the future there are going to be more , so doing lots of "if" seems really bad idea , every time i'll need to hard coded them which i want to prevent form it .
                      So i will be glad to solve the missing expression problem , after all it is an error message regardless the discussion whether it's the right thing to do or not .
                      Naama

                      Edited by: naama on Jun 6, 2012 4:23 AM
                      • 8. Re: missing expression error  during EXECUTE IMMEDIATE
                        Prabodh
                        What I am trying to suggest here that perhaps the way your are running your test is causing the error you see. Passing the result of a query as parameter to function (I have not yet used this way) may be causing a problem
                        To eliminate that possibility you could try with PL/SQL first.
                        Something like.
                        DECLARE
                         V_TMP NUMBER;
                         V_RESULT NUMBER;
                        BEGIN
                          SELECT ind INTO V_TMP from ind_tab;
                          SELECT RETURN_LIDER(V_RESULT, LIDER) INTO V_RESULT FROM liders ;
                          --SELECT LIDER,RETURN_LIDER((SELECT ind from ind_tab),LIDER)  FROM liders ;
                          DBMS_OUTPUT.PUT_LINE('RESULT='||V_RESULT);
                        END;
                        Regards,

                        Edited by: Prabodh on Jun 6, 2012 5:09 PM

                        Edited by: Prabodh on Jun 6, 2012 5:10 PM
                        • 9. Re: missing expression error  during EXECUTE IMMEDIATE
                          user468
                          HI '
                          why is that ? the function gets two parameters , the indication and the lider number .
                          Naama
                          • 10. Re: missing expression error  during EXECUTE IMMEDIATE
                            jeneesh
                            You cannot use select statemnts as an argument to a function.
                            SQL> select length(select 'x' from dual) l;
                            select length(select 'x' from dual) l
                                          *
                            ERROR at line 1:
                            ORA-00936: missing expression
                            Either use PL/SQL or use as below
                            SELECT a.LIDER,RETURN_LIDER(b.ind,a.LIDER)  
                            FROM liders a,ind_tab b
                            where a.joining_column = b.joining_column;
                            • 11. Re: missing expression error  during EXECUTE IMMEDIATE
                              BluShadow
                              naama wrote:
                              HI
                              I Can;t be more agree with you that using dynamic sql is not the best solution , but there are few cases that it is a good choice. in my query this is not the only column that i selected there are few columns ,(i just didn't write them
                              down in the query) , now i have something like 4 tables (i've posted 2 of them ) as i posted in the first thread and in the future there are going to be more , so doing lots of "if" seems really bad idea , every time i'll need to hard coded them which i want to prevent form it .
                              So i will be glad to solve the missing expression problem , after all it is an error message regardless the discussion whether it's the right thing to do or not .
                              Naama
                              My point is proven. If you have 4 tables then you should have 4 seperate procedures as those 4 tables should each have their own purpose if the database is designed correctly.
                              Or perhaps your database is designed poorly and you have similar data split across 4 tables unnecessarily?

                              Lots of "if" statements, if required, is the better option. Dynamically generated SQL statements are not good. They make it hard to debug code, they're not validated at compile time, they prevent scalability and reuse of execution plans in a lot of cases, and allow for SQL injection.

                              You may say "there are few cases that it is a good choice", but most experts would disagree and actually say "there are only a few rare and exceptional cases where it is needed" and that is not about making choices but rather out of necessity. Your scenario is nowhere near being a necessity for using dyanamic SQL.

                              As for your errors, they are mainly because of poor syntax issues, which would be obvious if you weren't using dynamic SQL, which is further proof...

                              You said:
                              let's try what you've wrote :
                              EXECUTE IMMEDIATE 'SELECT '||L_LIDER_IN || 'FROM LIDERS WHERE LIDER = '||lider_in into l_lider;
                              we accept the error : 
                              ORA-00923: FROM keyword not found where expected  
                              please noticed that those are two different things
                              Obviously the FROM keyword is the issue, and that's because you don't have a space before it, so it's becoming part of the column name you are selecting. The clue is in the error message, but of course that wouldn't have been apparent at compile time, only at run time because of your poor use of dynamic SQL.