2

In SQL Developer when I want to provide a list of values to a single placeholder SQL developer reads them as one value. e.g 

select *  from some_table where some_value in (:list_of_values);

When I enter 1,2,3 into the Value text box it oracle reads all 1,2,3 as one value enter image description here

Adelin
  • 18,144
  • 26
  • 115
  • 175

1 Answers1

1

Note: this is an answer to the original version of the question which has been significantly changed by edits.

What do I need to put in the text box below ?

There is nothing that you can put into the text box to generate multiple columns when you are using a bind variable since a bind variable represents a single value.

If you change your query to use a substitution variable:

select &ary from dual;

Then you can enter 1,2,3 in the dialog box and you will get the output:

1 2 3
- - -
1 2 3
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Ok, what if the query what something 'where value in (:array)' ? – Adelin Nov 17 '17 at 14:16
  • @Adelin See [this](https://stackoverflow.com/questions/44778342/unable-to-pass-string-value-1-2-as-input-to-an-oracle-query) or [this](https://stackoverflow.com/questions/35231757/pl-sql-use-list-variable-in-where-in-clause) or [this](https://stackoverflow.com/questions/242771/oracle-stored-procedure-with-parameters-for-in-clause) or [this](https://stackoverflow.com/questions/35648999/where-in-condition-not-accepting-string-value) or [this](https://stackoverflow.com/q/44798614/1509264). That particular question has been asked (and closed as a duplicate) many many times. – MT0 Nov 17 '17 at 14:21
  • 1
    I don't think the question is duplicated as I am asking about Oracle SQL Developer for Oracle in general – Adelin Nov 17 '17 at 14:24
  • @Adelin Using SQL Developer or TOAD or Java does not change how a bind variable behaves. An answer for one of those IDEs is an answer for all of them. – MT0 Nov 17 '17 at 14:27
  • 1
    I don't agree with you, because in java if I replace :list_of_values with '1,2,3' it will actually work the way it should :) – Adelin Nov 17 '17 at 14:28
  • 1
    It is like Sql Develop reads the provided value of 1,2,3 as '1,2,3','other_values', '....' Not like 1,2,3 – Adelin Nov 17 '17 at 14:30
  • @Adelin No - In Java, if you use `PreparedStatement st = con.prepareStatement("SELECT * FROM DUAL WHERE '1' IN (:ary)" ); st.setString("1,2,3");ResultSet rs = st.executeQuery();` then you will not get any results back as `'1'` does not equal `'1,2,3'`. *A bind variable is a single value in all IDEs*. – MT0 Nov 17 '17 at 14:37
  • "if I replace :list_of_values with '1,2,3'" then you are not using a bind variable `:list_of_values` you are using a substitution variable `&list_of_values`. – MT0 Nov 17 '17 at 14:42