Home » SQL & PL/SQL » SQL & PL/SQL » selecting columns dynamically
selecting columns dynamically [message #220524] Wed, 21 February 2007 01:49 Go to next message
gaya_c2003
Messages: 3
Registered: February 2007
Junior Member
Hi all,

I want to build a sql statement by dynamically passing the column names. I tried the following.

declare
s1 varchar2(100);
s varchar2(100);
begin
s:='c1';
select s into s1 from test2;
dbms_output.put_line(s);
end;

It is just displaying 'c1' not the value inside the column.

Can you please help on this?

Thanks
Gayathri.
Re: selecting columns dynamically [message #220537 is a reply to message #220524] Wed, 21 February 2007 02:36 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to use EXECUTE IMMEDIATE, as in this example:
DECLARE
  s1 NUMBER;
  s  VARCHAR2(100);
BEGIN
  s := 'sal';
  EXECUTE IMMEDIATE 'select sum(' || s ||') from emp ' INTO s1;
  dbms_output.put_line(s1);
END;
/

Problem with your code might also be a possible TOO-MANY-ROWS error (but that's another story).
Re: selecting columns dynamically [message #220543 is a reply to message #220537] Wed, 21 February 2007 03:30 Go to previous messageGo to next message
gaya_c2003
Messages: 3
Registered: February 2007
Junior Member
Hi,

my requirement is

1. I have one function which will return all the column names of the table
2. I have to loop through the table (using the above function) and send the data to a file.

I have attached the code.

function column_names_string (in_table_name in varchar2 ) return varchar2
is
s_cols varchar2(5000);
s_temp varchar2(1000);
s_cols_temp varchar2(1000);
type c_cols_type is ref cursor;
c_cols c_cols_type;
begin
s_temp:= 'select column_name from all_tab_columns where table_name ='''||in_table_name||''' order by column_id';
open c_cols for s_temp;
loop
fetch c_cols into s_cols_temp;
s_cols:=s_cols||','||s_cols_temp;
exit when c_cols%notfound;
end loop;
close c_cols;
return s_cols;

end column_names_string;


PROCEDURE id001(v_session IN VARCHAR2)
IS
s_id001 VARCHAR2(10000);
s_id001_rec VARCHAR2 (10000);

s_schema_name VARCHAR2(100);
s_table_name VARCHAR2(100);
s_temp varchar2(1000);
v_file UTL_FILE.FILE_TYPE;

TYPE c_id001_type IS REF CURSOR;
c_id001 c_id001_type;

BEGIN
s_schema_name:='ID_OWN_ST';
s_table_name:='ID_T_ST_REJ_PRD_ITEM2PRD';
s_temp:=column_names_string(s_table_name);
s_id001:='select '|| s_temp||' from '||s_schema_name||'.'||s_table_name; --||' where session_inst_cod_vc ='''|| v_session||'''';
v_file := UTL_FILE.FOPEN ('ID_35_FR', 'test2.txt', 'W');
s_temp:=s_id001;

OPEN c_id001 FOR s_temp;

LOOP
FETCH c_id001
INTO s_id001_rec;

UTL_FILE.PUT_LINE (v_file, s_id001_rec);
EXIT WHEN c_id001%NOTFOUND;
END LOOP;

CLOSE c_id001;
UTL_FILE.FCLOSE (v_file);

END id001;


In the select command it throws an error: missing expression.
s_id001:='select '|| s_temp||' from '||s_schema_name||'.'||s_table_name; --||' where session_inst_cod_vc ='''|| v_session||'''';[/B]

Can you please tell me where I am doing mistake.

Thanks
Gayathri.
Re: selecting columns dynamically [message #220551 is a reply to message #220543] Wed, 21 February 2007 04:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
why don't you just select * ???
Re: selecting columns dynamically [message #220554 is a reply to message #220551] Wed, 21 February 2007 04:24 Go to previous messageGo to next message
gaya_c2003
Messages: 3
Registered: February 2007
Junior Member
Hi,

If I give "select *", I need to capture the result into a %rowtype variable. Then it will not be generic for all the tables.

I want to capture the result of the cursor into a string variable so that it can be reused for any table.

And also,I want the "looping through cursor" to be generic for all the tables.

'select '|| s_temp||' from '||s_schema_name||'.'||s_table_name;

Thanks
Gayathri.
Re: selecting columns dynamically [message #220557 is a reply to message #220554] Wed, 21 February 2007 04:33 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What is the basic requirement?
Reason I ask is that it looks a long way around for something that comes done to typing in SQL*Plus:
select * from ID_OWN_ST.ID_T_ST_REJ_PRD_ITEM2PRD
Previous Topic: What ESTAMTED do ?
Next Topic: Automatic Export & Automatic Shutdown - Startup DB
Goto Forum:
  


Current Time: Mon Dec 05 10:56:19 CST 2016

Total time taken to generate the page: 0.04712 seconds