Is there a reason that you need dynamic SQL, a collection, and a loop to iterate over the collection? It sounds like you just want to check whether the row exists which can be done with a simple
count (you could make the check more efficient by writing it as a
where exists but that’s probably not important here)
declare p_workspace VARCHAR2(100):='WS-38515'; l_num_workspaces integer; begin select count(*) into l_num_workspaces from WMSYS.all_workspaces where workspace = p_workspace; if( l_num_workspaces = 0 ) then dbms_wm.CreateWorkspace(p_workspace,isrefreshed=>TRUE); end if; end;
Of course, you could also just skip the check, call
CreateWorkspace, and handle the exception when it already exists. If you expect that the vast majority of the time you’re going to create the workspace, that might be more efficient.
CLICK HERE to find out more related problems solutions.