0

I'm having trouble passing in a string to a function where the string is going to be used in an IN clause.

The string looks like this:

OracleParameter p = new OracleParameter("'ABC','EEE','DDD','FFF'", val, OracleDbType.VarChar2, ParameterDirection.Input);

and the IN clause in the PL/SQL function is something like this:

sqlString := 'WHERE SomeCd IN (' || in_Codes || ')'

So when the function executes you'll get this for the clause:

WHERE someCd IN ('ABC','EEE','DDD','FFF')

The problem is, I can't really test this in my IDE since in order to pass this in I would have to use an escape sequence to escape the single quotes. This leads to the string to either look like this:

  1. '''ABC'',''EEE'',''DDD'',''FFF'''

    which gets parsed as ''ABC'',''EEE'',''DDD'',''FFF''

  2. q'['ABC','EEE','DDD','FFF']'

    Which gets sent as q'['ABC','EEE','DDD','FFF']' to the function.

Neither of these will work correctly as the format of the string is now wrong.

How can I pass in this list to the function in the correct format? Do I have to use a varray or something?

EDIT:

The problem in general is: When I escape a single quote '' within an escaped pair of single quotes '''', the escaped single quote '' is being passed in to the function also.

Jimenemex
  • 3,104
  • 3
  • 24
  • 56
  • [Oracle stored procedure with parameters for IN clause](https://stackoverflow.com/questions/242771/oracle-stored-procedure-with-parameters-for-in-clause) – Lukasz Szozda Jul 19 '18 at 15:42
  • 2
    Don't construct your SQL queries as strings in the first place. It's extremely insecure. – Servy Jul 19 '18 at 15:43
  • @Servy It's not constructed as a `string` on the C# side. Just passed in as a `VARCHAR2` to the PL/SQL function. – Jimenemex Jul 19 '18 at 15:49
  • https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand – Siraj K Jul 19 '18 at 15:56
  • _The problem is, I can't really test this in my IDE since in order to pass this in I would have to use an escape sequence to escape the single quotes_. I don't understand what you mean. Without commenting on your overall approach, your code for creating the `OracleParameter` value looks right. Why and where do you think you need to escape the single quotes in C#? – Matthew McPeak Jul 19 '18 at 15:59
  • @MatthewMcPeak The problem doesn't lie on the C# side, but when I test the `function` itself from PL/SQL developer. You cannot write the format `"'ABC','EEE','DDD','FFF'"` since you need to escape the single quote in the IDE and double quotes aren't valid syntax for a `VARCHAR2` – Jimenemex Jul 19 '18 at 16:06
  • You mean Oracle SQL\*Developer's "Run PL/SQL" window? If so, the correct value would be ''ABC'',''EEE'',''DDD'',''FFF''. That is, replace each single quote with two single quotes. Oracle SQL*Developer (latest version, anyway) will do that for you automatically if you forget. – Matthew McPeak Jul 19 '18 at 16:13
  • @MatthewMcPeak The escape `'` are being sent in with the command if I include them within `''`. – Jimenemex Jul 19 '18 at 16:15
  • 1
    They will appear that way in the **Run PL/SQL** window, but that is displaying wrapper code that SQL\*Developer is creating on the fly. Your actual function will receive the correct value. I tested this on SQL\*Developer 17.4. If you are not getting the same results, I suggest you update your question with more details and, possibly, a screenshot. – Matthew McPeak Jul 19 '18 at 16:21
  • Possible duplicate of [Oracle stored procedure with parameters for IN clause](https://stackoverflow.com/questions/242771/oracle-stored-procedure-with-parameters-for-in-clause) – Jimenemex Jul 19 '18 at 17:39
  • If the issue is in a PL/SQL Developer Test window then why is this tagged C#, and where is the PL/SQL code that is failing? I'm pretty sure this is easy to fix but I need to see some code. – William Robertson Jul 19 '18 at 22:00

1 Answers1

1

Try OracleParameter p = new OracleParameter(@"'ABC','EEE','DDD','FFF'", val, OracleDbType.VarChar2, ParameterDirection.Input);

This should interpret the string literally without changing the string itself.

Note that i added the @ symbol.

SJ10
  • 315
  • 1
  • 4
  • 12
  • I was going to suggest that, but you don't need to escape single quotes in C#, do you? So the verbatim character (@) shouldn't be necessary? – Matthew McPeak Jul 19 '18 at 16:03
  • 1
    Personally I have seen single quotes cause problems in some of my own projects, but I'm pretty sure that his issue is that escape chars are being sent into his SQL function as part of the command. So I suggest using @ because it shouldn't change the string hes passing. Hopefully – SJ10 Jul 19 '18 at 16:10