Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Error Handling with SMTP Server

User_OR6IUNov 26 2021

Hi,
I use the UTL Package for sending emails via Oracle Database Function ( Database Version 12g), sometimes
i get this error:
ORA-29278 Transient Error: 451 4.7.0 Timeout waiting for client input
question is here, how can I handle this error, I thought that I use a try catch and implement
a wait/Sleep, try catch is implemented but what is the best Practice for sleep timer ?

Comments

Frank Kulash

Hi, @prokopis
Thanks for posting the sample data. Don't forget to post the exact results you want from the given sample data, an explain, step by step, how you get those results. Always give your full Oracle version (e.g. 18.4.0.0.0).
I want to split amounts to 36 doses.
Does that mean the 3 rows of sample data will produce 3 * 36 = 108 row of results? To avoid posting all 108 rows, you may want to use an example where you only need to split amounts into 3 doses. Just make it clear that you need 36 doses in your real problem; you'll get answers that can easily be changed.

Prokopis

Thanks for your comment
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
I give below a sample with 3 doses

WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT) AS (
SELECT '0423423423442', 3, 1300.12 FROM DUAL UNION ALL
SELECT   '04238423442', 3,  412.22 FROM DUAL
)
,DATA (CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER, MONTH_AMOUNT) 
 AS (SELECT CONTRACT_NUM, NUM_OF_DOSES , AMOUNT, 1, ROUND(AMOUNT/NUM_OF_DOSES,2)
       FROM BASE_QUERY
      UNION ALL
     SELECT CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER+1, MONTH_AMOUNT
       FROM DATA
      WHERE ITER+1 <= NUM_OF_DOSES)
SELECT CONTRACT_NUM, NUM_OF_DOSES, ITER AS CONTRACT_DOSE_ITER, ADD_MONTHS(TO_DATE('25/09/2021','dd/mm/yyyy'), ITER) AS MONTH_DATE, AMOUNT AS TOTAL_AMOUNT, MONTH_AMOUNT
  FROM DATA
ORDER BY CONTRACT_NUM, CONTRACT_DOSE_ITER;

I get as result
image.pngBut supposing a minimum amount 500 and fixing decimals to the last dose
I want to get
image.png

Solomon Yakobson 
WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT)
  AS (
      SELECT '0423423423442',36,1300.12 FROM DUAL
     )
SELECT  CONTRACT_NUM,
        NUM_OF_DOSES,
        AMOUNT,
        ITER,
        CASE ITER
          WHEN NUM_OF_DOSES THEN AMOUNT - ROUND(AMOUNT/NUM_OF_DOSES,2) * (NUM_OF_DOSES - 1)
          ELSE ROUND(AMOUNT/NUM_OF_DOSES,2)
        END MONTH_AMOUNT
  FROM  BASE_QUERY,
        LATERAL(
                SELECT  LEVEL ITER
                  FROM  DUAL
                  CONNECT BY LEVEL <= NUM_OF_DOSES
               )
/

CONTRACT_NUM  NUM_OF_DOSES     AMOUNT       ITER MONTH_AMOUNT
------------- ------------ ---------- ---------- ------------
0423423423442           36    1300.12          1        36.11
0423423423442           36    1300.12          2        36.11
0423423423442           36    1300.12          3        36.11
0423423423442           36    1300.12          4        36.11
0423423423442           36    1300.12          5        36.11
0423423423442           36    1300.12          6        36.11
0423423423442           36    1300.12          7        36.11
0423423423442           36    1300.12          8        36.11
0423423423442           36    1300.12          9        36.11
0423423423442           36    1300.12         10        36.11
0423423423442           36    1300.12         11        36.11
0423423423442           36    1300.12         12        36.11
0423423423442           36    1300.12         13        36.11
0423423423442           36    1300.12         14        36.11
0423423423442           36    1300.12         15        36.11
0423423423442           36    1300.12         16        36.11
0423423423442           36    1300.12         17        36.11
0423423423442           36    1300.12         18        36.11
0423423423442           36    1300.12         19        36.11
0423423423442           36    1300.12         20        36.11
0423423423442           36    1300.12         21        36.11
0423423423442           36    1300.12         22        36.11
0423423423442           36    1300.12         23        36.11
0423423423442           36    1300.12         24        36.11
0423423423442           36    1300.12         25        36.11
0423423423442           36    1300.12         26        36.11
0423423423442           36    1300.12         27        36.11
0423423423442           36    1300.12         28        36.11
0423423423442           36    1300.12         29        36.11
0423423423442           36    1300.12         30        36.11
0423423423442           36    1300.12         31        36.11
0423423423442           36    1300.12         32        36.11
0423423423442           36    1300.12         33        36.11
0423423423442           36    1300.12         34        36.11
0423423423442           36    1300.12         35        36.11
0423423423442           36    1300.12         36        36.27

