create or replace procedure child_check(tname in varchar2)
as
cursor c1(tname varchar2) is
select column_name from user_tab_columns where table_name =tname;
v_sql varchar2(200);
v_count number;
begin
select count(1) into v_count from tname where instr(i.column_name,chr(10))>0 or instr(i.column_name,chr(13))>0 or instr(i.column_name,chr(32))>0;
end;
我的select into 那个语句里面 from 后面的表名是传进来的变量 ,但是执行时提示我‘表或试图不存在’怎么解决啊 在线等!
EXECUTE IMMEDIATE sql 语句也不好使呀……
as
cursor c1(tname varchar2) is
select column_name from user_tab_columns where table_name =tname;
v_sql varchar2(200);
v_count number;
begin
select count(1) into v_count from tname where instr(i.column_name,chr(10))>0 or instr(i.column_name,chr(13))>0 or instr(i.column_name,chr(32))>0;
end;
我的select into 那个语句里面 from 后面的表名是传进来的变量 ,但是执行时提示我‘表或试图不存在’怎么解决啊 在线等!
EXECUTE IMMEDIATE sql 语句也不好使呀……
as
cursor c1(tname varchar2) is
select column_name from user_tab_columns where table_name =tname;
v_sql varchar2(200);
v_count number;
begin
select count(1) into v_count from tname i where instr(i.column_name,chr(10))>0 or instr(i.column_name,chr(13))>0 or instr(i.column_name,chr(32))>0;
end;
2 v_i number(10);
3 v_table varchar2(10);
4 begin
5 v_table:='dual';
6 execute immediate 'select 1 from '||v_table into v_i;
7 dbms_output.put_line(v_i);
8 end;
9 /
1
PL/SQL procedure successfully completed
SQL>
as
v_sql varchar2(200);
v_count number;
begin
set v_sql:='select count(1) from '||tname||
' where instr(i.column_name,chr(10))>0'||
' or instr(i.column_name,chr(13))>0'||
' or instr(i.column_name,chr(32))>0'; execute immediate v_sql into v_count;
end;
as
cursor c1(tname1 varchar2) is
select column_name from user_tab_columns where table_name = tname;
v_sql varchar2(200);
v_count number;
begin
execute immediate 'select count(1) from ' || tname into v_count;
dbms_output.put_line(v_count);
end;
create or replace procedure child_check(tname in varchar2)
as
cursor c1 is select column_name from user_tab_columns where table_name =upper(tname);
v_sql varchar2(200);
v_count number;
begin
for i in c1 loop
v_sql:='select count(1) from '||tname||' where instr('||i.column_name||',chr(10))>0 or instr('||i.column_name||',chr(13))>0 or instr('||i.column_name||',chr(32))>0';
execute immediate v_sql into v_count;
end loop;
dbms_output.put_line(v_count);
end;
/
as
cursor c1(tname varchar2) is
select column_name from user_tab_columns where table_name =tname;
v_sql varchar2(200);
v_count number;
begin
execute immediate 'select count(1) from '||tname|| 'where instr(i.column_name,chr(10))>0
or instr(i.column_name,chr(13))>0 or instr(i.column_name,chr(32))>0 ' into v_count;
end;