Using ROWID to determine the primary key of a record

This function gets a rowid as an input parameter and then if valid, returns true, and as output parameters, gives the SQL statement, primary key columns list and a where clause statement including primary key columns and the actual values of the corresponding record. If the rowid is not valid, the function returns false, and if the rowid is valid but the related table does not have a primary key, then ... .

create or replace function rowid2pkwc(gRowid varchar2,
selectStmt out varchar2,
colsList out varchar2,
whereClause out varchar2) return boolean is
cursor c1(tableName varchar2) is
select * from user_cons_columns
where constraint_name = (select constraint_name from user_constraints
where table_name=tableName
and constraint_type = 'P');
r1 c1%rowtype;
objectId number;
gTableName varchar2(30);
sqlStmt varchar2(32767);
descTab DBMS_SQL.DESC_TAB;
colCnt integer;
cursorId integer;
rowsProcessed integer;
colType varchar2(100);
colTypeDt date;
colVal varchar2(100);
colValDt date;
begin
--gRowid := 'AAAWsyAAHAAATVgAES';

-- Finding out which table the given rowid belongs to
-- selectStmt out variable is formed here
objectId := dbms_rowid.rowid_object(chartorowid(gRowid));
begin
select object_name into gTableName from user_objects
where object_id = objectId
and object_type = 'TABLE';
selectStmt := 'select * from '||gTableName;
exception
when no_data_found then
return false;
end;

-- Finding out the primary key columns of the table found in previous step
-- colList out variable (primary key columns in comma separated format)
-- is formed here
sqlStmt := 'select ';
colCnt := 0;
for r1 in c1(gTableName) loop
if c1%rowcount > 1 then
sqlStmt := sqlStmt || ',';
colsList := colsList || ',';
end if;
sqlStmt := sqlStmt || r1.column_name;
colsList := colsList || r1.column_name;
end loop;
sqlStmt := sqlStmt || ' from '||gTableName ||' where rowid = ''' || gRowid || '''';

-- The sql statement formed according to table name and pk columns in previous step
-- is parsed for the purpose of retrieving the values of pk columns
cursorId := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursorId, sqlStmt, DBMS_SQL.NATIVE);

-- Indicating what values to expect from the query parsed in previous step
DBMS_SQL.DESCRIBE_COLUMNS (cursorId, colCnt, descTab);
for i in 1 .. colCnt loop
if descTab(i).col_type in (dbms_types.TYPECODE_DATE) then
dbms_sql.define_column( cursorId, i, colTypeDt);
else
dbms_sql.define_column( cursorId, i, colType, 100);
end if;
end loop;

-- Execute the dynamic sql query
rowsProcessed := DBMS_SQL.EXECUTE(cursorId);

-- Reading values of the selected columns (pk columns) for the purpose of forming
-- the where clause
-- whereClouse out variable is formed here
if dbms_sql.fetch_rows(cursorId) > 0 then
whereClause := '';
for i in 1 .. colCnt loop
if (length(whereClause)>0) then
whereClause := whereClause || ' and ';
end if;
if descTab(i).col_type in (dbms_types.TYPECODE_CHAR,
dbms_types.TYPECODE_VARCHAR2,
dbms_types.TYPECODE_VARCHAR) then
dbms_sql.column_value(cursorId,i,colVal);
whereClause := whereClause || descTab(i).col_name || '=''' || colVal || '''';
elsif descTab(i).col_type in (dbms_types.TYPECODE_NUMBER) then
dbms_sql.column_value(cursorId,i,colVal);
whereClause := whereClause || descTab(i).col_name || '=' || colVal;
elsif descTab(i).col_type in (dbms_types.TYPECODE_DATE) then
dbms_sql.column_value(cursorId,i,colValDt);
whereClause := whereClause || descTab(i).col_name || '=' ||
'to_date('''||to_char(colValDt,'DD, MM, YYYY, HH24:MI:SS')||''' ,''DD, MM, YYYY, HH24:MI:SS'')';
else
dbms_sql.column_value(cursorId,i,colVal);
whereClause := whereClause || descTab(i).col_name || '=''' || colVal || '''';
end if;
end loop;
if (length(whereClause)>0) then
whereClause := 'where '||whereClause;
end if;
end if;

DBMS_SQL.CLOSE_CURSOR(cursorId);

return true;
end rowid2pkwc;