recent

Titulo

What the Heck is ORA-01795?

This is the error message that you should not be seeing it. However, when people use dummy tools to generate the SQL queries, they will often encounter this issue. Oracle does not let over 1000 values inside IN functions. In Order to avoid this situation, we will need to build inner queries. To make this explanation more clear lets do the lab exercise to demo this problem and solution.

Lab Exercise:  
Let's  re-create a problem and then put a fix to it..

Problem:
SELECT * 
FROM   dept 
WHERE  fname IN ( 'NAME1', 'NAME2',............... 'NAME1000', 'Name1002' ); 

Result:
Database Error: ORA-01795: maximum number of expressions in a
list is 1000

Solution:
SELECT * 
FROM   dept 
WHERE  name IN (SELECT fname 
                FROM   employee); 

What is the take home from this exercise? Write your own damn query! Be a MAN!!!
Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.