Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Column Selection
Dynamic Column Selection [message #282988] Sun, 25 November 2007 21:37 Go to next message
gksharma
Messages: 8
Registered: October 2007
Junior Member
I got this following code from http://forums.oracle.com/forums/thread.jspa?messageID=2179932&#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 #282990 is a reply to message #282988] Sun, 25 November 2007 21:50 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
EXECUTE IMMEDIATE in the hands of a novice is like giving a loaded pistol to a young child.
PL/SQL, which runs deep inside the database, has no mechanism to interact directly with an external user.
Re: Dynamic Column Selection [message #282992 is a reply to message #282990] Sun, 25 November 2007 21:56 Go to previous messageGo to next message
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 #282994 is a reply to message #282988] Sun, 25 November 2007 22:06 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above.

While you may clearly know & understand what you want, I certainly do not know what are the intended inputs or expected results.

Re: Dynamic Column Selection [message #282996 is a reply to message #282994] Sun, 25 November 2007 22:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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 Go to previous messageGo to next message
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 #283002 is a reply to message #282988] Sun, 25 November 2007 22:29 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME NOT IN ('col1','col3','col7');

Please explain why you do NOT return data for ALL columns & then just not use the data for col1,col3,col7.
Re: Dynamic Column Selection [message #283004 is a reply to message #283002] Sun, 25 November 2007 22:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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 Go to previous messageGo to next message
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 #283025 is a reply to message #283006] Sun, 25 November 2007 23:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In short Ana's answer is: you can't do it the way you want ("PL/SQL, which runs deep inside the database, has no mechanism to interact directly with an external user").
Explaining what is your original need will help us to give you another way to do it.

Regards
Michel
Re: Dynamic Column Selection [message #283037 is a reply to message #283025] Mon, 26 November 2007 00:13 Go to previous messageGo to next message
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 #283061 is a reply to message #283037] Mon, 26 November 2007 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How different cases do you have? Only 4? Then use 4 static queries.

Regards
Michel
Re: Dynamic Column Selection [message #283069 is a reply to message #283061] Mon, 26 November 2007 01:12 Go to previous messageGo to next message
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
Re: Dynamic Column Selection [message #283075 is a reply to message #283069] Mon, 26 November 2007 01:21 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

In
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference
you have exemples of how to return rows from dynamic SQL.

Regards
Michel
Previous Topic: update query performance
Next Topic: how to commit grant on tables
Goto Forum:
  


Current Time: Mon Dec 05 21:11:47 CST 2016

Total time taken to generate the page: 0.12118 seconds