| Procedure Not executing Via SQL [message #10176] |
Wed, 07 January 2004 04:47  |
Michael GIllespie
Messages: 8
Registered: January 2004
|
Junior Member |
|
|
Can someone tell me if the following is syntacially correct.
CREATE OR REPLACE PROCEDURE ae_risk_calc_loan_msrs(as_dummy VARCHAR2)
AS
ln_id VARCHAR2(30);
ln_prof_nm VARCHAR2(30);
tot_pa NUMBER(10,2);
tot_msr NUMBER(10,2);
BEGIN
DECLARE CURSOR pa_msr_cur IS
SELECT l.loan_id,
l.profile_name,
l.price_adjustment - SUM(p.price_adjustment) as new_pa,
l.msr + SUM(p.price_adjustment) as new_msr
FROM rmcat_loan l,
rmcat_syntax_values_pa_tmp3 p
WHERE
l.loan_id = p.loan_id
AND l.investor_id = p.investor_id
AND p.price_adjustment_group like '%par rate%'
GROUP BY l.loan_id,
l.profile_name,
l.price_adjustment,
l.msr
;
BEGIN
FOR rm_ln IN pa_msr_cur LOOP
UPDATE rmcat_loan
SET price_adjustment = rm_ln.tot_pa,
msr = rm_ln.tot_msr
WHERE
loan_id = rm_ln.loan_id
AND profile_name= rm_ln.profile_name;
COMMIT;
END LOOP ;
END;
commit;
END;
^
|
|
|
|
| Re: Procedure Not executing Via SQL [message #10178 is a reply to message #10176] |
Wed, 07 January 2004 05:44  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Your syntax seems correct although I did not run your code, why? Did you have a compillation error? Did you run it an got a run-time error?
However, a little suggestion:
Do not commit in a LOOP.
And another question:
Why do you want to do it a hard and long way - in PL/SQL and not easier and quicker way in SQL?
|
|
|
|
|
|
|
|
| Re: Procedure Not executing Via SQL [message #10181 is a reply to message #10180] |
Wed, 07 January 2004 06:50 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Where your tot_pa comes from? Your cursor does not catch it. It catches loan_id,profile_name, new_pa and
new_msr:
SELECT l.loan_id,
l.profile_name,
l.price_adjustment - SUM(p.price_adjustment) as new_pa,
l.msr + SUM(p.price_adjustment) as new_msr
|
|
|
|
| Re: Procedure Not executing Via SQL [message #10182 is a reply to message #10180] |
Wed, 07 January 2004 06:57 |
Michael GIllespie
Messages: 8
Registered: January 2004
|
Junior Member |
|
|
|
sverch your a genius that was the problem. I was initial creating variables to fetch into. Then I read you can use the foor loop and run through the whole cursor. I forgot to change the select to my selected fields in the cursor. I made the change and it works correctly.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
