List of Record type matching issue Oracle

I want to check if a workspace is not available in all_workspace table then create new workspace.
My requirement is if p_workspace is not matching the list of value of v_workspace then it should create new one.

DECLARE
p_workspace VARCHAR2(100):='WS-38515';
TYPE t_ws IS TABLE OF VARCHAR2(30);
v_workspace t_ws := t_ws();
Begin
  execute immediate '
    SELECT
    DISTINCT WORKSPACE 
    FROM WMSYS.all_workspaces 
    '
    BULK COLLECT INTO v_workspace;
      FOR i IN v_workspace.first..v_workspace.last LOOP
        IF v_workspace(i) <> p_workspace THEN
           dbms_wm.CreateWorkspace(p_workspace,isrefreshed=>TRUE);
        END IF;
      END LOOP;   
        EXCEPTION
          when others then
            Dbms_Output.put_line(p_workspace || ': ' || SQLERRM);
END;
/

It gives me error after creation.

PL/SQL procedure successfully completed.

WS-38515: ORA-20023: a workspace already exists with the name: 'WS-38515'

If I run that workspace individually then its working. but when I am passing through block with checking availability then its not working.

execute dbms_wm.CreateWorkspace('WS-38515',isrefreshed=>TRUE);

Please help anyone.

Answer

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.