This content has been marked as final. Show 3 replies
If your table type is defined in SQL (as a DB object), you can do as
select * from customers where customer_id in ( select * from table(cast(i_customer_list as sql_type_name)) );
1 person found this helpful
Igor S. wrote:Depends on the nature of the situation.
What would be the best approach on handling this type of situation?
If, for example, the same sets of lists are commonly submitted by clients, then that should be addressed via normalisation. A relation looking something as follows:
+GROUPS = ( group_id, group_name, ... )+
+CUSTOMER_GROUPS = ( group_ip, customer_id )+
The client then submits a group identifier instead of a list of customer identifiers - and the SQL does a join or exists in the customer group relation in order to identify the relevant customers.
If, for example, the lists are fairly random and large per client session, but re-used by the client session, then a GTT (global temp table) can be considered. This allows the client session to submit its large list of customer identifiers once, and storing that in a GTT. Where SQLs can then use, and re-use, this table via a join or exists.
What does NOT make sense is the client session submitting a string, containing a comma delimited list of customer identifiers. Software engineering principles say that this approach is wrong.
Structured data and structured programming are sides of the same coin, that says well designed, consistent, and robust software. Sending structured data (such as a customer id array) as a string is, plain and simple, wrong.
Thank you both for your answers.