36 rows selected.

SQL>

SY.

Frank Kulash

Hi, Prokopis
This happens due to rounding. Is it possible to assign the difference to the last dose ?
So, for all iters except the last one, you want month_amount to be  ROUND (amount / num_of_doses, 2), For the highest value of iter, you want month_amount to be whatever it takes to make the total of all month_amounts to be exactly amount; is that right?
Here's one way to do that:

WITH  data (contract_num, num_of_doses, amount, iter, month_amount)   AS
(
	SELECT  contract_num, num_of_doses , amount, 1, ROUND (amount / num_of_doses, 2)
  	FROM 	base_query
UNION ALL
        SELECT  contract_num, num_of_doses, amount, iter + 1
	, 	CASE
		  WHEN iter + 1 < num_of_doses
		  THEN month_amount
		  ELSE amount - ( (num_of_doses - 1)
		  	 	  * month_amount
				  )
		END
  	FROM 	data
  	WHERE 	iter + 1 <= num_of_doses
)
SELECT   contract_num
, 	 num_of_doses
, 	 iter		 AS contract_dose_iter
, 	 ADD_MONTHS ( TO_DATE ('25/09/2021','dd/mm/yyyy')
		    , iter
		    )    AS month_date
,        amount   	 AS total_amount
, 	 month_amount
FROM 	 data
ORDER BY contract_num
,   	 contract_dose_iter
;

Results (with num_of_doses changed to 3 on all rows):

 
CONTRACT_NUM NUM_OF_DOSES CONTRACT_DOSE_ITER MONTH_DATE   TOTAL_AMOUNT MONTH_AMOUN
------------- ------------ ------------------ ----------- ------------ ------------
0423423423442            3                  1 25-Oct-2021      1300.12       433.37
0423423423442            3                  2 25-Nov-2021      1300.12       433.37
0423423423442            3                  3 25-Dec-2021      1300.12       433.38
04238411111              3                  1 25-Oct-2021       120.69        40.23
04238411111              3                  2 25-Nov-2021       120.69        40.23
04238411111              3                  3 25-Dec-2021       120.69        40.23
04238423442              3                  1 25-Oct-2021       412.22       137.41
04238423442              3                  2 25-Nov-2021       412.22       137.41
04238423442              3                  3 25-Dec-2021       412.22        137.4
mathguy

Here is how I would do it. Note that this is a direct computation in closed form, rather than an iterative computation; so I can use a hierarchical (CONNECT BY) query instead of a recursive one. Also using the LATERAL join syntax, available since Oracle 12.
The condition on LEVEL in the lateral subquery could be written more compactly (dividing AMOUNT by PRELIM_MONTH_AMOUNT ) but that may cause rounding errors and the wrong number of doses.
I changed the number of doses to 12 so the output is not too long. I allow the number of doses and the minimum amount per month to depend on the contract (neither 12 nor 100 are hard-coded); if instead they should be constant across all rows (but still user inputs - like 36 and 110 for all contracts, instead of 36 and 100, or 12 and 100), you will have to modify this slightly to accept those as inputs, instead of being part of the table data.

with
  base_query (contract_num, num_of_doses, amount, min_amount) as (
    select '0423423423442', 12, 1300.12, 100 from dual union all
    select '04238423442'  , 12,  412.22, 100 from dual union all
    select '04238411111'  , 12,  120.69, 100 from dual
  )
, prep (contract_num, num_of_doses, amount, min_amount, prelim_month_amount) as (
    select contract_num, num_of_doses, amount, min_amount,
           greatest(100, round(amount / num_of_doses, 2))
    from   base_query
  )
select p.contract_num, l.iter,
       case l.isleaf
            when 0 then p.prelim_month_amount
            else p.amount - (l.iter - 1) * p.prelim_month_amount end as month_amount
from   prep p cross join lateral
       (
         select  level as iter, connect_by_isleaf as isleaf
         from    dual
         connect by (level - 1) * p.prelim_month_amount <= p.amount
                and level <= p.num_of_doses
       ) l
;


CONTRACT_NUM        ITER MONTH_AMOUNT
------------- ---------- ------------
0423423423442          1       108.34
0423423423442          2       108.34
0423423423442          3       108.34
0423423423442          4       108.34
0423423423442          5       108.34
0423423423442          6       108.34
0423423423442          7       108.34
0423423423442          8       108.34
0423423423442          9       108.34
0423423423442         10       108.34
0423423423442         11       108.34
0423423423442         12       108.38
04238423442            1          100
04238423442            2          100
04238423442            3          100
04238423442            4          100
04238423442            5        12.22
04238411111            1          100
04238411111            2        20.69
mathguy

