create or replace procedure smsfilter(phone in varchar2,tmpCode in varchar2) as
type array_str is table of varchar2(20) index by binary_integer;
id_str number;
code_str varchar2(50);
noList array_str;
tmpList array_str;
no_str varchar2(4000) default phone;
tmp_str varchar2(4000) default tmpCode;
begin
for i in 0 .. length(phone)-length(replace(phone,',','')) loop
if (instr(no_str,',')) = 0 then
noList(i) := no_str;
else
noList(i) := substr(no_str,0,instr(no_str,',')-1);
end if;
no_str := substr(no_str,instr(no_str,',')+1);
select nvl(max(t.subs_id),'1.11') into id_str from cus_subscriber t where t.phoneno = noList(i);
if (id_str'1.11') then
tmp_str := tmpCode;
for i in 0 .. length(tmpCode)-length(replace(tmpCode,',','')) loop
if (instr(tmp_str,',')) = 0 then
tmpList(i) := tmp_str;
else
tmpList(i) := substr(tmp_str,0,instr(tmp_str,',')-1);
end if;
select nvl(max(subs_id),'1.11') into code_str from biz_subs_sms_filter where
tmp_code = tmpList(i) and subs_id = id_str;
if code_str = '1.11' then
insert into biz_subs_sms_filter values(id_str,tmpList(i));
---dbms_output.put_line('insert new record succedd!');
end if;
tmp_str := substr(tmp_str,instr(tmp_str,',')+1);
end loop;
end if;
end loop;
end smsfilter;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
String sql = "{call smsfilter(?,?}";
stmt = con.prepareCall(sql);
stmt.setString(1, "15302500000"
;
stmt.setString(2,"1"
;
stmt.execute();
程序调用出错：
Exception in thread "main" java.sql.SQLException: ORA-06550: 第 1 行, 第 22 列:
PLS-00103: 出现符号 ";"在需要下列之一时：
. ( ) , * @ % & | = - +
at in is mod remainder not range rem => ..
or != or ~= >=and or like LIKE2_ LIKE4_ LIKEC_
between || indicator multiset member SUBMULTISET_
符号 "
" 被替换为 ";" 后继续。
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)
at Test.main(Test.java:22)
|
