create or replace procedure cnav_lock_table
(
 cnav_lockId OUT number,
 tableName IN varchar2,
 p_rowId IN number default null,
 p_keyValueList IN varchar2 default null,
 DeltaLockTime IN number default 120
)
IS
 type t_entity is ref cursor;
 c_newid t_entity;
 v_newid number;
 v_cursor number;
 v_rowId number;
 v_keyValueList varchar2(2000);
 v_command varchar2(2000);
 v_rows number; 
 keyValue varchar2(2000);
 restStr varchar2(2000);
 cursor lock_table_lock is select flag from lock_table_mutex for update;
BEGIN
 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;
   if (v_keyValueList is null) then
     cnav_lockId := -4;
     return;
   end if;
 end if;
 open lock_table_lock;
 cnav_lock_test(cnav_lockId,tableName,null,v_keyValueList);
 if (cnav_lockId != -1) then -- desired table or row already locked 
     cnav_lockId := -1;
     rollback;
     return;
 end if;  
 open c_newid for select 
      lock_id_seq.nextval from dual;             
 fetch c_newid into cnav_lockId;
 close c_newid;
 --v_command := 'select lock_id_seq.nextval newid from dual';
 --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);
     insert into table_lock (
          lock_id,
          table_name,
          row_id,
          delta_lock_time,
          lock_date, 
          cnav_member_id,
          created_by,
          created_date,
          last_modified_by,
          last_modified_date
         ) 
         values (
           cnav_lockId,
           tableName,
           v_rowid,
           deltalocktime,
           sysdate,
           5772,
           5772,
           sysdate,
           5772,
           sysdate
        );
   if (v_keyValueList is not null) then
          keyValue:=listfirst(v_keyValueList,',');
          reststr:=listrest(v_keyValueList,',');
          while (keyValue is not null) loop
            --v_cursor := DBMS_SQL.OPEN_CURSOR;
            --v_command := 'insert into table_lock_keys (lock_id,key_value,';
            --v_command := v_command || 'created_by,created_date,last_modified_by,last_modified_date) values (';
            --v_command := v_command || to_char(cnav_lockId)||','''||keyValue||''',';
            --v_command := v_command || '5772,sysdate,5772,sysdate)';
            --DBMS_SQL.PARSE(v_cursor,v_command,DBMS_SQL.V7);  
            --v_rows := DBMS_SQL.EXECUTE(v_cursor);
            --DBMS_SQL.CLOSE_CURSOR(v_cursor);  
            insert into table_lock_keys (
                  lock_id,
                  key_value,
                  created_by,
                  created_date,
                  last_modified_by,
                  last_modified_date) 
               values ( cnav_lockId,
                        keyValue,
                        5772,
                        sysdate,
                        5772,
                        sysdate
            );
            keyValue:=listfirst(reststr,',');
            reststr:=listrest(reststr,',');         
          end loop;
      end if;
      commit;
      close lock_table_lock;
 END cnav_lock_table;
/
show errors;
grant execute on cnav_lock_table to public;