Pages

Search

Friday, February 13, 2009

Oracle function that returns columns of a table in table format.

In this case it is not just to return the columns in a table but they should be returned in a table format.
So we should crate a user defined type.
To know about this in brief check at
Oracle Type in Prog. findings
I am creating a type called “tbl_columns”.
create type tbl_Columns as table of varchar2(255)



My oracle function that returns the columns in given table as table format is

--Function expecting table name as input parameter
CREATE OR REPLACE FUNCTION fun_columns(argTableName IN VARCHAR)
--setting return type, such that returning the pipe lined rows
RETURN tbl_columns pipelined
IS
--Initialising cursor to get column names of a given table
CURSOR cur_columns IS SELECT column_name FROM user_tab_cols WHERE table_name=UPPER(argTableName);
tmpRec user_tab_cols.column_name%TYPE;
BEGIN
--opening cursor to read values from the select query
OPEN cur_columns;
LOOP
FETCH cur_columns INTO tmpRec;
EXIT WHEN cur_columns%NOTFOUND;
--piping each value as row in the defined type table tbl_columns
pipe ROW(tmpRec);
END LOOP;
END;




I would like to create a table called “MyTable” to test with this oracle function as
CREATE TABLE MyTable
(
MyColumn1 NUMBER,
MyColumn2 FLOAT,
MyColumn3 DATE,
MyColumn4 VARCHAR2(100),
MyColumn5 CHAR
)



After compiling the oracle function, I am using the below select query to get the coulns available in this table (MyTable).
SELECT * FROM (TABLE(fun_columns('MyTABle')));
Output:


No comments:

Post a Comment