存储过程编译没错误。。调试也通过了。。调用时候就报错,ORA-06550: 第 1 行, 第 60 列:
PLS-00103: 出现符号 ":"在需要下列之一时:
( - + case mod new not null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current exists max min prior sql
stddev sum variance execute forall merge time timestamp
interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<一个带有字符集说明的可带引号的字符串文字>
<一个可带引号的 SQL 字符串>
符号 "(在 ":" 继续之前已插入。
ORA-06550: 第 1 行, 第 76 列:
PLS-00103: 出现符号 ":"在需要下列之一时:
( - + case mod new not null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current exists max min prior sql
stddev sum variance execute forall merge t
ORA-06550: 第 1 行, 第 95 列:
PLS-00103: 出现符号 ":"在需要下列之一时:
( - + case mod new not null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current exists max min prio 一下是存储过程代码:CREATE OR REPLACE PACKAGE BODY pkg_reportstorebanbao AS
PROCEDURE PROC_ReportStoreBanBao(stime in varchar2,
etime in varchar2,
deviceID in varchar2,
pageIndex in int,
pageSize int,
pageCount out int,
p_list OUT myrctype
)
IS
tempstoreNum numeric(18, 2);
v_sql varchar(500);
v_startPage int ;
v_endPage int;
BEGIN begin
select NVL(sum(DECODE(g.gl_status,
11,s.stg_weight * s.stg_quantity,
12,(s.stg_weight * s.stg_quantity) * -1,
13,s.stg_weight * s.stg_quantity,
21,s.stg_weight * s.stg_quantity,
22,(s.stg_weight * s.stg_quantity) * -1,
31,s.stg_weight * s.stg_quantity,
32,(s.stg_weight * s.stg_quantity) * -1,
33,s.stg_weight * s.stg_quantity,0)),0)
into tempstoreNum
from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(g.gl_time, 'YYYY-MM-DD') < stime;
exception
when NO_DATA_FOUND then
tempstoreNum := 0;
end;
v_startPage:= pageSize * (pageIndex - 1) + 1;
v_endPage:= pageSize * pageIndex;
INSERT INTO ReportInOutBanBao(id, inTime, import, goodCode, USERID, UWITH_ID, stat)
select rowindex,inTIme,import,gl_stcode,gl_opr,gl_with,gl_status from (
select rownum rowindex, gl_time as inTIme,
(s.STG_WEIGHT * s.STG_QUANTITY) as import,
g.gl_stcode, g.gl_opr, g.gl_with, g.gl_status
from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(gl_time, 'YYYY-MM-DD') >= stime
and to_char(gl_time, 'YYYY-MM-DD') <= etime
and gl_deviceid = deviceID
order by gl_time)
where rowindex BETWEEN v_startPage and v_endPage; --更新出库数量 update ReportInOutBanBao set export = import where stat IN (12, 22, 32);
update ReportInOutBanBao set import = 0 where stat IN (12, 22, 32); --更新交接人和收物人
update ReportInOutBanBao
set username = (select u.user_name
from Userinfo u
where ReportInOutBanBao.USERID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.USERID = u.user_id); update ReportInOutBanBao
set Uwith = (select u.user_name
from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id) ;
update reportInOutBanBao
set storeNum = (select tempstoreNum + sum(import - export) as num
from reportInOutBanBao r
where r.id <= reportInOutBanBao.id)
where exists(select 1 from reportInOutBanBao r
where r.id <= reportInOutBanBao.id);
OPEN p_list FOR select * from reportInOutBanBao order by id;
v_sql :=' select count(1) as sumCount from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(gl_time, ''YYYY-MM-DD'') >=''' || stime|| '''
and to_char(gl_time, ''YYYY-MM-DD'') <= '''|| etime || '''
and gl_deviceid ='''||deviceID||'''';
execute immediate v_sql into pageCount;END PROC_ReportStoreBanBao;
END pkg_reportstorebanbao;
PLS-00103: 出现符号 ":"在需要下列之一时:
( - + case mod new not null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current exists max min prior sql
stddev sum variance execute forall merge time timestamp
interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<一个带有字符集说明的可带引号的字符串文字>
<一个可带引号的 SQL 字符串>
符号 "(在 ":" 继续之前已插入。
ORA-06550: 第 1 行, 第 76 列:
PLS-00103: 出现符号 ":"在需要下列之一时:
( - + case mod new not null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current exists max min prior sql
stddev sum variance execute forall merge t
ORA-06550: 第 1 行, 第 95 列:
PLS-00103: 出现符号 ":"在需要下列之一时:
( - + case mod new not null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current exists max min prio 一下是存储过程代码:CREATE OR REPLACE PACKAGE BODY pkg_reportstorebanbao AS
PROCEDURE PROC_ReportStoreBanBao(stime in varchar2,
etime in varchar2,
deviceID in varchar2,
pageIndex in int,
pageSize int,
pageCount out int,
p_list OUT myrctype
)
IS
tempstoreNum numeric(18, 2);
v_sql varchar(500);
v_startPage int ;
v_endPage int;
BEGIN begin
select NVL(sum(DECODE(g.gl_status,
11,s.stg_weight * s.stg_quantity,
12,(s.stg_weight * s.stg_quantity) * -1,
13,s.stg_weight * s.stg_quantity,
21,s.stg_weight * s.stg_quantity,
22,(s.stg_weight * s.stg_quantity) * -1,
31,s.stg_weight * s.stg_quantity,
32,(s.stg_weight * s.stg_quantity) * -1,
33,s.stg_weight * s.stg_quantity,0)),0)
into tempstoreNum
from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(g.gl_time, 'YYYY-MM-DD') < stime;
exception
when NO_DATA_FOUND then
tempstoreNum := 0;
end;
v_startPage:= pageSize * (pageIndex - 1) + 1;
v_endPage:= pageSize * pageIndex;
INSERT INTO ReportInOutBanBao(id, inTime, import, goodCode, USERID, UWITH_ID, stat)
select rowindex,inTIme,import,gl_stcode,gl_opr,gl_with,gl_status from (
select rownum rowindex, gl_time as inTIme,
(s.STG_WEIGHT * s.STG_QUANTITY) as import,
g.gl_stcode, g.gl_opr, g.gl_with, g.gl_status
from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(gl_time, 'YYYY-MM-DD') >= stime
and to_char(gl_time, 'YYYY-MM-DD') <= etime
and gl_deviceid = deviceID
order by gl_time)
where rowindex BETWEEN v_startPage and v_endPage; --更新出库数量 update ReportInOutBanBao set export = import where stat IN (12, 22, 32);
update ReportInOutBanBao set import = 0 where stat IN (12, 22, 32); --更新交接人和收物人
update ReportInOutBanBao
set username = (select u.user_name
from Userinfo u
where ReportInOutBanBao.USERID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.USERID = u.user_id); update ReportInOutBanBao
set Uwith = (select u.user_name
from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id) ;
update reportInOutBanBao
set storeNum = (select tempstoreNum + sum(import - export) as num
from reportInOutBanBao r
where r.id <= reportInOutBanBao.id)
where exists(select 1 from reportInOutBanBao r
where r.id <= reportInOutBanBao.id);
OPEN p_list FOR select * from reportInOutBanBao order by id;
v_sql :=' select count(1) as sumCount from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(gl_time, ''YYYY-MM-DD'') >=''' || stime|| '''
and to_char(gl_time, ''YYYY-MM-DD'') <= '''|| etime || '''
and gl_deviceid ='''||deviceID||'''';
execute immediate v_sql into pageCount;END PROC_ReportStoreBanBao;
END pkg_reportstorebanbao;
--更新交接人和收物人
update ReportInOutBanBao
set username = (select u.user_name
from Userinfo u
where ReportInOutBanBao.USERID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.USERID = u.user_id); update ReportInOutBanBao
set Uwith = (select u.user_name
from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id) ;
update reportInOutBanBao
set storeNum = (select tempstoreNum + sum(import - export) as num
from reportInOutBanBao r
where r.id <= reportInOutBanBao.id)
where exists(select 1 from reportInOutBanBao r
where r.id <= reportInOutBanBao.id);要不改成:
--更新交接人和收物人
update ReportInOutBanBao
set username = (select max(u.user_name)
from Userinfo u
where ReportInOutBanBao.USERID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.USERID = u.user_id); update ReportInOutBanBao
set Uwith = (select max(u.user_name)
from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id)
where exists( select 1 from Userinfo u
where ReportInOutBanBao.UWITH_ID = u.user_id) ;
update reportInOutBanBao
set storeNum = (select tempstoreNum + sum(import - export) as num
from reportInOutBanBao r
where r.id <= reportInOutBanBao.id)
where exists(select 1 from reportInOutBanBao r
where r.id <= reportInOutBanBao.id);
试试看
v_sql :=' select count(1) as sumCount from goodlog g, STORAGE s
where g.gl_stcode = s.stg_stcode(+)
and to_char(gl_time, ''YYYY-MM-DD'') >=''' || stime|| '''
and to_char(gl_time, ''YYYY-MM-DD'') <= '''|| etime || '''
and gl_deviceid ='''||deviceID||''''; execute immediate v_sql into pageCount;应该是这里的错。
where g.gl_stcode = s.stg_stcode(+)
and to_char(gl_time, 'YYYY-MM-DD') >= stime
and to_char(gl_time, 'YYYY-MM-DD') <= etime
and gl_deviceid = deviceID ;可是 还是报错还是那个错误
真是郁闷了 oracle 真是难搞
你是怎样在程序里调用这个package里的方法的呀。 贴出来看看。
OracleParameter[] parameters = {
new OracleParameter(":stime", OracleType.VarChar,100),
new OracleParameter(":etme", OracleType.VarChar,100),
new OracleParameter(":deviceID", OracleType.VarChar,100),
new OracleParameter(":pageIndex", OracleType.Int32),
new OracleParameter(":pageSize", OracleType.Int32),
new OracleParameter(":pageCount", OracleType.Int32),
new OracleParameter(":p_list", OracleType.Cursor),
};
parameters[0].Value = stime;
parameters[1].Value = etime;
parameters[2].Value = deviceID;
parameters[3].Value = pageIndex;
parameters[4].Value = pageSize;
parameters[5].Direction = ParameterDirection.Output;
parameters[6].Direction = ParameterDirection.Output;
return DbHelperOra.RunProcedure("pkg_ReportStoreBanBao.PROC_ReportStoreBanBao", parameters, "reportInOutBanBao");
new OracleParameter(":stime", OracleType.VarChar,100),
new OracleParameter(":etme", OracleType.VarChar,100),
new OracleParameter(":deviceID", OracleType.VarChar,100),
new OracleParameter(":pageIndex", OracleType.Int32),
new OracleParameter(":pageSize", OracleType.Int32),
new OracleParameter(":pageCount", OracleType.Int32),
new OracleParameter(":p_list", OracleType.Cursor),
};
parameters[0].Value = stime;
parameters[1].Value = etime;
parameters[2].Value = deviceID;
parameters[3].Value = pageIndex;
parameters[4].Value = pageSize;
parameters[5].Direction = ParameterDirection.Output;
parameters[6].Direction = ParameterDirection.Output;
return DbHelperOra.RunProcedure("pkg_ReportStoreBanBao.PROC_ReportStoreBanBao", parameters, "reportInOutBanBao");