How to get the 3rd column(i.e all the data along with the column name)in a tab

How to get the 3rd column(i.e all the data along with the
column name)in a table?

Question Posted / dibya

2 Answers
8092 Views
Logica CMG, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How to get the 3rd column(i.e all the data along with the column name)in a table?..

Answer / ramesh

Column Names of a table are stored in the data dictionary
table user_tab_columns along with column_id. By referring
column_id we can display the column name as :

select column_name from user_tab_columns where table_name
= 'EMP' and column_id = 3;

Is This Answer Correct ?

10 Yes

6 No

How to get the 3rd column(i.e all the data along with the column name)in a table?..

Answer / p.rajasekar

This is the common solution for any table

Run the following Function and fire the follwoing query

CREATE OR REPLACE FUNCTION fnSpecificcol(p_tablename IN
VARCHAR2,
p_columnid IN number,
P_ROWID IN Varchar2)
RETURN VARCHAR2

AUTHID CURRENT_USER AS
TYPE c_refcur IS REF CURSOR;

lc_str VARCHAR2(4000);
out_char varchar2(50);
lc_colval VARCHAR2(4000);
tmp_strSQL varchar2(400);

c_dummy c_refcur;

l number;

BEGIN

tmp_strSQL := 'select column_name from user_tab_cols a '
||
'where upper(table_name)=' || '''' ||
p_tablename || '''' ||
' and column_id=' || p_columnid;
OPEN c_dummy FOR tmp_strSQL;

LOOP

FETCH c_dummy
INTO lc_colval;

EXIT WHEN c_dummy%NOTFOUND;

lc_str := lc_str || lc_colval;

END LOOP;

CLOSE c_dummy;

tmp_strSQL := '';
tmp_strSQL := 'select ' || lc_str || ' from ' ||
p_tablename ||
' A WHERE A.ROWID=' || '''' || P_ROWID
|| '''';
lc_str := '';
lc_colval := '';
OPEN c_dummy FOR tmp_strSQL;

LOOP

FETCH c_dummy
INTO lc_colval;

EXIT WHEN c_dummy%NOTFOUND;

lc_str := lc_str || lc_colval || CHR(13);

END LOOP;

CLOSE c_dummy;

RETURN SUBSTR(lc_str, 1);

EXCEPTION

WHEN OTHERS THEN

lc_str := SQLERRM;

IF c_dummy%ISOPEN THEN

CLOSE c_dummy;

END IF;

RETURN lc_str;

END;

SELECT fnSpecificcol('Tablename', ColumnNumber,A.ROWID)
from Tablename A

eg
SELECT rowtocol('AA', 1,A.ROWID) from aa A

Is This Answer Correct ?

2 Yes

4 No

Post New Answer