This discussion is archived
3 Replies Latest reply: Jul 18, 2013 3:05 AM by swapnil kambli RSS

ORA-00900 Invalid SQL statement

swapnil kambli Journeyer
Currently Being Moderated

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

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

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

    I might have missed some thing while I executed it

    It is executing it now

Legend

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