This content has been marked as final. Show 1 reply
Thanks for first trying the SQL and PL/SQL forum before coming here -- that was definitely the right approach, and you got some very good suggestions there. In terms of your requirement for a single bind prompt for the in-clause value list, I think I have a possible solution. I will present it for the case of a character list, so you might have to tweak it for other data types. First add the following to your schema:
Now you can write a query, say for scott.dept, as follows and have Run Statement prompt for the in-clause value list as a single bind variable:
create or replace TYPE bind_tab_typ AS TABLE OF VARCHAR2(4000); create or replace FUNCTION comma_to_table(iv_raw IN VARCHAR2) RETURN bind_tab_typ PIPELINED IS ltab_lname dbms_utility.lname_array; ln_len BINARY_INTEGER; BEGIN dbms_utility.comma_to_table(list => iv_raw ,tablen => ln_len ,tab => ltab_lname); FOR i IN 1 .. ln_len LOOP PIPE ROW (ltab_lname(i)); END LOOP; END;
When prompted, provide the list of values separated by only commas without any extra blanks.
select * from dept where dname in ( select * from table( comma_to_table( :BNDS )) );
I'm not sure if the Varchar2(4000) really needs to be that large. I use it because that's what dbms_utility.lname_array uses.
SQL Developer Team