Oracle stored procedure, after adding a variable, why did you report this error?

CREATE OR REPLACE PROCEDURE QD_MYDTJ
 (BEGINDATE  VARCHAR2,
  ENDDATE  VARCHAR2,
  xz_name VARCHAR2,
  xz_number VARCHAR2,

  --BEGININTEVAL  VARCHAR2,
  --ENDINTEVAL  VARCHAR2,
  start1         IN NUMBER,
  limit1         IN NUMBER,
  AN_O_RET_CODE         OUT NUMBER,
  AC_O_RET_MSG          OUT VARCHAR2,
  COUNTALL              OUT NUMBER,
  CUR_RET               OUT PKG_PUB_UTILS.REFCURSOR
 )
as
  limit2 NUMBER := 0;
BEGIN
    AN_O_RET_CODE := 0;
    AC_O_RET_MSG  := "";
     select COUNT (*) INTO COUNTALL
    from (select count(userid) from tb_agent_satisfaction where  to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE group by userid);
 limit2:=limit1;
  if limit1<0 then
    limit2:=COUNTALL+1;
  end if;
  --/*  */
  OPEN CUR_RET FOR
  SELECT * FROM ( SELECT  ROWNUM AS ROW_NUM,TT.* FROM (
select a.userid as RESULT_01,
e.name as RESULT_02,
to_char(NVL(a.count,0)) as RESULT_03,
to_char(NVL(b.count,0))as RESULT_04,
case to_char(b.count) when "0" then "0.00%" else to_char((NVL(b.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_05,
to_char(NVL(c.count,0)) as RESULT_06,
case to_char(c.count) when "0" then "0.00%" else to_char((NVL(c.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_07,
to_char(NVL(d.count,0)) as RESULT_08,
case to_char(d.count) when "0" then "0.00%" else to_char((NVL(d.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_09
from (select userid,count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE group by userid) a
left join (select userid,count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("1","2") group by userid) b on a.userid=b.userid
left join (select userid,count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("3","4") group by userid) c on a.userid=c.userid
left join  (select userid,count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("5","6") group by userid) d on a.userid=d.userid
left join TB_SSO_OPER e on e.LOGINCODE = a.USERID and a.userid =xz_number and e.name= xz_name
union all
select "" as RESULT_01,
"" as RESULT_02,
to_char(NVL(a.count,0)) as RESULT_03,
to_char(NVL(b.count,0))as RESULT_04,
case to_char(b.count) when "0" then "0.00%" else to_char((NVL(b.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_05,
to_char(NVL(c.count,0)) as RESULT_06,
case to_char(c.count) when "0" then "0.00%" else to_char((NVL(c.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_07,
to_char(NVL(d.count,0)) as RESULT_08,
case to_char(d.count) when "0" then "0.00%" else to_char((NVL(d.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_09
from (select count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE ) a,
(select count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("1","2") ) b ,
(select count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("3","4") ) c ,
(select count(*) as  count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("5","6") ) d
) TT ) WHERE ROW_NUM BETWEEN  start1  AND  limit2  ;
  EXCEPTION
    WHEN OTHERS THEN
        AN_O_RET_CODE := -1;
        AC_O_RET_MSG  := ":" || SQLCODE || CHR(13) || ":" || SQLERRM;
END QD_MYDTJ;
<!--  -->
    <parameterMap id="parameter_report_QD_mydtj" class="java.util.HashMap" >    
        <parameter property="BEGIN_DATE"             jdbcType="VARCHAR"     javaType="java.lang.String"     mode="IN"/>
        <parameter property="END_DATE"             jdbcType="VARCHAR"     javaType="java.lang.String"     mode="IN"/>
        <parameter property="start"     jdbcType="VARCHAR"     javaType="java.lang.String"     mode="IN"/>
        <parameter property="limit"        jdbcType="VARCHAR"     javaType="java.lang.String"     mode="IN"/>
        <parameter property="zx_name"    jdbcType="VARCHAR"     javaType="java.lang.String"     mode="IN"/>
        <parameter property="zx_number"    jdbcType="VARCHAR"     javaType="java.lang.String"     mode="IN"/>
        <parameter property="ret_code"                jdbcType="VARCHAR"    javaType="java.lang.String"        mode="OUT"/>
        <parameter property="ret_message"                jdbcType="VARCHAR"    javaType="java.lang.String"        mode="OUT"/>
        <parameter property="countall"                jdbcType="VARCHAR"    javaType="java.lang.String"        mode="OUT"/>
        <parameter property="cur_ret"                jdbcType="ORACLECURSOR"    javaType="java.sql.ResultSet"        mode="OUT" resultMap="result_report_QD_mydtj"/>
    </parameterMap>        
    <procedure  id="report_QD_mydtj" resultClass="java.util.HashMap" parameterMap="parameter_report_QD_mydtj">
        {call QD_MYDTJ(?,?,?,?,?,?,?,?,?,?) }
    </procedure >
ActionServlet................................
action=========================[com.sunyard.csr.action.GetReportAction@31b75e54]
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>{END_DATE=20180629, zx_number=12, limit=10, zx_name=2, BEGIN_DATE=20180530, IS_EXPORT_TYPE=3, session=no, start=0, REPORT_TYPE=report_QD_mydtj, small=0, large=0.6}
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in com/sunyard/csr/dao/xml/UserCalReport.xml.  
--- The error occurred while applying a parameter map.  
--- Check the UserCalReport.parameter_report_QD_mydtj.  
--- Check the statement (update procedure failed).  
--- Cause: java.sql.SQLException: ORA-06550:  1 ,  7 : 
PLS-00905:  WEBAGENT.QD_MYDTJ 
ORA-06550:  1 ,  7 : 
PL/SQL: Statement ignored

xz_name and zx_number
have been added to

xml. The two variables xz_name VARCHAR2,
xz_number VARCHAR2, have been added to the stored procedure, and nothing else has changed from the sql, of and a.userid = xz_number and e.name = xz_name

.
Mar.22,2021
The

error prompt is obvious, the stored procedure is invalid, and I need to recompile WEBAGENT.QD_MYDTJ


Thank you for the invitation. Because I am using mysql, I will not comment on it considering that there are still some differences between oracle and mysql. This is a feeling I found is OK, you can see if it is helpful to you. Link description

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1bef05f-313c7.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1bef05f-313c7.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?