That kind of "sample SQL statement" is not sufficient for anyone to help you.
Unless this is a SQL Developer tool-specific question (as in Early adopter 188.8.131.52.84 (ORA-01427 single-row subquery returns more than one row) ), you are much better off posting in SQL & PL/SQL where you will definitely need to provide the (executable) DDL and SQL select code for your test case.
but I am uanble to understand why this happens when exporting the data.
It has NOTHING to do with 'exporting the data' or with sql developer.
You query executes on the database server and that exception is caused by a scalar subquery (perhaps in that join you didn't show us) returning more than one value.
If you just execute the query in Sql Dev it will typically only fetch the first 50 rows.
But that query could run fine until the very last row when that exception occurs. So if you just run the query and then go to the last row you will likely get that same exception.
You need to fix your query. For help with that you need to post in the other forum and post the actual query.
1 person found this helpful
When you run the query in sql developer, initially it shows 50 records, where as toad shows 500 rows.
so if any error in first 50 rows then it will show error that time only, if that error happens after 50th row then when you scroll down, that time it will show that error.
I guess when you use distinct, then it will show the error directly irrespective of no of rows, because when you use distinct then it will check each and every record until end.
So true..I have a subquery which returns multiple rows. I was deceived by the output that SQL Developer returned and skipped examining my inner queries. Thanks for the insight.
I was deceived by the output that SQL Developer returned and skipped examining my inner queries
It is not that SQL Developer "skipped examining" your inner queries. Since you used the Run Statement button (or, alternately, Ctrl+Enter), only the fetch limit (see Tools > Preferences > Database > Advanced > SQL Array Fetch Size) number of rows populated the Query Result grid, as rp0428 noted above.
Keep in mind there are two kinds of error: compile time and run time. There are a couple of things you can do in SQL Developer so the run time (ORA-01427) error you experienced in the Export (when all rows are processed) will also occur during normal query processing. To have all rows processed...
1. Use Run Script (F5) after setting Tools > Preferences > Database > Worksheet > Max Rows... to a high enough value
2. Use Run Statement (Ctrl+Enter), then focus cursor on a Query Results grid cell and Ctrl+End to process all rows.
Hope this helps you in the future.