create or replace procedure kingway.PIS_sequence2(tablename varchar2,
columnname varchar2) as
strsql varchar2(1000);
begin
strsql := 'create sequence kingway.seq_' || tablename ||
' minvalue 1 maxvalue
999999 start with 1 increment by 1 nocache';
execute immediate strsql;
strsql := 'create or replace trigger kingway.trg_' || tablename || ' before insert on
' || tablename || ' for each row
begin SELECT CONCAT(
TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),
LPAD(seq_' || tablename ||'.nextval,6,'0'))
into :new.' || columnname || ' FROM dual;end;';
execute immediate strsql;
end;
执行就报出
PROCEDURE KINGWAY.PIS_SEQUENCE2 编译错误错误:PLS-00103: Encountered the symbol "DDMMYYYYHHMISS" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol ". was inserted before "DDMMYYYYHHMISS" to continue.
行:12
文本:TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),错误:PLS-00103: Encountered the symbol "0" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol ". was inserted before "0" to continue.
行:13
文本:LPAD(seq_' || tablename ||'.nextval,6,'0'))
而分组实验中
1 SQL> SELECT CONCAT(TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),LPAD(seq_transorder.nextval,6,'0')) FROM dual;
正常执行如下CONCAT(TO_CHAR(SYSDATE,'DDMMYY
------------------------------
290720060924180000022 如下过程则也可以安全编译通过
create or replace procedure system.pr_CreateIdentityColumn(tablename varchar2,
columnname varchar2) as
strsql varchar2(1000);
begin
strsql := 'create sequence seq_' || tablename ||
' minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache';
execute immediate strsql;
strsql := 'create or replace trigger trg_' || tablename ||
' before insert on ' || tablename ||
' for each row begin select seq_' || tablename ||
'.nextval into :new.' || columnname || ' from dual; end;';
execute immediate strsql;
end;
痛苦啊,大虾帮助!!
columnname varchar2) as
strsql varchar2(1000);
begin
strsql := 'create sequence kingway.seq_' || tablename ||
' minvalue 1 maxvalue
999999 start with 1 increment by 1 nocache';
execute immediate strsql;
strsql := 'create or replace trigger kingway.trg_' || tablename || ' before insert on
' || tablename || ' for each row
begin SELECT CONCAT(
TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),
LPAD(seq_' || tablename ||'.nextval,6,'0'))
into :new.' || columnname || ' FROM dual;end;';
execute immediate strsql;
end;
执行就报出
PROCEDURE KINGWAY.PIS_SEQUENCE2 编译错误错误:PLS-00103: Encountered the symbol "DDMMYYYYHHMISS" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol ". was inserted before "DDMMYYYYHHMISS" to continue.
行:12
文本:TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),错误:PLS-00103: Encountered the symbol "0" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol ". was inserted before "0" to continue.
行:13
文本:LPAD(seq_' || tablename ||'.nextval,6,'0'))
而分组实验中
1 SQL> SELECT CONCAT(TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),LPAD(seq_transorder.nextval,6,'0')) FROM dual;
正常执行如下CONCAT(TO_CHAR(SYSDATE,'DDMMYY
------------------------------
290720060924180000022 如下过程则也可以安全编译通过
create or replace procedure system.pr_CreateIdentityColumn(tablename varchar2,
columnname varchar2) as
strsql varchar2(1000);
begin
strsql := 'create sequence seq_' || tablename ||
' minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache';
execute immediate strsql;
strsql := 'create or replace trigger trg_' || tablename ||
' before insert on ' || tablename ||
' for each row begin select seq_' || tablename ||
'.nextval into :new.' || columnname || ' from dual; end;';
execute immediate strsql;
end;
痛苦啊,大虾帮助!!
解决方案 »
- 监听程序当前无法识别连接描述符中所给出的sid,services进程只有一个
- 在bat文件中 執行 從一個數據庫 導出數據 然后導入另一個數據庫 怎么寫??????????????
- 求一条SQL语句
- 求个sql语句!
- 文件已经读入了BFILE , 再怎么将它读出来???????????????
- 请问:关于数据库授权的问题(急)
- 没有监听器?help
- 又一个数据导出失败的问题!!!!!!!!!
- 今天老板给一个任务,我感觉天快塌下来了。(1000分相送,不够再加)
- sybase中的数据转入oracle中
- 数据库SQL 语句 sum() 和count() 查询速度问题???????
- 请问oracle数据库简体繁体共存的问题?
'select ''12''||''bbb'' from dual'
用两个代替一个。
show error;
这个命令来看看具体哪个地方错。
SELECT CONCAT(TO_CHAR(SYSDATE, "DDMMYYYYHHMISS"),LPAD(seq_' || tablename || '.nextval,6,"0")) 过程编译通过,但仍然有隐含错误:也就是执行
exec PIS_sequence2('sdspdept','deptid');后,创建的触发器kingway.trg_sdspdept
代码如下:
create or replace trigger trg_sdspdept before insert on
TransSeq for each row
begin
SELECT CONCAT(TO_CHAR(SYSDATE, "DDMMYYYYHHMISS"),LPAD(seq_TransSeq.nextval,6,"0"))
into :new.SeqNo FROM dual;end;
编译报错:
TRIGGER KINGWAY.TRG_TRANSSEQ 编译错误错误:PL/SQL: ORA-00904: "0": invalid identifier
行:4
文本:SELECT CONCAT(TO_CHAR(SYSDATE, "DDMMYYYYHHMISS"),LPAD(seq_TransSeq.nextval,6,"0"))错误:PL/SQL: SQL Statement ignored
行:4
文本:SELECT CONCAT(TO_CHAR(SYSDATE, "DDMMYYYYHHMISS"),LPAD(seq_TransSeq.nextval,6,"0"))//////////////////////////////////then what is wrong :(PIS_sequence2:代码如下
create or replace procedure PIS_sequence2(tablename varchar2,
columnname varchar2) as
strsql varchar2(1000);
begin
strsql := 'create sequence kingway.seq_'|| tablename ||
' minvalue 1 maxvalue
999999 start with 1 increment by 1 nocache';
execute immediate strsql;
strsql := 'create or replace trigger kingway.trg_' || tablename || ' before insert on
' || tablename || ' for each row
begin
SELECT CONCAT(TO_CHAR(SYSDATE, "DDMMYYYYHHMISS"),LPAD(seq_' || tablename || '.nextval,6,"0"))
into :new.' || columnname || ' FROM dual;end;';
execute immediate strsql;
end;
在oracle中,你不可以采用双引号,也用单引号
SELECT CONCAT(TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS'),LPAD(seq_TransSeq.nextval,6,'0'))
SELECT CONCAT(TO_CHAR(SYSDATE, ''DDMMYYYYHHMISS''),LPAD(seq_' || tablename || '.nextval,6,''0'')) 我说的清清楚楚明明白白,后面N位老兄也copy,paster了好几次,你还是把两个单引号写成了一个双引号-_-!
1.在oracle的存储过程中可以直接执行sql语句,这时的书写规则和sqlplus中完全相同。
如:
begin
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
end;2.在oracle的存储过程中通过“execute immediate sql_string;”的方式执行拼接好的sql语句,这时在拼接sql语句时要注意对单引号(')的转义,转义字符为单引号('),这就是为什么要用两个单引号代替一个单引号的原因。
如:
declare
strsql varchar(4000);
begin
strsql := 'select to_char(sysdate,''YYYY-MM-DD HH24:MI:SS'') from dual';
execute immediate strsql;
end;