OraFAQ Forum: SQL & PL/SQL » selecting columns dynamically

Home » SQL & PL/SQL » SQL & PL/SQL » selecting columns dynamically

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

selecting columns dynamically [message #220524]

Wed, 21 February 2007 01:49

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.

Report message to a moderator

Re: selecting columns dynamically [message #220537 is a reply to message #220524]

Wed, 21 February 2007 02:36

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: selecting columns dynamically [message #220543 is a reply to message #220537]

Wed, 21 February 2007 03:30

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.

Report message to a moderator

Re: selecting columns dynamically [message #220551 is a reply to message #220543]

Wed, 21 February 2007 04:18

Frank
Messages: 7901
Registered: March 2000

Senior Member

why don't you just select * ???

Report message to a moderator

Re: selecting columns dynamically [message #220554 is a reply to message #220551]

Wed, 21 February 2007 04:24

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.

Report message to a moderator

Re: selecting columns dynamically [message #220557 is a reply to message #220554]

Wed, 21 February 2007 04:33

skooman
Messages: 913
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

Report message to a moderator

Previous Topic:	What ESTAMTED do ?
Next Topic:	Automatic Export & Automatic Shutdown - Startup DB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 17:59:03 CST 2024