Pages

Search

Thursday, October 30, 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:

Post a Comment