 | |
#1
 
| |||
| |||
|
Cursors -
01-07-2008
, 01:07 PM
#2
| |||
| |||
|
|
Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! |
another process:
declare
type mycur is ref cursor;
myrefcursor mycur;
begin
open myrefcursor for select * from user_objects;
end;
/
You COULD build a query string and substitute the table_name:
declare
type mycur is ref cursor;
myrefcursor mycur;
cursor get_tabname is
select table_name from user_tables;
begin
for tabname in get_tabname loop
open myrefcursor for 'select * from '||
tabname.table_name||' where 0=1';
end loop;
end;
/
And possibly that's what you're wanting.
David Fitzjarrell
#3
| |||
| |||
|
|
Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! |
another process:
declare
type mycur is ref cursor;
myrefcursor mycur;
begin
open myrefcursor for select * from user_objects;
end;
/
You COULD build a query string and substitute the table_name:
declare
type mycur is ref cursor;
myrefcursor mycur;
cursor get_tabname is
select table_name from user_tables;
begin
for tabname in get_tabname loop
open myrefcursor for 'select * from '||
tabname.table_name||' where 0=1';
end loop;
end;
/
And possibly that's what you're wanting.
David Fitzjarrell
#4
| |||
| |||
|
|
Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! |
another process:
declare
type mycur is ref cursor;
myrefcursor mycur;
begin
open myrefcursor for select * from user_objects;
end;
/
You COULD build a query string and substitute the table_name:
declare
type mycur is ref cursor;
myrefcursor mycur;
cursor get_tabname is
select table_name from user_tables;
begin
for tabname in get_tabname loop
open myrefcursor for 'select * from '||
tabname.table_name||' where 0=1';
end loop;
end;
/
And possibly that's what you're wanting.
David Fitzjarrell
#5
| |||
| |||
|
|
Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! |
another process:
declare
type mycur is ref cursor;
myrefcursor mycur;
begin
open myrefcursor for select * from user_objects;
end;
/
You COULD build a query string and substitute the table_name:
declare
type mycur is ref cursor;
myrefcursor mycur;
cursor get_tabname is
select table_name from user_tables;
begin
for tabname in get_tabname loop
open myrefcursor for 'select * from '||
tabname.table_name||' where 0=1';
end loop;
end;
/
And possibly that's what you're wanting.
David Fitzjarrell
#6
| |||
| |||
|
|
On Jan 7, 1:07*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! A REF CURSOR is used for passing the output of a select statement to another process: declare * *type mycur is ref cursor; * *myrefcursor mycur; begin * *open myrefcursor for select * from user_objects; end; / You COULD build a query string and substitute the table_name: declare * *type mycur is ref cursor; * *myrefcursor mycur; * *cursor get_tabname is * *select table_name from user_tables; begin * *for tabname in get_tabname loop * * * * * *open myrefcursor for 'select * from '|| tabname.table_name||' where 0=1'; * *end loop; end; / And possibly that's what you're wanting. David Fitzjarrell |
Got it done. Thanks!
#7
| |||
| |||
|
|
On Jan 7, 1:07*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! A REF CURSOR is used for passing the output of a select statement to another process: declare * *type mycur is ref cursor; * *myrefcursor mycur; begin * *open myrefcursor for select * from user_objects; end; / You COULD build a query string and substitute the table_name: declare * *type mycur is ref cursor; * *myrefcursor mycur; * *cursor get_tabname is * *select table_name from user_tables; begin * *for tabname in get_tabname loop * * * * * *open myrefcursor for 'select * from '|| tabname.table_name||' where 0=1'; * *end loop; end; / And possibly that's what you're wanting. David Fitzjarrell |
Got it done. Thanks!
#8
| |||
| |||
|
|
On Jan 7, 1:07*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! A REF CURSOR is used for passing the output of a select statement to another process: declare * *type mycur is ref cursor; * *myrefcursor mycur; begin * *open myrefcursor for select * from user_objects; end; / You COULD build a query string and substitute the table_name: declare * *type mycur is ref cursor; * *myrefcursor mycur; * *cursor get_tabname is * *select table_name from user_tables; begin * *for tabname in get_tabname loop * * * * * *open myrefcursor for 'select * from '|| tabname.table_name||' where 0=1'; * *end loop; end; / And possibly that's what you're wanting. David Fitzjarrell |
Got it done. Thanks!
#9
| |||
| |||
|
|
On Jan 7, 1:07*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: Hi All, I've looked over some internet pages and have not found the solution yet..... I have a cursor which is the same in terms of columns for 5 tables, except the table name is different. *Can I create some type of REF cursor or something where I can change the table name so I can use the same select? Not sure if this can be a cursor parameter...... Not my design, so I cannot combine the tables...... Thanks!! A REF CURSOR is used for passing the output of a select statement to another process: declare * *type mycur is ref cursor; * *myrefcursor mycur; begin * *open myrefcursor for select * from user_objects; end; / You COULD build a query string and substitute the table_name: declare * *type mycur is ref cursor; * *myrefcursor mycur; * *cursor get_tabname is * *select table_name from user_tables; begin * *for tabname in get_tabname loop * * * * * *open myrefcursor for 'select * from '|| tabname.table_name||' where 0=1'; * *end loop; end; / And possibly that's what you're wanting. David Fitzjarrell |
Got it done. Thanks!
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.