Home » SQL & PL/SQL » SQL & PL/SQL » fetching dynamic columns from a table (Oracle 11g)
fetching dynamic columns from a table [message #622051] Wed, 20 August 2014 11:41 Go to next message
punitsoneji
Messages: 6
Registered: August 2014
Location: India
Junior Member
Hi Folks,

I have a table from which i need to fetch values.. and table definition would change at sometime so I cannot hardcode the column names.

So in order to get a list of all columns i have used all_tab_columns and below logic is implemented and I know it won't work

DECLARE
counter INTEGER := 0;
TYPE c_array IS TABLE OF VARCHAR2 (40);
counts1 c_array := c_array ();

BEGIN
for i in (select * from all_tab_columns where table_name=<table_name> order by column_id)
LOOP
counter1 := counter1 + 1;
counts1.EXTEND;
counts1 (counter1) := i.column_name;
END LOOP;

for i in ( select * from <table_name>) loop
for j in 1..counts1.count
loop
dbms_output.put_line(i.counts1(j));
end loop;
end loop;
end;

Can someone expert in collections can suggest on how can I get the values of columns.

If there is an alternate approach available for the same.. that is fine with me Smile

Thanks,

Punit Soneji
Re: fetching dynamic columns from a table [message #622054 is a reply to message #622051] Wed, 20 August 2014 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>.. and table definition would change at sometime so I cannot hardcode the column names.
yes, you can & should hardcode the column names.

>can suggest on how can I get the values of columns.
the datatype of the columns matter for correct presentation.
Re: fetching dynamic columns from a table [message #622055 is a reply to message #622054] Wed, 20 August 2014 11:55 Go to previous messageGo to next message
punitsoneji
Messages: 6
Registered: August 2014
Location: India
Junior Member
Hi BlackSwan,

>suppose I have a table which has 25 columns and in the next run it might have more or less columns so I can't hardcode the column names in loop to fetch the values of it

>>For the same reason I made a collection of all available columns in this table on which I would perform actions

>say datatype of all columns in that table would be of varchar2(20)

Thanks

Punit Soneji

[Updated on: Wed, 20 August 2014 11:57]

Report message to a moderator

Re: fetching dynamic columns from a table [message #622060 is a reply to message #622055] Wed, 20 August 2014 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> how can I get the values of columns.
to where are these values supposed to go after you get them?
Re: fetching dynamic columns from a table [message #622061 is a reply to message #622060] Wed, 20 August 2014 12:10 Go to previous messageGo to next message
punitsoneji
Messages: 6
Registered: August 2014
Location: India
Junior Member
> to where are these values supposed to go after you get them?

these values will be used to form a string as in below example :-

^^^p_id=>v_set,NULL,v_effective_from,v_effective_to~~~460000000,103~~~460000001,10~~~460000002,2~~~460000003,*~447471147```

103 - is value available @ column1
10 - is value available @ column2
2 - is value available @ column3
* - is value available @ column4

Numbers like 460000000,460000001,460000002 & 460000003 are available in another collection
and prefix of the string is predefined.

I hope you got my requirement

Re: fetching dynamic columns from a table [message #622068 is a reply to message #622051] Wed, 20 August 2014 13:40 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Why is the structure of the table changing?
How is this data being used when you don't know from one day to the next what it even IS?

This design appears to be seriously and deeply flawed.
Re: fetching dynamic columns from a table [message #622070 is a reply to message #622061] Wed, 20 August 2014 13:51 Go to previous messageGo to next message
fahed
Messages: 5
Registered: August 2014
Junior Member
I think this will help you

DECLARE
V_QRY LONG;
BEGIN

FOR DATA IN (SELECT *
FROM SCOTT.EMP
ORDER BY EMPNO) LOOP

FOR COL IN (SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP'
ORDER BY COLUMN_ID) LOOP

EXECUTE IMMEDIATE 'SELECT '||COL.COLUMN_NAME||' FROM SCOTT.EMP WHERE EMPNO = '||DATA.EMPNO INTO V_QRY;

Dbms_Output.Put_Line(COL.COLUMN_NAME||'-> '||V_QRY);

END LOOP; --DATA LOOP

END LOOP;-- COLUMN LOOP

END;

Re: fetching dynamic columns from a table [message #622072 is a reply to message #622070] Wed, 20 August 2014 14:00 Go to previous messageGo to next message
punitsoneji
Messages: 6
Registered: August 2014
Location: India
Junior Member
Hi Fahed,

thanks for providing your prompt suggestion.

This won't be proper due to below reason :-

suppose there are 2 entries for an empno and 1st loop will process the records 1 by 1 and when we use execute immediate to get the values for the same employee.. i will get 2 rows but actually i would expect only 1 value... is there a way if we can use current of 1 loop row ?

thanks,

Punit Soneji
Re: fetching dynamic columns from a table [message #622074 is a reply to message #622072] Wed, 20 August 2014 14:15 Go to previous messageGo to next message
fahed
Messages: 5
Registered: August 2014
Junior Member
I think you are talking about this

EXECUTE IMMEDIATE 'SELECT '||COL.COLUMN_NAME||' FROM SCOTT.EMP WHERE ROWNUM = 1 AND EMPNO = '||DATA.EMPNO INTO V_QRY;
Re: fetching dynamic columns from a table [message #622076 is a reply to message #622074] Wed, 20 August 2014 14:36 Go to previous messageGo to next message
punitsoneji
Messages: 6
Registered: August 2014
Location: India
Junior Member
Hi Fahed,

Consider below dataset :-

empno empname empsal
1 A 1000
1 B 2000
1 C 3000


the outer loop will run for 3 times but everytime I will get same salary which is 1000

It would be great if I can get salary for the row for which loop is running

Thanks,

Punit Soneji
Re: fetching dynamic columns from a table [message #622077 is a reply to message #622076] Wed, 20 August 2014 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: fetching dynamic columns from a table [message #622097 is a reply to message #622077] Thu, 21 August 2014 01:49 Go to previous messageGo to next message
fahed
Messages: 5
Registered: August 2014
Junior Member
This will solve your problem

DECLARE
V_QRY LONG;
BEGIN

FOR DATA IN (SELECT EMP.*,ROWID
FROM SCOTT.EMP
ORDER BY EMPNO) LOOP

FOR COL IN (SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP'
ORDER BY COLUMN_ID) LOOP

EXECUTE IMMEDIATE 'SELECT '||COL.COLUMN_NAME||' FROM SCOTT.EMP WHERE ROWID = '||Chr(39)||DATA.ROWID||Chr(39)||' AND EMPNO = '||DATA.EMPNO INTO V_QRY;

Dbms_Output.Put_Line(COL.COLUMN_NAME||'-> '||V_QRY);

END LOOP; --DATA LOOP

END LOOP;-- COLUMN LOOP

END;

Regards
Re: fetching dynamic columns from a table [message #622100 is a reply to message #622097] Thu, 21 August 2014 02:08 Go to previous messageGo to next message
punitsoneji
Messages: 6
Registered: August 2014
Location: India
Junior Member
Hi Fahed,

Thanks for your wonderful suggestion and help.

It's working now as I wanted.

Thank you so much for your help..

Thanks,

Punit Soneji
Re: fetching dynamic columns from a table [message #622125 is a reply to message #622097] Thu, 21 August 2014 05:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
fahed wrote on Thu, 21 August 2014 12:19

V_QRY LONG;


Still using LONG? That too in 11g? It is provided for backward compatibility.
Re: fetching dynamic columns from a table [message #622140 is a reply to message #622125] Thu, 21 August 2014 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

LONG in PL/SQL has nothing to do with LONG in SQL.


PL/SQL Language Reference version 11.2.0.1
The LONG data type stores variable-length character strings. The LONG data type is like the VARCHAR2 data type, except that the maximum size of a LONG value is 32,760 bytes (as opposed to 32,767 bytes).


However, latest 11.2 documentation is more restrictive:
Quote:
Instead of LONG, use VARCHAR2(32760)


Anyway, this LONG one is just a subtype (a synonym) defined by:
subtype LONG is VARCHAR2(32760);

Re: fetching dynamic columns from a table [message #622142 is a reply to message #622140] Thu, 21 August 2014 06:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Thu, 21 August 2014 17:08

LONG in PL/SQL has nothing to do with LONG in SQL.


Yes.

For both SQL and PL/SQL, Oracle recommends NOT to use LONG, instead, use VARCHAR2(32760), BLOB, CLOB or NCLOB in PL/SQL and use LOB columns (CLOB, NCLOB) in SQL.
Re: fetching dynamic columns from a table [message #622144 is a reply to message #622142] Thu, 21 August 2014 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I already pointed to the documentation; my point is that, if you will have many problems to use it as table column type, there is no problem to use it in PL/SQL, as soon as you know what it is, no more problem than INTEGER, INT, SMALLINT, DECIMAL, NUMERIC, NATURAL or POSITIVE, it is just an alias for a constrained type.

[Edit: "it" missing"]

[Updated on: Thu, 21 August 2014 07:09]

Report message to a moderator

Re: fetching dynamic columns from a table [message #622158 is a reply to message #622144] Thu, 21 August 2014 14:07 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Fair explanation Michel. I do agree the disadvantage of LONG is mostly in SQL, having a table column as LONG and thus preventing conditions/expressions on that LONG column.
Previous Topic: Help Required In - Row Generator Query
Next Topic: using pivot
Goto Forum:
  


Current Time: Fri Mar 29 03:26:05 CDT 2024