CODEWITHSUNDEEP
sqloracle-database
Victor
Victor

Reputation: 17087

oracle query returns 0 records when args passed

When I run an sql like this:

select * from tbl_emp where emp_name like '%%'

It gives me all records. When I run it like this:

select * from tbl_emp where emp_name like : arg_emp_name

Then execute the query, then pass arg_emp_name as '%%', it returns 0 records. Why is this behavior? Thank you.

Upvotes: 2

Views: 311

Answers (2)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

NEW ANSWER:-

As per the comments obtained for the answer, it is clear that the oracle does not see any difference between the literal and the binding variable values. The meaning does not change as I had mentioned in my old answer. The answer from @Conard seems logical.

OLD ANSWER:-

When you pass the '%%' as an argument, it is literally taken as those string and they lose out on special meaning which they have when used in the query.

It means, the query will search your column for data containing '%%' instead of treating it as a LIKE. Hence you get 0 records.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

That should work fine. For example the following returns the same number of records as no where clause

EXEC :arg_emp_name := '%%';

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE  :arg_emp_name

If you add more to the string 

   EXEC :arg_emp_name := '%ABC%';

   SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE  :arg_emp_name

only Tables with ABC in their name are returned

However I would note that there's no space between : and arg_emp_name

Having that space there causes the error 

Error starting at line 4 in command:
SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE  : arg_emp_name
Error report:
SQL Error: ORA-01008: not all variables bound
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:

Upvotes: 5

Related Questions