Here is our procedure:
CREATE OR REPLACE PROCEDURE WHSE.RPT_CAS_BUST_OUT (
start_date IN VARCHAR2
*,end_date IN VARCHAR2*
*,result_set OUT sys_refcursor*
*)*
AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp.sessions';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp.round_session';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp.wg2k_round';
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
BEGIN
INSERT INTO temp.sessions
*(session_id, customer_id)*
SELECT MAX (session_id) session_id, customer_id
FROM lottouser.sessions@cobratsb
WHERE start_time >= TO_DATE (start_date, 'dd-mon-yyyy')
AND start_time < TO_DATE (end_date, 'dd-mon-yyyy') + 1
AND end_time IS NOT NULL
GROUP BY customer_id;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
BEGIN
INSERT INTO temp.round_session
*(session_id, round_id)*
SELECT rs.session_id, rs.round_id
FROM lottouser.round_session@cobratsb rs, temp.sessions s
WHERE rs.session_id = s.session_id;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
BEGIN
INSERT INTO temp.wg2k_round
*(ID, total_bet, total_paid)*
SELECT ID, total_bet, total_paid
FROM lottouser.wg2k_round@cobratsb r, temp.round_session rs
WHERE r.ID = rs.round_id;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
OPEN result_set FOR
SELECT fn first_name, c.cid, ea email_address
*,NVL (lg, 0) lifetime_gain_loss*
FROM (SELECT s.customer_id cid
FROM temp.sessions s
*,temp.round_session rs*
*,temp.wg2k_round r*
WHERE s.session_id = rs.session_id AND rs.round_id = r.ID
GROUP BY s.customer_id
HAVING SUM (total_bet - total_paid)
*/ SUM (DECODE (total_bet, 0, NULL, total_bet)) > 0.25) c*
*, (SELECT c.customer_id cid, first_name fn, email_id ea*
*,-casino_lifetime_gain_loss lg*
FROM DW_CUSTOMERS c, DW_CUSTOMER_GAIN_LOSS g
WHERE c.customer_id = g.customer_id(+)
AND c.dw_eff_end_date > '31-dec-5000'
AND g.dw_eff_end_date > '31-dec-5000') g
WHERE c.cid = g.cid(+)
ORDER BY 2;
END;
it is executing in data base correctly but,
While we are calling a stored procedure by using ibatis with spring we are encountered with the following error:
OUTPUT=org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [72000]; error code [8103];
--- The error occurred in com/marketingdbreportingsite/ibatis/marketingdbreportingsite-sqlmap-productreports.xml.
--- The error occurred while applying a parameter map.
--- Check the marketingdbreportingsite.bustOutReport.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: ORA-08103: object no longer exists
The error occurred in com/marketingdbreportingsite/ibatis/marketingdbreportingsite-sqlmap-productreports.xml.
--- The error occurred while applying a parameter map.
--- Check the marketingdbreportingsite.bustOutReport.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: ORA-01410: invalid ROWID
The procedure in the database is working fine.
our code is working fine with other similar procedures.
Here is our mappings:
*<resultMap id="bustOutMapResult" class="com.sportsbetting.marketingdbreportingsite.domain.BustAndZeroOutReport" >*
*<result property="_firstName" column="first_name" jdbcType="VARCHAR" javaType="String"/>*
*<result property="_customerId" column="cid" jdbcType="NUMERIC" javaType="long"/>*
*<result property="_email" column="email_address" jdbcType="VARCHAR" javaType="String"/>*
*<result property="_revenue" column="lifetime_gain_loss" jdbcType="NUMERIC" javaType="double"/>*
*</resultMap>*
*<parameterMap id="bustOutReport" class="java.util.Map" >*
*<parameter property="startDate" jdbcType="VARCHAR" javaType="String" mode="IN"/>*
*<parameter property="endDate" jdbcType="VARCHAR" javaType="String" mode="IN"/>*
*<parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>*
*</parameterMap>*
*<procedure id="getBustOutReport" parameterMap="bustOutReport" resultMap="bustOutMapResult">*
*{ call WHSE.RPT_CAS_BUST_OUT(?, ?, ?) }*
**</procedure>**But this is not working.
why we are getting invalid rowid and object no longer exists???
Plz some one help to explain what may happening
Plz some one help to resolve this problem.