create or replace procedure cnav_lock_test
(
 cnav_lockId OUT number,
 tableName IN varchar2,
 p_rowId IN number default null,
 p_keyValueList IN varchar2 default null
)
IS
 type t_entity is ref cursor;
 c_lockid t_entity;
 c_lockid2 t_entity;
 cnav_lockid2 number;
 defaultDeltaLockTime number;
 v_cursor number;
 v_command varchar2(2000);
 v_rows number; 
 v_rowId number;
 v_keyValueList varchar2(2000);
 v_cursorb number;
 v_commandb varchar2(2000);
 v_rowsb number;
 keyValue varchar2(2000);
 restStr varchar2(2000); 
BEGIN
 v_rowId := p_rowId;
 v_keyValueList := p_keyValueList;
 defaultDeltaLockTime := 120;
 if (tableName is null) then
  cnav_lockId := -2;
  return;
 end if;
 if (v_rowId is not null and v_keyValueList is not null) then
  cnav_lockId := -3;
  return;
 end if;
 if (v_rowId is not null) then
   cnav_keyvalues_from_rowid(v_keyValueList,tablename,v_rowid);
   v_rowId := null;
 end if;
 open c_lockid for select lock_id 
      from table_lock 
      where table_name=tableName
           and unlock_date is null and row_id is null;
 fetch c_lockid into cnav_lockId;
 open c_lockid2 for select table_lock.lock_id 
       from table_lock, table_lock_keys
       where table_lock.table_name=tableName
           and table_lock.lock_id=table_lock_keys.lock_id
           and table_lock.row_id is null
           and table_lock.unlock_date is null;
 fetch c_lockid2 into cnav_lockId2;
 if (c_lockid%rowcount != 0 and c_lockid2%rowcount = 0) then
  return;
 end if;
 close c_lockid;
 close c_lockid2;
 if (v_rowid is null and v_keyValueList is null) then
   open c_lockid for select lock_id from table_lock 
        where table_name=tableName
            and unlock_date is null;
   fetch c_lockid into cnav_lockId;
   if (c_lockid%rowcount = 0) then
     cnav_lockId := -1;
     return;
    else
     return; 
   end if;
 end if;
 close c_lockid;
 if (v_rowid is not null) then
   open c_lockId for select lock_id 
        from table_lock 
        where table_name=tableName
            and unlock_date is null
            and row_id = v_rowid;
   fetch c_lockid into cnav_lockId;
   if (c_lockid%rowcount = 0) then
     cnav_lockId := -1;
     return;
    else
     return; 
   end if;
 end if;
 if (v_keyValueList is not null) then  
   v_cursor := DBMS_SQL.OPEN_CURSOR;
   v_command := 'select table_lock.lock_id from table_lock,table_lock_keys ';
   v_command := v_command || ' where table_lock.table_name=''' || tableName ||'''';
   v_command := v_command || ' and table_lock.lock_id=table_lock_keys.lock_id';
   v_command := v_command || ' and table_lock.unlock_date is null';
   v_command := v_command || ' and table_lock_keys.key_value in (';
          keyValue:=listfirst(v_keyValueList,',');
          reststr:=listrest(v_keyValueList,',');
          while (keyValue is not null) loop
            v_command := v_command ||'''' || keyValue || '''';
            keyValue:=listfirst(reststr,',');
            reststr:=listrest(reststr,','); 
            if (keyValue is not null) then
              v_command := v_command ||',';
            end if;        
          end loop;
   v_command := v_command || ')';
   DBMS_SQL.PARSE(v_cursor,v_command,DBMS_SQL.V7);  
   DBMS_SQL.DEFINE_COLUMN(v_cursor,1,cnav_lockId);
   v_rows := DBMS_SQL.EXECUTE(v_cursor);
   v_rows :=DBMS_SQL.FETCH_ROWS(v_cursor);
   if (v_rows != 0) then
     DBMS_SQL.COLUMN_VALUE(v_cursor,1,cnav_lockId);
   end if;
   DBMS_SQL.CLOSE_CURSOR(v_cursor);
   if (v_rows = 0) then
     cnav_lockId := -1;
     return;
    else
     return; 
   end if;
 end if;  
 END cnav_lock_test;
/
show errors;
grant execute on cnav_lock_test to public;