How can I correctly reference string variables in Oracle SQL WHERE clause?

Data and Databases
1

Oracle SQL does not auto-quote defined string variables. For example:

SET v_dep = '''HR''';
SELECT id FROM staff WHERE dep = v_dep;

How to fix this?

2

hey, maybe u can pass your string as a bind variable instead of literal? im curious if u tried concatenating quotes manually for proper sql. have u had any luck with that method? lets see if it can suit your exact context.

3

hey, try inserting the quotes directly, like using ‘’‘||v_dep||’‘’ in your where clause. sometimes playing with bind var is easyr, but dynamic concat can work for your case too. check if it suits your application.