这段PL/SQL为何报错？
本帖最后由 swjia8315 于 2013-05-08 17:13:58 编辑
declare
v_constvarchar2(50) := 'AS';
v_sqlvarchar2(5000);
v_count number(5,0);
cursor c_column is
select distinct all_tab_columns.owner,all_tab_columns.table_name,all_tab_columns.column_name from all_tab_columns,all_tables where all_tab_columns.table_name = all_tables.table_name and all_tab_columns.data_type = 'VARCHAR2' and all_tab_columns.owner <> 'SYS' ;
begin
for v_column in c_column loop
v_sql := 'select count(*) from '||v_column.owner||'.'||v_column.table_name||' where '||v_column.column_name||'='||v_const;
execute immediate v_sql into v_count;
if v_count > 0 then
insert into asearch(owner_name,table_name,column_name,table_view) values (v_column.owner,v_column.table_name,v_column.column_name,'table');
end if;
end loop;
commit;
end;
/
declare
v_const number(11,0) := 12008;
v_sql varchar2(5000);
v_count number(11,0);
cursor c_column is
select distinct all_tab_columns.owner,all_tab_columns.table_name,all_tab_columns.column_name from all_tab_columns,all_tables where all_tab_columns.table_name = all_tables.table_name and all_tab_columns.data_type = 'NUMBER' and all_tab_columns.owner <> 'SYS' ;
begin
for v_column in c_column loop
v_sql := 'select count(*) from '||v_column.owner||'.'||v_column.table_name||' where '||v_column.column_name||'='||v_const;
execute immediate v_sql into v_count;
if v_count > 0 then
insert into asearch(owner_name,table_name,column_name,table_view) values (v_column.owner,v_column.table_name,v_column.column_name,'table');
end if;
end loop;
commit;
end;
/
