我想写一个简单的动态sql但一直提示我有错误,请高手指点一下;MM_SQL:='select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual'; execute immediate MM_SQL into M_DABH;其中M_GLBM是变量名
MM_SQL:='select M_GLBM,lpad(veh_admin.veh_dabh_M_GLBM.nextval,6,0) from dual'; execute immediate MM_SQL into M_DABH;
MM_SQL:='select '||M_GLBM||' || lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual'; execute immediate MM_SQL into M_DABH; 我猜這樣
恩 这个语句 'select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual'之间是,还是ll自己确认下
--测试下: DECLARE MM_SQL VARCHAR2(100); M_GLBM VARCHAR2(100):='abc'; BEGIN MM_SQL:='select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual'; Dbms_Output.put_line(MM_SQL); END;--打印结果: select abclpad(veh_admin.veh_dabh_abc.nextval,6,0) from dual ;显然这个结果不是你想要的执行SQL吧 execute immediate MM_SQL into M_DABH; 就会报错 ,根本没有这个函数abclpad(),除非你自定义了
M_GLBM是变量名,还是字段名,是字段名上面的就是对的,还有execute immediate MM_SQL into M_DABH;你select出来的是什么,是个序列号,还是某个字段和序列号?
M_GLBM是要跟右边号码的连接起来 举个例子出来 MM_SQL:='select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual';M_DABH 什么类型的
谢谢楼上的,经过反复Dbms_Output.put_line调试已经搞定了
谢谢楼上的,经过反复Dbms_Output.put_line调试已经搞定了
MM_SQL:='select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual;'; execute immediate MM_SQL into M_DABH;以上是我最终成功的语法 --打印结果: select 'abc'||lpad(veh_admin.veh_dabh_abc.nextval,6,0) from dual ;但这个语句放到存储过程中调试时 执行到execute immediate MM_SQL into M_DABH; 时报ORA-00911:无效字符 错误???很是郁闷啊
--不要你拼接SQL的最后一个分号; create sequence veh_dabh_abc start with 1 increment by 1 ; CREATE OR REPLACE PROCEDURE test_seq is MM_SQL VARCHAR2(100); C_GLBM VARCHAR2(100):='abc'; M_DABH VARCHAR2(100); BEGIN MM_SQL:='select '||''''||C_GLBM||'''||lpad(veh_dabh_'||C_GLBM||'.nextval,6,0) from dual'; --注意这里动态sql最后不要加分号 execute immediate MM_SQL into M_DABH; Dbms_Output.put_line(M_DABH); END;BEGIN test_seq; END;389 PL/SQL block, executed in 0 sec. abc000002 Total execution time 0 sec.
'select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual;'--多了个分号
你没明白拼接 --这样 MM_SQL:='select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual'; execute immediate MM_SQL into M_DABH;
还有你的结果是字符型的 变量M_DABH是否是的
建议使用pl/sql developer进行单步调试!
使用pl/sql developer进行单步调试!execute immediate MM_SQL into M_DABH;这一句时提示表和视图不存在???dual 怎么会不存在呢???况且我将生成的动态SQL在PL/SQL里执行时是成功的呀???
不知道你有没有仔细看我12楼的测试,跟你说了,你多了个分号!不知道你改没有? 'select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual;'--多了个分号
加个sys.dual试过了,依然是ORA-00942:表和视图不存在
trace结果显示: TKPROF: Release 10.2.0.3.0 - Production on 星期二 11月 16 11:36:43 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Trace file: D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1472.trc Sort options: default******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************The following statement encountered a error during parse:select '622900'||LPAD(VEH_ADMIN.VEH_DABH_622900.NEXTVAL,6,0) FROM DUA--居然少了一个LError encountered: ORA-00942
估计是你拼SQL错了
execute immediate MM_SQL into M_DABH;
execute immediate MM_SQL into M_DABH;
我猜這樣
'select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual'之间是,还是ll自己确认下
DECLARE
MM_SQL VARCHAR2(100);
M_GLBM VARCHAR2(100):='abc';
BEGIN
MM_SQL:='select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual';
Dbms_Output.put_line(MM_SQL);
END;--打印结果:
select abclpad(veh_admin.veh_dabh_abc.nextval,6,0) from dual ;显然这个结果不是你想要的执行SQL吧
execute immediate MM_SQL into M_DABH; 就会报错 ,根本没有这个函数abclpad(),除非你自定义了
MM_SQL:='select '||M_GLBM||'lpad(veh_admin.veh_dabh_'||M_GLBM||'.nextval,6,0) from dual';M_DABH 什么类型的
MM_SQL:='select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual;';
execute immediate MM_SQL into M_DABH;以上是我最终成功的语法
--打印结果:
select 'abc'||lpad(veh_admin.veh_dabh_abc.nextval,6,0) from dual ;但这个语句放到存储过程中调试时 执行到execute immediate MM_SQL into M_DABH;
时报ORA-00911:无效字符 错误???很是郁闷啊
create sequence veh_dabh_abc
start with 1 increment by 1 ;
CREATE OR REPLACE PROCEDURE test_seq
is
MM_SQL VARCHAR2(100);
C_GLBM VARCHAR2(100):='abc';
M_DABH VARCHAR2(100);
BEGIN
MM_SQL:='select '||''''||C_GLBM||'''||lpad(veh_dabh_'||C_GLBM||'.nextval,6,0) from dual'; --注意这里动态sql最后不要加分号
execute immediate MM_SQL into M_DABH;
Dbms_Output.put_line(M_DABH);
END;BEGIN
test_seq;
END;389 PL/SQL block, executed in 0 sec.
abc000002
Total execution time 0 sec.
'select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual;'--多了个分号
--这样
MM_SQL:='select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual';
execute immediate MM_SQL into M_DABH;
使用pl/sql developer进行单步调试!execute immediate MM_SQL into M_DABH;这一句时提示表和视图不存在???dual 怎么会不存在呢???况且我将生成的动态SQL在PL/SQL里执行时是成功的呀???
'select '||''''||C_GLBM||'''||lpad(veh_admin.veh_dabh_'||C_GLBM||'.nextval,6,0) from dual;'--多了个分号
加个sys.dual试过了,依然是ORA-00942:表和视图不存在
TKPROF: Release 10.2.0.3.0 - Production on 星期二 11月 16 11:36:43 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Trace file: D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1472.trc
Sort options: default********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************The following statement encountered a error during parse:select '622900'||LPAD(VEH_ADMIN.VEH_DABH_622900.NEXTVAL,6,0) FROM DUA --居然少了一个LError encountered: ORA-00942