How to get the 3rd column(i.e all the data along with the
column name)in a table?
Answers were Sorted based on User's Feedback
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 |
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 |
What is PL/SQL table ?
Write a sql to print only character form the below string. @So&*CIE%$TE@GEN!@RAL
How to fetch values from testtable1 that are not in testtable2 without using not keyword?
Does group by remove duplicates?
Is oracle and sql same?
What is sql indexing?
how to fetch alternate records from a table? : Sql dba
What is the difference between functions, procedures, and packages in pl/sql?
What is Raise_application_error ?
How to use boolean type in select statement?
What do you know by pl/sql cursors?
If a cursor is open, how can we find in a pl/sql block?