ORA-00933运行时抛出错误

Question

我正在尝试查询dba_tables，如果表"SPLIT_EXT_INFO"不存在，我得到正确的输出，如果表"SPLIT_EXT_INFO">

SET serveroutput ON
DECLARE
t_cnt         NUMBER;
v_schema_name VARCHAR2(40) := 'DBA';
v_table_name  VARCHAR2(40) := 'SPLIT_EXT_INFO';
refcur        SYS_REFCURSOR;
split_mapper  VARCHAR2(40);
info_tab      VARCHAR2(40);
tracker       VARCHAR2(40);
BEGIN
SELECT Count(1)
INTO   t_cnt
FROM   dba_tables
WHERE  owner = v_schema_name
AND table_name = v_table_name;
dbms_output.Put_line(t_cnt);
IF t_cnt = 0 THEN
dbms_output.Put_line('NO_SPLIT_TAB');
ELSE
OPEN refcur FOR 'SELECT  split_mapper, info_tab, tracker FROM '
||v_schema_name
||'.'
||v_table_name
|| 'where nodename = ''host1.world.com''';
LOOP
FETCH refcur INTO split_mapper,info_tab,tracker;
EXIT WHEN refcur%NOTFOUND;
dbms_output.Put_line(split_mapper
||':'
||info_tab
||':'
||tracker);
END LOOP;
IF refcur%rowcount = 0 THEN
dbms_output.Put_line('NO_SPLIT_ENTRY');
END IF;
END IF;
CLOSE refcur;
END;
/ 

错误:

1
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 21

谁能帮帮我，谢谢!

的问候Kannan

Answer 1

WHERE子句连接前撇号位置错误

your code: || 'where nodename = ''host1.world.com''';
right is : ||' where nodename = ''host1.world.com''';

在您的版本中，sql将看起来像

select ... FROM tableWHERE nomename = ...

表名和WHERE之间缺少空格符号导致这个问题