While I was working on the problem, you posted a Reply in which you said your Oracle version is 11.
Please include your version with all your questions - it's much more relevant than most people think.
The LATERAL clause is not supported in your version. So you will need to modify the query I offered as shown below.

select  contract_num, level as iter,
        case connect_by_isleaf
             when 0 then prelim_month_amount
             else amount - (level - 1) * prelim_month_amount end as month_amount
from    prep
connect by (level - 1) * prelim_month_amount <= amount
        and level <= num_of_doses
        and prior contract_num = contract_num
        and prior sys_guid() is not null
;
1 person found this helpful
EdStevens

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
OUCH! That is completely out of support.

Frank Kulash
Answer

Hi, Prokopis
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Then LATERAL or CROSS APPLY won't help you; those were new features in Oracle 12.1.
But supposing a minimum amount 500 and fixing decimals to the last dose
I want to get
image.png
Okay, now I see what you want. Here's one way to do it:

WITH  data (contract_num, num_of_doses, amount, iter, month_amount, amount_left)   AS
(
	SELECT  contract_num, num_of_doses , amount, 0
	, 	GREATEST ( ROUND (amount / num_of_doses, 2)
			 , :min_amount
			 )
	,       amount
  	FROM 	base_query
UNION ALL
        SELECT  contract_num, num_of_doses, amount, iter + 1
	,	month_amount
	,	amount_left - month_amount
  	FROM 	data
  	WHERE 	iter + 1 <= num_of_doses
)
SELECT   contract_num
, 	 num_of_doses
, 	 iter		 AS contract_dose_iter
, 	 ADD_MONTHS ( TO_DATE ('25/09/2021','dd/mm/yyyy')
		    , iter
		    )    AS month_date
,       amount   	 AS total_amount
, 	 CASE
	   WHEN amount_left > 0
	    AND iter    < num_of_doses
	   THEN month_amount
	   ELSE amount_left + month_amount
	 END		 AS month_amount
--,	 amount_left   -- FOR DEBUGGING ONLY
FROM 	 data
WHERE	 iter                       >  0
AND	 amount_left + month_amount >= 0
ORDER BY contract_num
,   	 contract_dose_iter
;

The query above uses a bind variable (:min_amount). You could use any other technique to get the appropriate value into the query. Since it's only used in one place, you could hard-code it.

Marked as Answer by Prokopis · Sep 23 2021
Prokopis

Thanks a lot for all you answers !!

I did not know about Lateral function. I will search it.
Thanks again

mathguy

I see that you chose an answer based on recursive WITH clause as the "accepted solution".
In my replies, I suggested a solution using CONNECT BY, which does all the computations in closed form rather than relying on recursive calculations. This should result in faster execution times.
I set out to see if that's true - and if it is, how much faster (since small differences may not matter). For testing I used my second solution, where I am not using the LATERAL clause. It is the solution that you can use in Oracle 11.2.
The answer is - if you have only 1000 contracts to work on, the CONNECT BY query is 2-3 times faster, but that may not matter to you, since execution times are short: about 0.15 to 0.2 seconds for CONNECT BY vs. 0.5 seconds even for the slower, recursive WITH clause approach.
As expected, the difference becomes greater with more data. When I tested on a table with 10,000 rows (do you have that many contracts?) the CONNECT BY query takes 0.5 seconds, while the recursive WITH solution that you accepted takes 9 seconds. That (as anyone can calculate) is 18 times slower. Is that OK for your purposes?
Obviously, the best way to decide, if you are going to spend time on this, is to test on your own data.
For what it's worth, the CONNECT BY + LATERAL solution (available only from version 12.1 on) runs in less than 0.3 seconds for the same 10,000 contracts. Faster, but still the same order of magnitude. Going from recursive WITH to CONNECT BY, even without LATERAL, is what will make by far the biggest difference.

As an aside, as I was testing, I noticed that Mr. Kulash's query isn't 100% correct. When the amount is an exact multiple of the "minimum amount" (let's say 100), and smaller than that minimum amount times the number of doses (example: min amount 100, num of doses 36, but total amount 1200), the query will produce one extra row, for an unneeded period (month), with a monthly amount of 0. Of course, that can be fixed easily; just letting you know so you can take a look at it and fix it.

1 - 10

Post Details

Added on Nov 26 2021
#general-database-discussions
7 comments
864 views