REM dbdrv: none 
REM $Header: wfquet10i.sql 26.3 2005/01/03 19:00:49 dlam noship $
REM ***********************************************************************
REM   NAME
REM       wfquet10i.sql - 
REM   DESCRIPTION
REM       Creates Object Types for  Advanced Queue payload structure.
REM   USAGE
REM       sqlplus wfuser/wfpwd @wfquet10i
REM   MODIFICATION
REM   23/07/2003 ROSTHOMA  Created the script as a copy of wfquet.sql
REM                        to be run from the sys schema for 10i  
REM ********************************************************************

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

-- connect &1/&2;

prompt NOTE: make sure the Object Option is installed on the database
prompt or you will receive an error. 

REM if the payload already exist, dont raise error.

WHENEVER SQLERROR EXIT FAILURE ROLLBACK


declare
  cnt     number;
begin

  -- Create WF_PAYLOAD_T in current user schema
  -- the rownum = 1 is just a safeguard, since I could not find an unique
  -- index on owner, object_name and object_type in sys.obj$.
  select count(*)
    into cnt
    from all_objects 
   where object_name='WF_PAYLOAD_T'
     and object_type = 'TYPE'
     and owner = user
     and rownum = 1;

  if (cnt = 0) then
    execute immediate 'create or replace type wf_payload_t as object ('||
                  '  itemtype	   varchar2(8),'||
                  '  itemkey	   varchar2(240),'||
                  '  actid	   number,'||
                  '  function_name varchar2(200),'||
                  '  param_list    varchar2(4000),'||
                  '  result        varchar2(30))';
  end if;

end;
/

commit;
exit;