我在论坛朋友的帮助下,用下面的语句建了一个触发器,插入一条记录时,第15行报ORA-00905:缺失关键字的错误,不知道是什么原因?
能解答一下么?谢谢啦!!
CREATE OR REPLACE TRIGGER tab3102_I_TRIG AFTER INSERT ON tab3102
referencing new as new old as old
for each row
declare
v_targetdocid tab3102.targetdocid%type;
f_tbname varchar2(20);
l_storeid varchar2(10);
temp_sql varchar2(50);
seq_id number(20);
begin
v_targetdocid:=:new.targetdocid;
f_tbname:=SUBSTR(v_targetdocid,1,INSTR(v_targetdocid,'.')-1);
f_tbname:='tab'||f_tbname;
temp_sql:='select '||f_tbname||'$_SEQ.NEXTVAL into seq_id from dual';
execute immediate temp_sql;
--exception
f_tbname:=f_tbname||'$_temp';
l_storeid:=SUBSTR(v_targetdocid,INSTR(v_targetdocid,'.')+1,length(v_targetdocid));
temp_sql:='insert into '||f_tbname||' values('||to_char(seq_id)||','||l_storeid||',1,0)';
execute immediate temp_sql;
--exception
end tab3102_I_TRIG;目的:从targetdocid字段分离成两部分,如11.2345,那么把2345写到tab11$_temp中去
能解答一下么?谢谢啦!!
CREATE OR REPLACE TRIGGER tab3102_I_TRIG AFTER INSERT ON tab3102
referencing new as new old as old
for each row
declare
v_targetdocid tab3102.targetdocid%type;
f_tbname varchar2(20);
l_storeid varchar2(10);
temp_sql varchar2(50);
seq_id number(20);
begin
v_targetdocid:=:new.targetdocid;
f_tbname:=SUBSTR(v_targetdocid,1,INSTR(v_targetdocid,'.')-1);
f_tbname:='tab'||f_tbname;
temp_sql:='select '||f_tbname||'$_SEQ.NEXTVAL into seq_id from dual';
execute immediate temp_sql;
--exception
f_tbname:=f_tbname||'$_temp';
l_storeid:=SUBSTR(v_targetdocid,INSTR(v_targetdocid,'.')+1,length(v_targetdocid));
temp_sql:='insert into '||f_tbname||' values('||to_char(seq_id)||','||l_storeid||',1,0)';
execute immediate temp_sql;
--exception
end tab3102_I_TRIG;目的:从targetdocid字段分离成两部分,如11.2345,那么把2345写到tab11$_temp中去
解决方案 »
- 请教,以下语句我在一个示例中执行不会有问题,但是在两个库中就会报错,是不是左连的问题?是的话,如何解决?
- Oracle 9.0 下载地址
- 在PL/SQL 中运行这条语句报错exec granttoqryrole(tablename),请问是什么原因
- Oracle异常处理
- 使用SQL plus至少要打开哪些服务?
- 错误:ORA-06553: PLS-213: STANDARD 包不可存取
- 如何实现?
- 如何将表中字段为blob属性的字段值读出来(在plus中)
- 散分了!!!!二进制的形式保存用哪个字段?急在线等。!
- 如何生成select sys_guid() from dual的多个值并返回给调用者
- 导入表空间问题
- 重新建了分区表,索引坏了,有没有什么命令修复索引?
v_sql:='select xxx into x from dual';
execute immediate v_sql;
这个语法错误。应为:
v_sql:='select xxx from dual';
execute immediate v_sql into x;2.
temp_sql:='select '||f_tbname||'$_SEQ.NEXTVAL into seq_id from dual';
这个语法不存在。应该先定义变量xxx,再select $_SEQ.NEXTVAL into xxx from dual;
然后再temp_sql:='select '||f_tbname||xxx||' from dual';之类3.
如果你的f_tbname是字符,那么在构建动态SQL时要加引号:select '||''''||f_tbname.......
...
seq_id varchar2(10);
begin
v_targetdocid:=:new.targetdocid;
f_tbname:=SUBSTR(v_targetdocid,1,INSTR(v_targetdocid,'.')-1);
f_tbname:='tab'||f_tbname||'$_temp';
seq_id:=SUBSTR(v_targetdocid,INSTR(v_targetdocid,'.')+1,length(v_targetdocid));
l_storeid:=SUBSTR(v_targetdocid,INSTR(v_targetdocid,'.')+1,length(v_targetdocid));
temp_sql:='select seq_id||','||l_storeid||',1,0 into '||f_tbname;
execute immediate temp_sql;
...
举个例子,看是不是这个意思:
SQL> create sequence seq1 start with 1;序列已创建。SQL> select seq1.nextval from dual; NEXTVAL
----------
1SQL> declare
2 cnt number;
3 v_sql varchar2(100);
4 aaa varchar2(10);
5 begin
6 aaa:='seq';
7 v_sql:='select '||aaa||'1.nextval into cnt from dual';
8 execute immediate v_sql;
9 dbms_output.put_line(cnt);
10 end;
11 /
declare
*
第 1 行出现错误:
ORA-00905: 缺失关键字
ORA-06512: 在 line 8
SQL> declare
2 cnt number;
3 v_sql varchar2(100);
4 aaa varchar2(10);
5 begin
6 aaa:='seq';
7 --v_sql:='select '||aaa||'1.nextval into cnt from dual';
8 v_sql:='select '||aaa||'1.nextval from dual';
9 execute immediate v_sql into cnt;
10 dbms_output.put_line(cnt);
11 end;
12 /
2PL/SQL 过程已成功完成。SQL>