Oracle Stored Procedure not working (where in string)

Question

Here is my entire Stored Procedure:

CREATE OR REPLACE PROCEDURE "CTI"."TEMP_DIVISION"
(
    pDivisionList in varchar2,
    out_cursor out SYS_REFCURSOR
)
as
begin
  open out_cursor for
  SELECT distinct CTA_DIVISION
  FROM cti_account 
  WHERE CTA_DIVISION in (pDivisionList);  
END;

If I pass in TPA, it returns TPA as 1 row (correct). If I pass in CFL, it returns CFL as 1 row (correct). However, what I would like to do, is pass in TPA,CFL and have it run like WHERE CTA_DIVISION in ('TPA','CFL') returning 2 rows of data, but that's not happening. It's not returning any rows. What do I need to change to be able to pass that string in and get 2 rows returned? TIA

Answer 1

I think you can adapt Oracle: passing a list as bind variable to suit your case.

To make the varchar2 work as a list or single value, you'll need to parse the pDivisionList into an array (of possibly one value) and pass it on to another procedure (that can be defined within your procedure.)

WRT to the solution of @frustratedwithformsdesigner , dynamic sql is susceptible to sql-injection. Take care with such solutions.