请参阅第二次编辑.新的编码得到不同的错误.
See Second Edit. New coding gets different error.
我收到在遇到if时遇到循环"错误.我的目标是返回已由特定project_id更新的表的列表. project_id存储在每个表上标有project_id的列中.
I am getting the "encountered a loop when expecting an if" error. My goal is to return a list of tables that have been updated by a specific project_id. The project_id is stored in a column on each table labeled project_id.
我使用了with语句来创建2个表.一个表(sb_table)是我要查看的所有表的单列.还有更多表,但我认为如果我先缩小列表范围,它将加快处理速度.我正在创建的另一个表(项目)返回一个值,将提供的prjt_name转换为实际项目编号(不要问我为什么,但这是我公司的设置方式,用户创建了prct_name却从不知道项目编号).
I used a with statement to create 2 tables. One table (sb_table) is a single column of all the tables that I want to look at. There are many more tables, but I figured it would speed things up if I narrowed down the list first. The other table I am creating (project) returns a single value turning the prjt_name provided into the actual project number (don't ask me why, but this is how my company has it set up, user creates a prct_name and is never aware of the project number).
然后,我尝试遍历各个表,以查看它们的project_id列中是否具有项目编号.如果没有,我将它们从sb_table中删除.
Then I am trying to loop through the tables to see if they have the project number in their project_id column. If they do not, I delete them from the sb_table.
最终,我将要从所有更新的表中获取所有更新的行,但是我目前仍停留在获取更新表的列表中.
Ultimately, I am going to want to get all of the updated rows from all of the updated tables, but I am currently stuck on getting a list of the updated tables.
declare query varchar2(10000); table_count NUMBER; update_count number; prjt_name varchar2 not null := "01213264B"; cursor my_cur is select sbt.table_name from sb_table sbt; begin with sb_tables as (select table_name from all_tab_columns@db2 where column_name = 'PROJECT_ID' and owner = 'SANDBOX'), project as (select project_id from sandbox.sb_project@db2 where project_name = upper(prjt_name)) --select sbt.table_name --from sb_table sbt for tableName in my_cur loop query := 'select count(t.project_id) as "CNT" '|| 'from sandbox.' || tableName || '@db2 t, project p '|| ' where t.project_id = p.project_id '; Execute immediate query into update_count; if update_count <= 0 then query := 'DELETE FROM sb_tables where table_name = ' || tableName; execute immediate query ; end loop; end;根据注释,将select语句移至声明并现在遍历my_cur.我仍然遇到相同的错误.
Edit 1: Per comments, moved the select statement to the declare and am looping through my_cur now. I still get the same error.
根据建议更新了编码.现在,我得到了另一个错误消息.
Edit 2: Updated coding based on suggestions. I now get a different error message.
NEW ERROR: ORA-06550: line 12, column 16: PLS-00306: wrong number or types of arguments in call to '||' ORA-06550: line 12, column 7:我在子查询中遗漏了一个=,从而产生了错误第13行缺少表达式".
Edit 3: I was missing an = in my sub query which produced the error "Missing expression at line 13."
现在,我得到一些结果,然后出现以下消息,出错了
Edit 4: Now I get some results then error out with the following message
ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file ext_qsp_benefit.dat in DATA_DIR not found ORA-02063: preceding 3 lines from ADHOC_POS15 ORA-06512: at line 13成功!显然我无法查询某些表.所以我只是把那些桌子拿出来了.
Edit 5: Success! Apparently I cannot query certain tables. So I just took those tables out.
最终编码为:
declare query varchar2(10000); update_count integer := 0; prjt_name varchar2(100) := '01213264B'; cursor my_cur is (select table_name from all_tab_columns@db2 where column_name = 'PROJECT_ID' and owner = 'SANDBOX' and table_name in ('X') ); tableName varchar2(100); begin open my_cur; loop fetch my_cur into tableName; exit when my_cur%NOTFOUND; update_count := 0; execute immediate 'select count(project_id) as "CNT" from sandbox.' || tableName || '@db2 ' || ' where project_id = (select project_id from sandbox.sb_project@db2 where project_name = ''' || prjt_name || ''' ) ' into update_count; if update_count > 0 then dbms_output.put_line (tableName); end if; end loop; close my_cur; end;这并不能完全满足我的要求.它将结果发送到dbms_output.但这是一个开始!谢谢大家的帮助!
This doesn't do exactly what I wanted. It sends the results to dbms_output. But It is a start! Thanks everyone for you help!
推荐答案DECLARE update_count integer := 0; prjt_name varchar2(100) := 'tttt'; mysql varchar2(100); tablename varchar2(100); cursor my_cur is select 'DUAL' from dual where 'PROJECT_ID' = 'PROJECT_ID' and 'SANDBOX' = 'SANDBOX'; begin open my_cur; LOOP FETCH my_cur into tablename; EXIT WHEN my_cur%NOTFOUND; update_count := 0; mysql := 'select count(*) ' || ' from ' || tablename || ' where ''TTTT'' = upper(''' || prjt_name || ''')' ; Execute immediate mysql INTO update_count; dbms_output.put_line (mysql); dbms_output.put_line (update_count); END LOOP; CLOSE my_cur; end;
我尝试过像你这样的人.成功执行
I tried one like yours. This executes successfully
declare query varchar2(10000); update_count integer := 0; prjt_name varchar2(100) := '01213264B'; cursor my_cur is select table_name from all_tab_columns@adhoc_pos15 where column_name = 'PROJECT_ID' and owner = 'SANDBOX'; tableName varchar2(100); begin open my_cur; loop fetch my_cur into tableName; exit when my_cur%NOTFOUND; update_count := 0; query := 'select count(t.project_id) as ''CNT'' from sandbox.' || tableName || '@adhoc_pos15 t' || ' where t.project_id = (select project_id from sandbox.sb_project@adhoc_pos15 where project_name = ''' || prjt_name || ''') ' ; execute immediate query into update_count; dbms_output.put_line (query); dbms_output.put_line (update_count); end loop; close my_cur; end;更多推荐
Oracle PL/SQL:帮助解决"PLS
发布评论