Dynamic Column Selection [message #282988] |
Sun, 25 November 2007 21:37  |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
I got this following code from http://forums.oracle.com/forums/thread.jspa?messageID=2179932�.
In this thread Todd Barry has given following nice code:
create or replace procedure p_dynamic_sql
is
v_sql varchar2(4000);
begin
v_sql := 'select ';
for r in (select column_name
from user_tab_cols
where table_name = 'EMP'
and column_name not in ('EMPNO','JOB')) loop
v_sql := v_sql || r.column_name || ',';
end loop;
v_sql := rtrim(v_sql, ',') || ' from emp';
dbms_output.put_line( v_sql );
-- do something with SQL (open cursor, etc.)
end;
/
But, I wants to get the output of above v_sql and have feature that removing columns should be asked when this stored procedure run and it should work like "," seperated.
How can we get output of v_sql and give feature for asking name of removing columns at execution of p_dynamic_sql?
Thanks & Regards
Girish
|
|
|
|
Re: Dynamic Column Selection [message #282992 is a reply to message #282990] |
Sun, 25 November 2007 21:56   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
I am not able to process above code to get the output and what should i code to have feature for excluding the column names with "," seperated and at the runtime of this procedure.
Please help and guide me.
Thanks
|
|
|
|
Re: Dynamic Column Selection [message #282996 is a reply to message #282994] |
Sun, 25 November 2007 22:13   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
Thanks for your reply.
My question is, suppose there are 100 columns in a table and i want to get the data of 99 or 98; excluding 1 or 2 columns; so i have to write names of all the columns i.e. 99 or 98 (whatso ever i wish).
I do'nt want to mention all the required column names; rather i wish to tell that i do not want to output of such and such columns.
|
|
|
Re: Dynamic Column Selection [message #282997 is a reply to message #282988] |
Sun, 25 November 2007 22:18   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>I do'nt want to mention all the required column names; rather i wish to tell that i do not want to output of such and such columns.
Please go & create your own language that supports such a requirement because SQL does NOT have this capability.
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME NOT IN ('COLUMN_C','COLUMN_E');
[Updated on: Sun, 25 November 2007 22:18] by Moderator Report message to a moderator
|
|
|
Re: Dynamic Column Selection [message #283000 is a reply to message #282997] |
Sun, 25 November 2007 22:25   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
>SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME NOT IN ('COLUMN_C','COLUMN_E');
Yes, it is in the above procedure, but :
1. I want to get data from the table as query output.
2. The column names should be asked when ever we run the procedure like :
Enter Column Name(s) to be exclude :col1,col3,col7
like that.
Sir, please help me; because i am novice / learning stage in oracle.
|
|
|
|
Re: Dynamic Column Selection [message #283004 is a reply to message #283002] |
Sun, 25 November 2007 22:41   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
My table is containing 50 columns named col1,col2...col50 and at different time i have to get the data from this table; but excluding only 1 or 2 columns.
So, i wish there should be a procedure; which will take name(s) of columns which i do not want to include in the query output; as a parameter.
>SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME NOT IN ('col1','col3','col7');
is in the procedure as mentioned above, but how i can get the data from the table and name of columns (which are to be excluded) should be asked by the procedure at run time.
I mean:
SQL>exec p_dynamic_sql;
Enter name of Column(s) for exclude : col1,col3,col7
Output Should be:
Col2 Col4 Col5 Col6 Col8 Col9...... Col50
...
...
Thanks & Regards
|
|
|
Re: Dynamic Column Selection [message #283005 is a reply to message #282988] |
Sun, 25 November 2007 22:43   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Please explain why you do NOT return data for ALL columns & then just not use the data for col1,col3,col7.
>should be asked by the procedure at run time.
PL/SQL, which runs deep inside the database, has no mechanism to interact directly with an external user.
PL/SQL, which runs deep inside the database, has no mechanism to interact directly with an external user.
PL/SQL, which runs deep inside the database, has no mechanism to interact directly with an external user.
[Updated on: Sun, 25 November 2007 22:46] by Moderator Report message to a moderator
|
|
|
Re: Dynamic Column Selection [message #283006 is a reply to message #283005] |
Sun, 25 November 2007 22:48   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
>& then just not use the data for col1,col3,col7
Sorry Sir, I am not following...!
How to "not use the data for col1,col3,col7" is my problem.
Please tell me how can i reply your question or what do you want to ask me.
|
|
|
|
Re: Dynamic Column Selection [message #283037 is a reply to message #283025] |
Mon, 26 November 2007 00:13   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
>Explaining what is your original need will help us to give you another way to do it.
I have a table named SubStat (Subject Wise Statistics of candidates) which is like this:
Sub1 Sub2 Sub3 Sub4 Sub5 ... Sub50
7438 3612 1254 11254 13215 40
From Sub1 to 10 is for Science Group
Sub11 to 23 is for Commerce Group
Sub24 to 37 is for Arts Group
Sub38 to 50 is for Computer Group
Now, suppose if i want to get the data of Science group; i have to write:
select sub1,sub2,sub3....sub10 from substat
and if i want to get the data for science and commerce group; so i have to write:
select sub1,sub2,sub3,sub4....sub37; like that.
So, i supposed that there would be a mechanism; by which we can tell the oracle; that i do not want to get the data of such and such column. Though i search the code for procedure as mentioned here; but i am not able to get the data as well.
Thanks & Regards
|
|
|
|
Re: Dynamic Column Selection [message #283069 is a reply to message #283061] |
Mon, 26 November 2007 01:12   |
gksharma
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
yes, you are absolutely right; i can write 4 or more static queries; but please read the following code once again :
create or replace procedure p_dynamic_sql
is
v_sql varchar2(4000);
begin
v_sql := 'select ';
for r in (select column_name
from user_tab_cols
where table_name = 'EMP'
and column_name not in ('EMPNO','JOB')) loop
v_sql := v_sql || r.column_name || ',';
end loop;
v_sql := rtrim(v_sql, ',') || ' from emp';
dbms_output.put_line( v_sql );
-- do something with SQL (open cursor, etc.)
end;
Procedure created.
SCOTT@orcl> set serveroutput on
SCOTT@orcl> exec p_dynamic_sql
select ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO from emp
PL/SQL procedure successfully completed.
Now, just i want to get the data from emp table as output of this procedure (as i think it is may be possible; but i do'nt know); then my question's one part would be solved (i.e. getting the data as query output). Second part i.e. "column_name not in (.....)" will remain unsolved.
Regards
|
|
|
|