Friday, October 31, 2008

Oracle function that returns table row details.

Oracle function that accepts table name,primary key field name,primary key field value as input parameters
CREATE OR REPLACE FUNCTION Getrowdetails
(
TableName VARCHAR,
primarykey VARCHAR,
primarykeyvalue VARCHAR
)
RETURN VARCHAR
AS
--To return details of a row in table with primary key field name and value mentioned
tempColname user_tab_cols.COLUMN_NAME%TYPE;
usercursor sys_refcursor;
tempVar VARCHAR2(20000);
tempValueString VARCHAR2(25000);
BEGIN
tempValueString:=tempValueString||'
';
--Iterating for each column in the specific table and getting details for specific row for
-- that column
OPEN usercursor FOR SELECT column_name FROM user_tab_cols WHERE UPPER(table_name)=UPPER(TableName) ORDER BY column_id;
LOOP
FETCH usercursor INTO tempColname;
EXIT WHEN usercursor%NOTFOUND;
EXECUTE IMMEDIATE 'select '||tempColname||' from '||TableName||' where '||primarykey||' = '''||primarykeyvalue||''''INTO tempVar;
IF(tempVar IS NOT NULL) THEN
tempValueString:=tempValueString||'
'||tempColname||' -- > '||tempVar||'
';
ELSE
tempValueString:=tempValueString||'
'||tempColname||' -- > NULL
';
END IF;
END LOOP;
--Retuning the details of the row
RETURN tempValueString;
END;
/

This function returns details concatenating with delimeter (New Line character),

No comments: