How to get the 3rd column(i.e all the data along with the
column name)in a table?
- 2 Answers
- 7539 Views
- Logica CMG, I also Faced
- E-Mail Answers
Answer Posted / 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 View All Answers
Is oracle and sql same?
what does it mean to have quoted_identifier on? : Sql dba
Which tcp/ip port does sql server run on? How can it be changed? : Sql dba
What happens when a trigger is associated to a view?
How do I save a sql query?
What is the meaning of disabling a trigger?
how to return query output in html format? : Sql dba
What is the process of debugging?
What is nosql vs sql?
How do I remove duplicates in two columns?
What is a sql profiler?
Explain dml and ddl?
What is materialized view. What are different methods of refresh?
What are three advantages to using sql?
How delete all records from table in sql?
