List of Record type matching issue Oracle

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)

  p_workspace VARCHAR2(100):='WS-38515';
  l_num_workspaces integer;
  select count(*)
    into l_num_workspaces
    from WMSYS.all_workspaces 
   where workspace = p_workspace;

  if( l_num_workspaces = 0 )
  end if;

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.

Leave a Comment

Your email address will not be published.

Scroll to Top