执行过程时出现错误 PLS-00103

Question

我 http://sqlines.com/online 使用过这个网站(因为我以前从未使用过oracle... 将 procudere 从 sqlServer 转换为 oracle procudere 我正在OracleSqlDevelopers中执行procudere。

create or replace procedure GET_ITEMS_PROC(p_CODE varchar2(14), p_CODE_SUPPLIER varchar2(20),p_SELECT1 varchar2(4),p_SELECT2 varchar2(4)
,p_SELECT3 varchar2(4),p_SELECT4 varchar2(4),
p_SELECT5 varchar2(4),p_SELECT6 varchar2(4),p_SELECT7 varchar2(4)
,p_SELECT8 varchar2(4),p_SELECT9 varchar2(4),p_SELECT10 varchar2(4), cur out sys_refcursor)
as
begin
open cur for select * from ITEMS where ( p_CODE IS NULL OR CODE=p_CODE )and (p_CODE_SUPPLIER IS NULL OR SUP_CODE= p_CODE_SUPPLIER) and
(p_SELECT1 IS NULL OR  SELECT1= p_SELECT1)
and ( p_SELECT2 IS NULL OR SELECT2= p_SELECT2 )and(p_SELECT3 IS NULL OR  SELECT3= p_SELECT3 )and ( p_SELECT4 IS NULL OR SELECT4= p_SELECT4) and 
( p_SELECT5 IS NULL OR SELECT5= p_SELECT5) and ( p_SELECT6 IS NULL OR SELECT6= p_SELECT6) and ( p_SELECT7 IS NULL OR SELECT7= p_SELECT7)
and ( p_SELECT8 IS NULL OR SELECT8= p_SELECT8) and ( p_SELECT9 IS NULL OR SELECT9= p_SELECT9) and ( p_SELECT10 IS NULL OR SELECT10= p_SELECT10);
end;

它给了我PLS-00103 error
它是否有帮助，这是转换前用 sqlserver 编写的 procuder......

create procedure GET_ITEMS_PROC(@CODE nvarchar(14), @CODE_SUPPLIER nvarchar(20),@SELECT1 nvarchar(4),@SELECT2 nvarchar(4)
,@SELECT3 nvarchar(4),@SELECT4 nvarchar(4),
@SELECT5 nvarchar(4),@SELECT6 nvarchar(4),@SELECT7 nvarchar(4)
,@SELECT8 nvarchar(4),@SELECT9 nvarchar(4),@SELECT10 nvarchar(4))
as
select * from dbo.ITEMS where ( @CODE IS NULL OR CODE=@CODE )and (@CODE_SUPPLIER IS NULL OR SUP_CODE= @CODE_SUPPLIER) and
(@SELECT1 IS NULL OR  SELECT1= @SELECT1)
and ( @SELECT2 IS NULL OR SELECT2= @SELECT2 )and(@SELECT3 IS NULL OR  SELECT3= @SELECT3 )and ( @SELECT4 IS NULL OR SELECT4= @SELECT4) and 
( @SELECT5 IS NULL OR SELECT5= @SELECT5) and ( @SELECT6 IS NULL OR SELECT6= @SELECT6) and ( @SELECT7 IS NULL OR SELECT7= @SELECT7)
and ( @SELECT8 IS NULL OR SELECT8= @SELECT8) and ( @SELECT9 IS NULL OR SELECT9= @SELECT9) and ( @SELECT10 IS NULL OR SELECT10= @SELECT10)

多谢

Answer 1

不需要在 Oracle 中传递参数的大小。所以试试这个——

CREATE OR REPLACE PROCEDURE GET_ITEMS_PROC(
p_CODE VARCHAR2,
p_CODE_SUPPLIER VARCHAR2,
p_SELECT1 VARCHAR2,
p_SELECT2 VARCHAR2,
p_SELECT3 VARCHAR2,
p_SELECT4 VARCHAR2,
p_SELECT5 VARCHAR2,
p_SELECT6 VARCHAR2,
p_SELECT7 VARCHAR2,
p_SELECT8 VARCHAR2,
p_SELECT9 VARCHAR2,
p_SELECT10 VARCHAR2,
cur OUT sys_refcursor)
AS
BEGIN
OPEN cur FOR
SELECT *
FROM ITEMS
WHERE (p_CODE         IS NULL OR CODE               = p_CODE)
AND ( p_CODE_SUPPLIER IS NULL OR SUP_CODE           = p_CODE_SUPPLIER)
AND ( p_SELECT1       IS NULL OR SELECT1            = p_SELECT1)
AND ( p_SELECT2       IS NULL OR SELECT2            = p_SELECT2)
AND ( p_SELECT3       IS NULL OR SELECT3            = p_SELECT3)
AND ( p_SELECT4       IS NULL OR SELECT4            = p_SELECT4)
AND ( p_SELECT5       IS NULL OR SELECT5            = p_SELECT5)
AND ( p_SELECT6       IS NULL OR SELECT6            = p_SELECT6)
AND ( p_SELECT7       IS NULL OR SELECT7            = p_SELECT7)
AND ( p_SELECT8       IS NULL OR SELECT8            = p_SELECT8)
AND ( p_SELECT9       IS NULL OR SELECT9            = p_SELECT9)
AND ( p_SELECT10      IS NULL OR SELECT10           = p_SELECT10);
END;

Answer 2

procedure的输入参数不能有预言机中的数据长度。

因此，从procedure的所有参数中删除数据长度部分。

将p_CODE varchar2(14)更改为p_CODE varchar2并且所有参数相同。

代码的最终版本将是：

create or replace procedure GET_ITEMS_PROC(p_CODE varchar2, 
p_CODE_SUPPLIER varchar2,
p_SELECT1 varchar2,
p_SELECT2 varchar2,
p_SELECT3 varchar2,
p_SELECT4 varchar2,
p_SELECT5 varchar2,
p_SELECT6 varchar2,
p_SELECT7 varchar2,
p_SELECT8 varchar2,
p_SELECT9 varchar2,
p_SELECT10 varchar2, 
cur out sys_refcursor)
as
begin
open cur for select * from ITEMS where ( p_CODE IS NULL OR CODE=p_CODE )and (p_CODE_SUPPLIER IS NULL OR SUP_CODE= p_CODE_SUPPLIER) and
(p_SELECT1 IS NULL OR  SELECT1= p_SELECT1)
and ( p_SELECT2 IS NULL OR SELECT2= p_SELECT2 )and(p_SELECT3 IS NULL OR  SELECT3= p_SELECT3 )and ( p_SELECT4 IS NULL OR SELECT4= p_SELECT4) and 
( p_SELECT5 IS NULL OR SELECT5= p_SELECT5) and ( p_SELECT6 IS NULL OR SELECT6= p_SELECT6) and ( p_SELECT7 IS NULL OR SELECT7= p_SELECT7)
and ( p_SELECT8 IS NULL OR SELECT8= p_SELECT8) and ( p_SELECT9 IS NULL OR SELECT9= p_SELECT9) and ( p_SELECT10 IS NULL OR SELECT10= p_SELECT10);
end;

干杯！！