代码如下create or replace
PROCEDURE PROCEDURE1 AS
type ref_cursor is ref cursor;
tablename_list ref_cursor;
fieldname_list ref_cursor;
foundtable ref_cursor;
tablename user_tables.table_name%type;
fieldname user_tab_columns.column_name%type;
column_sql varchar2(500);
record_rownum number(1);
begin
open tablename_list for select table_name from user_tables;
loop
fetch tablename_list into tablename;
exit when tablename_list%notfound;
column_sql := 'select column_name from user_tab_columns where table_name='''||tablename||'''';
open fieldname_list for column_sql;
loop
fetch fieldname_list into fieldname;
exit when fieldname_list%notfound;
column_sql := 'select rownum from '||tablename||' where '||fieldname||' like ''%test%'' and rownum=1';
open foundtable for column_sql;
loop
fetch foundtable into record_rownum;
exit when foundtable%notfound;
dbms_output.put_line(tablename||' '||fieldname);
end loop;
close foundtable;
end loop;
close fieldname_list;
end loop;
close tablename_list;
end;编译过程无错
运行时出现以下错误,请问怎么解决?ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "JOOYOO.PROCEDURE1", line 21
ORA-06512: at line 2还有就是上面的代码我通动手动建库和表,查询,正常,但如果一张表中有两或两个以上你查询的关键字,他只显示最开始找到的,能否显示所有呢?
PROCEDURE PROCEDURE1 AS
type ref_cursor is ref cursor;
tablename_list ref_cursor;
fieldname_list ref_cursor;
foundtable ref_cursor;
tablename user_tables.table_name%type;
fieldname user_tab_columns.column_name%type;
column_sql varchar2(500);
record_rownum number(1);
begin
open tablename_list for select table_name from user_tables;
loop
fetch tablename_list into tablename;
exit when tablename_list%notfound;
column_sql := 'select column_name from user_tab_columns where table_name='''||tablename||'''';
open fieldname_list for column_sql;
loop
fetch fieldname_list into fieldname;
exit when fieldname_list%notfound;
column_sql := 'select rownum from '||tablename||' where '||fieldname||' like ''%test%'' and rownum=1';
open foundtable for column_sql;
loop
fetch foundtable into record_rownum;
exit when foundtable%notfound;
dbms_output.put_line(tablename||' '||fieldname);
end loop;
close foundtable;
end loop;
close fieldname_list;
end loop;
close tablename_list;
end;编译过程无错
运行时出现以下错误,请问怎么解决?ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "JOOYOO.PROCEDURE1", line 21
ORA-06512: at line 2还有就是上面的代码我通动手动建库和表,查询,正常,但如果一张表中有两或两个以上你查询的关键字,他只显示最开始找到的,能否显示所有呢?
解决方案 »
- OCI8绑定输入变量的问题
- 请教这样的SQL怎么写
- oracle把一个gb2312编码的数据库倒入到utf-8的数据库怎么做
- 用什么工具Oracle 8/9i 可以导出数据库到excel表?
- table 和 Nested Table到底有什么区别?
- oracle 的pro c中的问题
- 讨论:有关各种数据库的优缺点(主要讨论Sybase、Oracle和Microsoft三大公司的数据库产品)--------------参与有分
- 急!关于long row字段问题?
- 关于BLOB插入的问题,请帮忙看看为什么出错?
- 如何能够得出如下结果
- 在lomboz eclipse中连接 oracle 11G?
- 求助!求助
column_sql := 'select column_name from user_tab_columns where table_name=''' || tablename ||
''' and data_type in (''CHAR'',''VARCHAR2'')';
如果有查询指定其他用户对象,需要相应的权限才行
指定特定值,只需要存储过程增加一个入口参数
如PROCEDURE PROCEDURE1(i_searchStr varchar2)下面这句修改一下就可以
column_sql := 'select rownum from '||tablename||
' where '||fieldname||' like ''%'||i_searchStr||'%'' and rownum<2';
按照你的方法,修改后仍出现以下错误
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "JOOYOO.PROCEDURE1", line 20
ORA-06512: at line 6这出错在那里啊?
CREATE OR REPLACE PROCEDURE AllSearch(i_searchStr VARCHAR2) AS
sqlstr VARCHAR2 := '';
cnt PLS_INTEGER;
BEGIN
FOR c_tab IN (SELECT * FROM user_tables) LOOP
FOR c_col IN (SELECT * FROM user_tab_columns t
WHERE t.TABLE_NAME = c_tab.table_name AND t.DATA_TYPE IN ('CHAR', 'VARCHAR2')) LOOP
sqlstr := 'select count(1) from ' || c_tab.table_name || ' where ' || c_col.column_name ||
' like ''%' || i_searchStr || '%'' and rownum<2';
EXECUTE IMMEDIATE sqlstr
INTO cnt;
IF cnt = 1 THEN
dbms_output.put_line('table:' || c_tab.table_name || ',col:' || c_col.column_name);
END IF;
END LOOP;
END LOOP;
END AllSearch;执行
SQL> exec AllSearch('SMITH');
table:EMP,col:ENAME
PL/SQL procedure successfully completed
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB 麻烦大家帮我看看下面的代码,也是实现全库搜索的功能,但查询后没有反应,高手些能否看看是什么原因呢?
create or replace procedure search(v_colname in varchar2, v_find in varchar2) as
v_sql varchar2(2000);
v_result varchar2(1000);
v_cursor number;
v_stat number;
begin
dbms_output.put_line('表名,列名,结果');
for a in (select table_name,column_name from user_tab_columns where column_name = v_colname) loop
v_cursor := dbms_sql.open_cursor;
v_sql := 'select ' || v_colname || ' from ' || a.table_name || ' where ' || v_colname || ' like ''%' || v_find || '%''';
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
dbms_sql.define_column(v_cursor,1,v_result,1000);
v_stat := dbms_sql.execute(v_cursor);
loop exit when dbms_sql.fetch_rows(v_cursor)<=0;
dbms_sql.column_value(v_cursor,1,v_result);
dbms_output.put_line(a.table_name||','||a.column_name||','||v_result);
end loop;
dbms_sql.close_cursor(v_cursor);
end loop;
end search;
begin search('...','...'); end
查看是什么情况导致的,因为在我这儿 oracle 10.2.0.1.0 OK的
在需要搜索的表中先判断字段的内型,查找相对应的关键字呢?
REGEXP_LIKE(filedName, 'test');