dynamic table name in cursor

Question

I am trying to insert data whose schema is dynamic, as in user enters the schema name. For some reason I get ORA-000942: table doesn't exist even though table is there in the correct schema.

Here is the code:

declare
  l_ext_files_names varchar2(4000) := '&1';
  l_schema_name varchar2(4000) := '&2';
  l_table_name varchar2(4000) := l_schema_name||'.files_tbl';
  l_int_files_names varchar2(4000);
  c_file_name sys_refcursor;
begin
  open c_file_name for 'select file_names from '||l_table_name;
  loop
     fetch c_file_name into l_int_files_names;
     if (l_int_files_names <> l_ext_files_names) then
        insert into l_table_name (file_names, status)
        values (l_files_names, 'done');
     end if;
  end loop;
  close c_file_name;
end;

Any idea where I am making the mistake? Cheers in advance :)

Thank for for replies. I tried insert statement using the same schema that created the table. The insert statement went fine. So the problem is not with privileges. — Jaanna, Jul 02 '15 at 11:21
try the select statement with the same user and `set role NONE;` If the SELECT privilge is granted via ROLE it will not work in PL/SQL. — Marmite Bomber, Jul 02 '15 at 11:35
What do you get when you insert `DBMS_OUTPUT.PUT_LINE ('select file_names from '||l_table_name);` before you open the cursor? — Wernfried Domscheit, Jul 02 '15 at 13:35

score 0 · Answer 1 · answered Jul 02 '15 at 11:15

Most probably the user doesn't have the privilege to access the table in other schema.

You would need to grant the SELECT privilege.

GRANT SELECT ON table_name TO username;

Put the required table_name and in username put the user which is executing the SELECT.

score 0 · Accepted Answer · answered Jul 03 '15 at 08:39

After some researches and strolling trough the internet, this is what I found. It seems that I need to declare the schema and table names variables in a slightly different way. Why this solution works? no idea.

declare
  l_schema_name all_tables.owner%type := '&1';
  l_ext_files_names varchar2(4000) := '&2';
  l_table_name all_tables.table_name%type := 'FILES_TBL';
  l_int_files_names varchar2(4000);
  c_file_name sys_refcursor; 

begin
  open c_file_name for 'select file_names from '||l_schema_name||'.'||l_table_name;
  fetch c_file_name into l_int_files_names;
  if c_file_name%notfound then
     execute immediate 'insert into '||l_schema_name||'.'||l_table_name||' (file_names, entry, status) values ('''||l_ext_files_names||''', sysdate, ''done'')';
     commit;
  elsif (l_int_files_names <> l_ext_files_names) then
     execute immediate 'insert into '||l_schema_name||'.'||l_table_name||' (file_names, entry, status) values ('''||l_ext_files_names||''', sysdate, ''done'')';
     commit;
  else 
     dbms_output.put_line ('Already there.');
  end if;
exception when others then
  dbms_output.put_line ('Some errors.');
end;
/

dynamic table name in cursor

2 Answers2

Linked

Related