Cursor with table parameter

Question

Is it possible to declare cursor with table name as parameter and then loop through it?

I'm trying something like this:

create or replace procedure p_update_something as

v_tab varchar2(100) := my_table;
v_name varchar2(100) := my_name;

cursor c_global (l_tab in varchar2, l_name in varchar2) is
  select * from l_tab  -- [here points the error]
   where substr(comp_id, 1,2) in (l_name);

begin
  for r in c_global(v_tab, v_name) loop
  [update statement]
  end loop;

end p_update_something;

but while compiling I'm getting error:

ORA-00942: table or view does not exist

in cursor declaration (marked in code above as [here points the error]).

Anyone have idea what is wrong with it?

Out of curiosity, why do you need to keep changing the tablename if all the tables have the same structure? It's not because you're creating and dropping tables on the fly, is it? Also, you could most likely combine the update statement and the cursor in a single update or merge statement. — Boneist
– Boneist, Commented Nov 23, 2016 at 15:19
I need to loop through similar tables in few databases (each representing other country) and update my main table. Also there is requirement to commit after 10k rows, that's why I can't use simple update. Cheers. — massko
– massko, Commented Nov 23, 2016 at 16:54

William Robertson · Accepted Answer · 2016-11-23 12:42:27Z

2

There's no table called 'l_tab'. (Edit: I get what you're trying to do, I'm just describing it from the compiler's point of view.)

To vary the table at runtime you'll need dynamic sql, e.g.

open c for 'select * from ' || l_tab;

(There's no for r in c loop cursor loop construction for explicitly opened cursors either, so your next challenge will be how to manage an unknown table structure, unless your use case guarantees that all tables passed as l_tab will have a common structure.)

edited Nov 23, 2016 at 12:42

answered Nov 23, 2016 at 12:22

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

massko Over a year ago

Yeah, I know there is no such a table - I wanted pass it's name as a variable ;) Structure of my tables will be the same so that is not a problem and not a question :) I know I can use OPEN FOR, FETCH construction, but I was wandering if this case is possible - so the answer would be: "It's not possible to declare cursor with table name parameter" right?

William Robertson Over a year ago

Correct, you can't use variables for table or column names in static cursors like for r in (select * from l_tab) where l_tab is a variable. The compiler needs to know those things at compile time.

Collectives™ on Stack Overflow

Cursor with table parameter

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related