Oracle Cursor Scripts errors

Question

I have to construct a block to pull information from two tables using a cursor. As a further challenge, I have to identify the first item on the pull. I tried to use an IF statement to work through this. It errors out in several areas and I have no idea what I am doing wrong. Not asking for the answer per say, just enough of a push to get me moving again. Thanks. Here is the code I've put together so far:

DECLARE 
CURSOR cur_pled IS
SELECT dd_pledge.idpledge, dd_pledge.pledgeamt, dd_pledge.paymonths, dd_payment.paydate, dd_payment.payamt
FROM dd_pledge, dd_payment
WHERE dd_payment.idpledge = dd_pledge.idpledge AND  
dd_pledge.idpledge = 104
ORDER BY dd_pledge.idpledge, dd_payment.paydate;
TYPE type_pled IS RECORD
(pledID dd_pledge.idpledge%TYPE,
pledAmt dd_pledge.pledgeamt%TYPE,
payMonths dd_pledge.paymonths%TYPE,
payDate dd_payment.paydate%TYPE,
payAmt dd_payment.payamt%TYPE);
rec_pled type_pled;
lv_id_num dd_pledge.idpledge%TYPE := 0;
BEGIN
OPEN cur_pled;
LOOP
FETCH cur_pled INTO rec_pled;
EXIT WHEN cur_pled%NOTFOUND;
IF rec_pled.type <> lv_id_num THEN
DBMS_OUTPUT.PUT_LINE('First Payment');
ELSE DBMS_OUTPUT.PUT_LINE('Other Payment');
END IF;
END LOOP;
CLOSE cur_pled;
DBMS_OUTPUT.PUT_LINE(pledID || '   ' || dd_pledge.pledgeamt || '   ' ||
dd_pledge,payMonths || '   ' || dd_payment.payDate || '   ' || 
dd_payment.payAmt);
END;

Can you put a little more effort into the formatting please? — sstan
– sstan, Commented Jun 30, 2015 at 15:59

Boneist · Accepted Answer · 2015-06-30 16:47:28Z

1

There are loads of errors in your code. If you had formatted it correctly, you would have spotted some of them yourself!

Things that leapt out at me:

You're referring to dd_pledge in the final dbms_output.put_line, but dd_pledge isn't a variable. I think you meant to use rec_pled instead.
You refer to pledID in your final dbms_output.put_line statement - but this is a field defined in the record type, NOT a defined variable. I think you probably meant to use rec_pled.pledid
You're selecting the results of the cursor into rec_pled.type - however, "type" is not a field declared in the type_pled's definition! Did you mean rec_pled.idpledge instead?
You have dd_pledge,payMonths in your final dbms_output.put_line statement - the comma should be a full stop: rec_pled.payMonths
You're outputting the results after you've closed the results. Because this is just a record variable, you're only going to be outputting the results from the last row in the query.
Why aren't you doing a cursor for loop? That takes care of the exiting and declaring a record for you.

Anyway, I think you can achieve your results by using an analytic function in your query, rather than needing to use PL/SQL to do the work:

SELECT plg.idpledge,
       plg.pledgeamt,
       plg.paymonths,
       pay.paydate,
       pay.payamt,
       case when row_number() over (partition by plg.idpledge, pay.paydate) = 1 then 'First Payment'
            else 'Other Payment'
       end type_of_payment
FROM   dd_pledge plg
       inner join dd_payment pay on (pay.idpledge = plg.idpledge)
--WHERE  plg.idpledge = 104 -- add back in if you really need to do it for a single id
ORDER BY plg.idpledge, pay.paydate;

edited Jun 30, 2015 at 16:47

answered Jun 30, 2015 at 16:29

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Reed Over a year ago

I have to use cursors for this problem,. I fixed my some of the errors you mentioned and will look to see if your code suggestions can help with the rest. Still new to PL/SQL, so even small errors are sometimes tough to identify.

Boneist Over a year ago

Why do you "have" to use cursors? What is the purpose of the code? (Is it a homework type question?). In general, if you can do something in SQL, do it in SQL. Only fall back on PL/SQL when you can't do it in SQL. If you absolutely have to loop through the resultset and do other stuff, make sure you pack as much as you can into the cursor statement and use a cursor for loop rather than manually doing the open+row-by-row-fetch yourself (cursor for loops are optimised under the hood to do bulk fetches).

Collectives™ on Stack Overflow

Oracle Cursor Scripts errors

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related