3 Replies Latest reply: Jul 18, 2013 5:05 AM by swapnil kambli RSS

    ORA-00900 Invalid SQL statement

    swapnil kambli

      Hi,

       

      I am running below code

       

      begin

      for c_masteraccntfirmcustid in (select regexp_substr(substr(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1), 0, length(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1))-1),'[^,]+', 1, level) col_val from dual connect by regexp_substr(substr(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1), 0, length(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1))-1), '[^,]+', 1, level) is not null)

      loop
      dbms_output.put_line(c_masteraccntfirmcustid.col_val);
      --dbms_output.put_line('Hi');
      end loop;
      end;

       

       

       

       

      This is giving me Ora-900

       

       

       

      but below works

       

       

      select regexp_substr(substr(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1), 0, length(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1))-1),'[^,]+', 1, level) col_val from dual connect by regexp_substr(substr(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1), 0, length(substr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1))-1), '[^,]+', 1, level) is not null

       

       

      The requirement is from AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)  this string I need

       

      742254777

      2531215

      2568091

       

      one by one and then I am going to create a dynamic SQL for each of these values.

       

       

      Could you please help?

       

       

      Thanks,

      Swapnil

        • 1. Re: ORA-00900 Invalid SQL statement
          Mahir M. Quluzade

          I run your PL/SQL block, but not getting error.

           

          What is your database version?

           

           

          BEGIN
            FOR c_masteraccntfirmcustid                                                                                                                                                    IN
            (SELECT regexp_substr(SUBSTR(SUBSTR('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',
                   instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1), 0, LENGTH(SUBSTR('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',
                   instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1))-1),'[^,]+', 1, level) col_val
            FROM dual
              CONNECT BY regexp_substr(SUBSTR(SUBSTR('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1), 0,
              LENGTH(SUBSTR('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)',instr('AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)','(')+1))-1), '[^,]+', 1, level) IS NOT NULL
            )
            LOOP
              dbms_output.put_line(c_masteraccntfirmcustid.col_val);
              --dbms_output.put_line('Hi');
            END LOOP;
          END;
          
          • 2. Re: ORA-00900 Invalid SQL statement
            Mahir M. Quluzade

            And Can you  check this?

             

             

             

             

             

             

            WITH data AS 

               (SELECT  'AND WB0.MASTERACCNTFIRMCUSTID in (742254777, 2531215, 2568091)'   as str FROM dual) 

             

                SELECT regexp_substr(SUBSTR(SUBSTR(str,1,LENGTH(Str)-1),INSTR(str,'(') + 1), '[^,]+',1,level) res 

                  FROM data 

                CONNECT BY  level - 1 <= regexp_count(str,',') ;

             

            Regards

            Mahir M. Quluzade

            • 3. Re: ORA-00900 Invalid SQL statement
              swapnil kambli

              I might have missed some thing while I executed it

              It is executing it now