create or replace procedure cnav_keyvalues_from_rowid
(
 p_keyValueList OUT varchar2,
 p_tableName IN varchar2,
 p_rowId IN varchar2
)
IS
 type t_entity is ref cursor;
 c_keycol t_entity;
 v_keycolname varchar(2000); 
 v_keytypename varchar(2000); 
 v_keycoltype varchar(2000);
 v_keycollist  varchar(2000);
 v_number number;
 v_varchar2 varchar(2000);
 v_date date;
 countit number;
 v_cursor number;
 v_command varchar(2000);
 v_keytype varchar(2000);
 v_rows number;
BEGIN
 insert into rodmess values ('First foot print in cnav_keyvalues_from_rowid');
 commit; 
 if (p_tableName is null or p_rowId is null) then
  p_keyValueList := null;
  return;
 end if;
 insert into rodmess values ('Second foot print in cnav_keyvalues_from_rowid');
 commit; 
 open c_keycol for select dcc.column_name,utc.data_type
         from user_constraints dc, user_cons_columns dcc,user_tab_columns utc 
         where
                dcc.table_name=dc.table_name
            and dc.table_name=upper(p_tablename)
            and dcc.owner=dc.owner
            and dcc.constraint_name=dc.constraint_name
            and (dc.constraint_type='P' or dc.constraint_type='R')
            and utc.table_name=dc.table_name
            and utc.column_name=dcc.column_name;  
      insert into rodmess values ('after open c_keycol');
     commit; 
 v_keycollist := '';
 countit:=0; 
 loop                  
     insert into rodmess values ('root print before c_keycol fetch');
     commit; 
     fetch c_keycol into v_keycolname,v_keytypename;
     insert into rodmess values ('level 3 coming at you: '||v_keycolname||' : '||v_keytypename);
     commit; 
     exit when c_keycol%NOTFOUND;
     countit:=countit+1;
     if (countit != 1) then
      v_keycollist := v_keycollist  || '||'',''||';
     end if;
     if (v_keytypename = 'NUMBER') then 
       v_keycollist := v_keycollist  || 'to_char(' || v_keycolname || ')';
     end if;
     if (v_keytypename = 'DATE') then 
       v_keycollist := v_keycollist  || 'to_char(' || v_keycolname || ',''MM-MON-YYY HH24:MM:SS'')';
     end if;
     if (v_keytypename = 'VARCHAR2') then 
       v_keycollist := v_keycollist  || 'replace(' || v_keycolname || ','','',''%44'')';
     end if;
 end loop;
 insert into rodmess values ('yeah baby another print in cnav_keyvalues_from_rowid');
 commit; 
 if (countit>0) then
     insert into rodmess values ('level 3 level 3 warning: '||v_keycollist);
     commit; 
     v_cursor := DBMS_SQL.OPEN_CURSOR;
     v_command := 'select ' || v_keycollist || ' from ' || p_tablename || ' where rowid=''' ||  p_rowid||'''';
     insert into rodmess values ('yeah baby the command '|| v_command);
     commit; 
     DBMS_SQL.PARSE(v_cursor,v_command,DBMS_SQL.V7);
     DBMS_SQL.DEFINE_COLUMN(v_cursor,1,p_keyvaluelist,2000);  
     v_rows := DBMS_SQL.EXECUTE(v_cursor); 
     v_rows :=DBMS_SQL.FETCH_ROWS(v_cursor);
     p_keyvaluelist :='';
     if (v_rows != 0) then
        DBMS_SQL.COLUMN_VALUE(v_cursor,1,p_keyvaluelist);
      else
       p_keyValueList := null;
       return;        
     end if;
   else
     p_keyvaluelist:=null;
     return;
 end if; 
END cnav_keyvalues_from_rowid;
/
show errors;
grant execute on cnav_keyvalues_from_rowid to public;