游标中的动态表名 [英] dynamic table name in cursor

问题描述

我试图插入架构是动态的数据，因为用户输入架构名称.出于某种原因，我得到 ORA-000942: table does not exist 即使表在正确的架构中.

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.

代码如下:

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 :)

推荐答案

经过一些研究和网上闲逛，我发现了这个.似乎我需要以稍微不同的方式声明模式和表名变量.为什么这个解决方案有效?不知道.

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;
/  

这篇关于游标中的动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！