How to get the 3rd column(i.e all the data along with the
column name)in a table?
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
What is difference sql and mysql?
GLOBAL TEMPORARY TABLE over Views in advantages insolving mutating error?
What is java sql connection?
What is localdb mssqllocaldb?
Why cannot I use bind variables in ddl/scl statements in dynamic sql?
Explian rowid, rownum? What are the psoducolumns we have?
How is debugging done?
What is the full form of sql?
Which is better stored procedure or query?
Is a view faster than a stored procedure?
Define join and name different types of joins?
Which is better cte or subquery?
What does stand for in sql?
Define the select into statement.
explain commit and rollback in mysql : sql dba