Home » SQL & PL/SQL » SQL & PL/SQL » Guidence
Guidence [message #250509] Tue, 10 July 2007 05:44 Go to next message
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

IF errors (request_rec (i), error_ret) = FALSE
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 ;

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 ;
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);

Re: Guidence [message #250511 is a reply to message #250509] Tue, 10 July 2007 05:46 Go to previous message
Michel Cadot
Messages: 64290
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).

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

Current Time: Mon Jan 23 06:49:37 CST 2017

Total time taken to generate the page: 0.16369 seconds