exec PRO_OA_WEBSHOW('root','0101','T006','CP001,CP002,CP003,IP001,IP002,WJZZ,YG_TITLE,CN048,CN035,CN013,CN015,CN021,DN001,IP004,CN018,CP006,CP007,CP008,CN016,CN022,CN020,IN007,CN039,IHITS,YG_IMPDATS,ID,CN017,SYS_FLAG,YG_WEBSHOW','CP001 like '%01%' and CP003 like '%1991%'')
红色字体也是单独的一个参数,但是这个参数是where条件,里面含有引号,请教各位大侠,这个怎么加这个引号才正确呢?在线等....
红色字体也是单独的一个参数,但是这个参数是where条件,里面含有引号,请教各位大侠,这个怎么加这个引号才正确呢?在线等....
改为
'CP001 like ''%01%'' and CP003 like ''%1991%'''
PLS-00306: 调用 'PRO_OA_WEBSHOW' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
create or replace procedure abc(a varchar2,b varchar2,c varchar2,d varchar2,e varchar2)
is
begin
dbms_output.put_line(a);
dbms_output.put_line(b);
dbms_output.put_line(c);
dbms_output.put_line(d);
dbms_output.put_line(e);
end;
begin
abc('root',
'0101',
'T006',
'CP001,CP002,CP003,IP001,IP002,WJZZ,YG_TITLE,CN048,CN035,CN013,CN015,CN021,DN001,IP004,CN018,CP006,CP007,CP008,CN016,CN022,CN020,IN007,CN039,IHITS,YG_IMPDATS,ID,CN017,SYS_FLAG,YG_WEBSHOW',
'CP001 like ''%01%'' and CP003 like ''%1991%''');
end;
结果
root
0101
T006
CP001,CP002,CP003,IP001,IP002,WJZZ,YG_TITLE,CN048,CN035,CN013,CN015,CN021,DN001,IP004,CN018,CP006,CP007,CP008,CN016,CN022,CN020,IN007,CN039,IHITS,YG_IMPDATS,ID,CN017,SYS_FLAG,YG_WEBSHOW
CP001 like '%01%' and CP003 like '%1991%'
ARCLASS IN VARCHAR2,
TBNAME IN VARCHAR2,
COLDISP IN VARCHAR2,
WHERESTR IN VARCHAR2,
RECS OUT PKG_ARCHIVE.reCurTb) IS
VAR_QZH VARCHAR2(4000);
VAR_FLH VARCHAR2(4000);
VAR_TEMP_QZH VARCHAR2(4000);
VAR_TEMP_FLH VARCHAR2(4000);
VAR_SQL VARCHAR2(4000);
--VAR_WHERE VARCHAR2(500);
begin
select qzhright into VAR_TEMP_QZH from ygsys106 where groupname ='01' and Lower(roomname) = 'room01' and Lower(username) =UNAME and serialcode = ARCLASS;
select flbmright into VAR_TEMP_FLH from ygsys106 where groupname ='01' and Lower(roomname) = 'room01' and Lower(username) =UNAME and serialcode = ARCLASS;
--if sql%rowcount > 0 then
VAR_QZH := substr('''' || Replace(VAR_TEMP_QZH, ';', ''','''),
1,
length('''' || Replace(VAR_TEMP_QZH, ';', ''',''')) - 2); VAR_FLH := substr('''' || Replace(VAR_TEMP_FLH, ';', ''','''),
1,
length('''' || Replace(VAR_TEMP_FLH, ';', ''',''')) - 2);
VAR_QZH := Replace(VAR_QZH, ' ', '');
VAR_FLH := Replace(VAR_FLH, ' ', '');
--VAR_WHERE :=Replace(WHERESTR, '''','''''');
--返回权限内的数据值
if wherestr is null then
VAR_SQL:='select '|| COLDISP|| ' from '|| TBNAME || ' where cp001 in ('||VAR_QZH||') and cp002 in ('||VAR_FLH||') and (yg_webshow=0 or yg_webshow is null)';
else
--VAR_SQL:='select '|| COLDISP|| ' from '|| TBNAME || ' where cp001 in ('||VAR_QZH||') and cp002 in ('||VAR_FLH||') and (yg_webshow=0 or yg_webshow is null)';
--VAR_SQL:='select '|| COLDISP|| ' from '|| TBNAME || ' where cp001 in (01,02) and cp002 in ('||VAR_FLH||') and (yg_webshow=0 or yg_webshow is null) and cp001 like ''%01%'' and cp003<''2001''';
VAR_SQL:='select '|| COLDISP|| ' from '|| TBNAME || ' where cp001 in ('||VAR_QZH||') and cp002 in ('||VAR_FLH||') and (yg_webshow=0 or yg_webshow is null) and '||WHERESTR||'';
end if;
OPEN RECS FOR VAR_SQL;
--end if;
exception
when others then
OPEN RECS FOR select 'NULL' from dual;end pro_oa_webshow;大哥,这个是我的存储过程,是不是有问题啊,怎么用跟你用的一样就是出错呢?能帮我改改么?
帖子我会再加20分的。
你传的参数就是错误的.难怪会
报PLS-00306: 调用 'PRO_OA_WEBSHOW' 时参数个数或类型错误
这个错误..
你少传了个参数啊..