AnithaLetchumi

 Profile Answers by AnithaLetchumi 

• Jul 27th, 2011

 

Hi, I don't think there exists predefined pseudo column for column number in oracle. I have done a procedure, which creates a view for displaying data from Mth to Nth column in a table. Hope it helps.

Code
 


CREATE OR REPLACE PROCEDURE select_col(first_col NUMBER, last_col NUMBER)


AS


BEGIN


 


DECLARE


col_no NUMBER;


col_name VARCHAR2(20);


selected_fields VARCHAR2(200);


 


-- USER_TAB_COLUMNS -> contains Columns of user's tables, views and clusters 


-- create a cursor, that has all the column names and it's corresponding column number.


 


CURSOR col_cursor IS


SELECT ROWNUM,COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('table_name') ;


 


BEGIN


OPEN col_cursor;


 


LOOP


 


FETCH col_cursor INTO col_no,col_name;


EXIT WHEN col_cursor%NOTFOUND;


 


-- Fetch the columns that's between the given criteria


 


IF(col_no=first_col) THEN


   selected_fields:= col_name;


END IF;


 


IF(col_no>first_col) AND (col_no<=last_col) THEN


   selected_fields := selected_fields ||','|| col_name;


   DBMS_OUTPUT.PUT_LINE(selected_fields);


-- Create a view to display the data between the columns.


   EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW myview AS SELECT '||selected_fields||' FROM table_name';


END IF;


 


END LOOP;


CLOSE col_cursor;


 


END;


END;


 


-------------------------------------------------------------------------------------------------------------------------


-- specify the column numbers to display the data in between.


BEGIN


select_col(2,4);


END;


 


-------------------------------------------------------------------------------------------------------------------------


 


SELECT * FROM myview;