locked
return oracle error ORA-01427 [help] RRS feed

  • Question

  • User-583959464 posted

    i don't know why my sql don't have subquery but it return ora-01427 single row subquery retrun more than 1 rows.

    <row id='123'>

      <c199> Name199 </c199>

     <c199 m='2'> 20181231 </c199>

    < c199 m='3'> address199 </c199>

    </row>

    <row id='456'>

      <c199> Name456 </c199>

     <c199 m='2'> 20181231 </c199>

    < c199 m='3'> address456 </c199>

    </row>

    my sql:

    select extract(xmltypecolumn, '/row/c199') , extractvalue(xmltypecolumn, '/row/c199[position()=2') from table_a;

    -----------------------------------------------------------

    i think this error occurs only when select some adnormal data. how to find out the adnormal data and fix it?

    Sunday, January 27, 2019 7:06 AM

All replies

  • User36583972 posted

    return oracle error ORA-01427 [help]

    i don't know why my sql don't have subquery but it return ora-01427 single row subquery retrun more than 1 rows.

    <row id='123'>

      <c199> Name199 </c199>

     <c199 m='2'> 20181231 </c199>

    < c199 m='3'> address199 </c199>

    </row>

    <row id='456'>

      <c199> Name456 </c199>

     <c199 m='2'> 20181231 </c199>

    < c199 m='3'> address456 </c199>

    </row>

    my sql:

    select extract(xmltypecolumn, '/row/c199') , extractvalue(xmltypecolumn, '/row/c199[position()=2') from table_a;

    -----------------------------------------------------------

    i think this error occurs only when select some adnormal data. how to find out the adnormal data and fix it?

    Hi 20141113,

    Which Data Base you have used? my sql or oracle?

    Form the error message, you may tried to execute a SQL statement that contained a SQL subquery that returns more than one row.

    You can try to use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved. Or Rewrite your query so that the subquery only returns one row.

    Besides, with a detailed description about the results including any exception messages. This will help us quickly analyze your problem.

    Best Regards,

    Yong Lu

    Monday, January 28, 2019 5:47 AM
  • User-583959464 posted
    i am using oracle. I have putted my sql from my post. the difference is <c199> has more than 1 node, mean there is another <c199 m="2"> in xmltype column, I am not sure if this will causing this error. but is it subquery?
    Monday, January 28, 2019 1:48 PM
  • User36583972 posted

    i am using oracle. I have putted my sql from my post. the difference is <c199> has more than 1 node, mean there is another <c199 m="2"> in xmltype column, I am not sure if this will causing this error. but is it subquery?

    As far as I know, the issue caused by you tried to execute a SQL statement that contained a SQL subquery that returns more than one row.

    You can try to use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved. Or Rewrite your query so that the subquery only returns one row.

    You can try it on your side.

    Besides, if you have any question oracle, you need go to the oracle support center and get suitable help.

    Thank you for your understanding.

    Best Regards,

    Yong Lu

    Tuesday, January 29, 2019 3:17 AM