Home » SQL & PL/SQL » SQL & PL/SQL » creating a stored procedure..cursor question
creating a stored procedure..cursor question [message #193663] Mon, 18 September 2006 16:56 Go to next message
bnath001
Messages: 10
Registered: September 2006
Junior Member
I am creating my first stored proc in oracle. this is very simple stored proc.
My intention is to call this proc from C# programme. so far this is what I have. I am definitley making a mistake at cursor.
could some one please correct?

CREATE OR REPLACE PROCEDURE ReconcileHardm(io_cursor IN OUT t_cursor) IS
v_cursor t_cursor;

BEGIN

--STEP 1: DELETE THE CONTENTS OF THE 2 TABLES
DELETE FROM PORTFOLIOS.TEMP_FAS133_MAIN_COPY;

DELETE FROM PORTFOLIOS.TEMP_HEDGES_MON_F;

--STEP 2: insert the records into the temp table

INSERT INTO TEMP_FAS133_MAIN_COPY(DEAL,Hedge_MV,Pay_Notional,Rec_Notional,MTC_Current_Par,MTC_MV)
select deal, sum(hedge_mv), sum(pay_notional),sum(rec_notional),sum(mtc_current_par), sum(mtc_mv) from portfolios.fas133_main_copy@prod
where trade_date <= trunc(last_day(add_months(sysdate,-1))) and maturity_date > trunc(last_day(add_months(sysdate,-1))) and hedge_mv<>0
group by deal;

COMMIT;

--STEP 3: insert the records into the temp table TEMP_HEDGES_MON_f
INSERT INTO PORTFOLIOS.TEMP_HEDGES_MON_f(HEDGE_DEAL,Hedge_MV,Notional,Instrument_Par,Instrument_MV)
select hedge_deal, sum(hedge_mv),sum(notional),sum(instrument_par),sum(instrument_mv)
from harmdm.hedges_mon_f
where as_of_date = trunc(last_day(add_months(sysdate,-1)))
and hedge_mv <> 0
group by hedge_deal;

COMMIT;

--STEP 4: MASSAGE THE DATA

UPDATE TEMP_FAS133_MAIN_COPY SET DEAL = SUBSTR(DEAL,1,Instr(DEAL, '-')-1);

--STEP 5: Join the two temp tables and do the reconciliation
OPEN v_cursor FOR
SELECT FAS133.DEAL, HEDGES.HEDGE_DEAL, FAS133.MTC_MV, HEDGES.Instrument_MV, FAS133.HEDGE_MV, HEDGES.HEDGE_MV,FAS133.MTC_CURRENT_PAR,HEDGES.INSTRUMENT_PAR FROM TEMP_FAS133_MAIN_COPY FAS133 INNER JOIN TEMP_HEDGES_MON_f HEDGES ON FAS133.DEAL = HEDGES.HEDGE_DEAL WHERE FAS133.HEDGE_MV<>HEDGES.HEDGE_MV OR AS133.MTC_CURRENT_PAR<>HEDGES.INSTRUMENT_PAR
OR FAS133.MTC_MV <> HEDGES.Instrument_MV ORDER BY DEAL;

io_cursor := v_cursor;


END ReconcileHardm;
/
Re: creating a stored procedure..cursor question [message #193668 is a reply to message #193663] Mon, 18 September 2006 19:43 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I can't look at all the details of your query at the moment, but:

1) Remove all those separate COMMITs - there should just be one at the end, or better yet, let your C# program to the commit after calling the proc.

2) Just open the io_cursor parameter directly. There is no need for the v_cursor variable. If you are on 9i or later, you can use the built-in sys_refcursor type and:

io_cursor OUT sys_refcursor


In any case, it can just be an OUT parameter.

If you still have a problem, post the actual error message.
Previous Topic: accessing a select column in where clause
Next Topic: Why update takes time much more than insert
Goto Forum:
  


Current Time: Fri Dec 02 18:29:59 CST 2016

Total time taken to generate the page: 0.10984 seconds