Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Parallel hint with case giving ORA-01427

NikJunejaFeb 6 2024 — edited Feb 6 2024

Hi Experts,

Need help in a query.. We have a three tier architecture so not much we can do in inputs we receive from UI:-

Create table student (eid number, name varchar2(5));
insert into student values (10,'10A');
insert into student values (50,'50A');

When i run this without parallel hint with inputs as 10A,50A it runs fine, but when i run with a parallel hint it gives

ORA-01427: single-row subquery returns more than one row

There not much I can do with the query as input can be delimited or ALL value:

                     select /*+ parallel(4) */ * from student where    CASE WHEN :NAME ! = 'ALL'
                                       THEN NAME 
                                       ELSE 
                                        (
                                                     select regexp_substr(:NAME,'[^,]+', 1, level) from dual
                                          connect by regexp_substr(:NAME, '[^,]+', 1, level) is not null
                                        )
                                  END
                                           IN
                                           (
                                          select regexp_substr(:NAME,'[^,]+', 1, level) from dual
                                          connect by regexp_substr(:NAME, '[^,]+', 1, level) is not null
                                          );

Just want to know why is Oracle calculating the else part in case we force it to go parallel?

Comments
Post Details
Added on Feb 6 2024
8 comments
72 views