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



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

More SQL PLSQL Interview Questions

Explain two virtual tables available at the time of database trigger execution.

1 Answers  


Can we create index on primary key?

0 Answers  


What are the types of variable use in pl sql?

0 Answers  


How can I create a table from another table without copying any values from the old table?

8 Answers  


How to take user input in pl sql?

0 Answers  






How many aggregate functions are available there in sql?

0 Answers  


Is left join faster than join?

0 Answers  


can i use global variables in stored procedure or function

1 Answers   L&T,


Can we use threading in pl/sql?

0 Answers  


What is PL/SQL Records?

0 Answers  


Explain scalar functions in sql?

0 Answers  


Do we need to rebuild index after truncate?

0 Answers  


Categories