Home » SQL & PL/SQL » SQL & PL/SQL » Guidence
Guidence [message #250509] Tue, 10 July 2007 05:44 Go to next message
cmac
Messages: 1
Registered: July 2007
Junior Member
Hi, I have a version below working but it ain't very scalable, just looking for a tip to make it more efficent as qt the moment it a bit limited to 2 cycles through the loop, in each run through the loop it picks up the value <error_ret> and assigns them outside the loop, so at the end I concat them together to update in a single row in a table that is used as a display in an app. anyway a tip on how to change code so that its robust for multiple loops and concats for each run through the code would be better. varrays?


FOR i IN request_rec.FIRST .. request_rec.LAST
LOOP

IF errors (request_rec (i), error_ret) = FALSE
THEN
IF (i) = 1 then
h_request_id := request_rec (i);

error_ret1 := error_ret;
v_request_pgm1 := v_request_pgm;
h_request_id1 := h_request_id;
v_policy_no1 := v_policy_no;
v_claim_item_no1 := v_claim_item_no;
e_error_message1 := 'The process had the following errors: ' || chr(10) || 'Letter : ' || v_request_pgm || ', Request id:' || h_request_id || ', Policy no:' || v_policy_no || ', Claim no:' || v_claim_item_no || chr(10) ||error_ret ;

ELSE
error_ret2 := error_ret;
v_request_pgm2 := v_request_pgm;
h_request_id2 := h_request_id;
v_policy_no2 := v_policy_no;
v_claim_item_no2 := v_claim_item_no;
e_error_message2 := 'The process had the following errors: ' || chr(10) || 'Letter : ' || v_request_pgm2 || ', Request id:' || h_request_id2 || ', Policy no:' || v_policy_no2 || ', Claim no:' || v_claim_item_no2 || chr(10) ||error_ret ;
END IF;
END IF;
END LOOP;
e_error_message := e_error_message1 || chr(10) || e_error_message2;

insert_case_item_p (e_error_message, v_request_pgm, h_request_id, v_claim_item_no, v_policy_no, v_request_user);

COMMIT;
Re: Guidence [message #250511 is a reply to message #250509] Tue, 10 July 2007 05:46 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: UPDATE Query Help
Next Topic: about variable
Goto Forum:
  


Current Time: Fri Dec 09 19:40:40 CST 2016

Total time taken to generate the page: 0.09136 seconds