On 15 July 2020, I bought a 12 inch inner bicycle tube for my 16 inch bandsaw, on amazon.nl. I paid 2.51 euro for it, which was quite cheap compared to other shops. But a few weeks later I noticed that the price had gone up - and not a little but to almost 9 euro - more that 3 times the price I paid!
Of course all prices on amazon (and similar websites) are "flexible", and are determined by automated (and complex) supply-and-demand algorithms, but this was a bit shocking. So I decided to write some code to monitor this trend. What else could I do? ;-) Well, obviously this has been done before by other site that track prices, like camelcamelcamel.com and keepa.com - but for amazon.nl this wasn't available at the time. And besides, it's fun to build.
I have included 2 other items on amazon which I looked at, and for which I noticed big prices jumps too. I fact, I didn't buy either of them because the price had gone up while I was still thinking about them. I'm not sure if amazon really intended that end result of their price changes...
I also included amazon.de, to see if the prices on there did roughly the same - which they do, more or less. Nothing shocking anyway, although the price difference between NL and DE for the 3rd item (Steel Ruler Set) is, and stays, quite large.
I'm not quite sure what to do with this information, but it sure is interesting. The price of the 12 inch tube did drop to it original level eventually, but that took quite a few weeks - and then went up again to 5 euros shortly after.

As a side note: Originally I had the database job scheduled to run every minute, instead of every hour. I had no idea yet how often the prices changed, so I thought I'd make sure to capture as much data as I could. However, it turns out Amazon does not like that kind of behavior very much (can't blame them really...), so after about a day I got 500 server errors for almost every try. Polling every hour seems to work fine, at least for a few months.

Note: May 10th 2021 I stopped the database job. The ted_amazon_logs table was completely filling up my Users tablespace (8112 MB and counting), and that was a bit much for my small VPS. I probably should disable logging for that table, but I think the experiment has gone on long enough to prove my point.

Page

Identification
	Page ID	1147
	Name	Miscellaneous - Amazon Prices

Item

Identification
	Sequence	10
	Name	P1147_PRODUCT_ID
	Display As	Hidden
User Interface
	Region	Products
List Of Values
	Display Null Value	No
Advanced
	Warn on Unsaved Changes	Page Default
Source
	Source Used	Only when current value in session state is null
	Source Type	Always Null
Default
	Default Value	B01EV8HJYQ
	Default Value Type	STATIC_TEXT_WITH_SUBSTITUTIONS

Item

Identification
	Sequence	20
	Name	P1147_COUNTRY_CODE
	Display As	Select List
User Interface
	Region	Products
	Label	Country Code
List Of Values
	Display Extra Values	Yes
	Display Null Value	No
	List of values definition	select distinct prod.country_code as d , prod.country_code as r from ted_amazon_products prod order by 1
Advanced
	Warn on Unsaved Changes	Page Default
Source
	Source Used	Only when current value in session state is null
	Source Type	Static Assignment (value equals source attribute)
	Source value or expression	nl

Region

Identification
	Sequence	20
	Title	Products
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	select prod.pk_id , prod.name , prod.product_id , prod.url_suffix , ted_amazon.build_url ( p_country_code => prod.country_code , p_product_id => prod.product_id , p_url_suffix => prod.url_suffix ) as amazon_url from ted_amazon_products prod where prod.country_code = :P1147_COUNTRY_CODE

Report Column

Column Definition
	Column Name	PK_ID
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	 
Column Link
	Link Text	<img src="#APEX_FILES#app_ui/img/icons/apex-edit-pencil-alt.png" class="apex-edit-pencil-alt" alt="">
	Link Attributes	id="PRODUCT_ID#PRODUCT_ID#"
	URL	f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.::&DEBUG.::P1147_PRODUCT_ID:#PRODUCT_ID#

Region

Identification
	Sequence	30
	Title	Price per Hour
	Type	Chart
Source
	Source Type	NATIVE_JET_CHART
	Page Items to Submit	P1147_PRODUCT_ID
Chart Series
	Series Name	NL
	Sequence	10
	Data Source Type	SQL Query
	Data Source	select t.d , t.price from ted_p1147_v t where t.product_id = :P1147_PRODUCT_ID and t.country_code = 'nl' order by 1
Chart Series
	Series Name	DE
	Sequence	20
	Data Source Type	SQL Query
	Data Source	select t.d , t.price from ted_p1147_v t where t.product_id = :P1147_PRODUCT_ID and t.country_code = 'de' order by 1

Dynamic Action

Identification
	Name	Row Highlight
Execution Options
	Sequence	20
When
	Event	After Refresh
	Selection Type	Region
	Region	Price per Hour
Advanced
	Event Scope	Dynamic
True Action
Identification
	Action	Execute JavaScript Code
Settings
	Code	// Interactive Reports: highlight the row if ($v('P1147_PRODUCT_ID')) { var row = document.getElementById("PRODUCT_ID&P1147_PRODUCT_ID.").parentElement.parentElement; for (var j = 0; j < row.cells.length; j++) { row.cells[j].style.setProperty("background-color", "BlanchedAlmond", "important"); } }
Execution Options
	Sequence	10
	Fire On Initialization	No

View

View
	Name	ted_p1147_v
	DDL	with iv_pric as ( select pric.prod_pk_id , pric.date_start as d1 , trunc( coalesce(pric.date_end, pric.date_last_check), 'HH24' ) as d2 , pric.price from ted_amazon_prices pric ) , iv_p as ( select iv_pric.prod_pk_id , trunc( iv_pric.d1, 'HH24' ) as d , iv_pric.price from iv_pric union all select iv_pric.prod_pk_id , trunc( iv_pric.d2, 'HH24' ) as d , iv_pric.price from iv_pric ) select prod.pk_id as prod_pk_id , prod.name as prod_name , prod.product_id , prod.country_code , iv_p.d , min(iv_p.price) as price from iv_p join ted_amazon_products prod on prod.pk_id = iv_p.prod_pk_id group by prod.pk_id , prod.name , prod.product_id , prod.country_code , iv_p.d

Table

Table
	Name	ted_amazon_products
	DDL	CREATE TABLE "TEDSTRUIK"."TED_AMAZON_PRODUCTS" ( "PK_ID" NUMBER NOT NULL ENABLE, "NAME" VARCHAR2(4000), "PRODUCT_ID" VARCHAR2(255) NOT NULL ENABLE, "COUNTRY_CODE" VARCHAR2(10) NOT NULL ENABLE, "URL_SUFFIX" VARCHAR2(1000), CONSTRAINT "TED_AMAZON_PRODUCTS_UK" UNIQUE ("PRODUCT_ID", "COUNTRY_CODE") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "TED_AMAZON_PRODUCTS_PK" PRIMARY KEY ("PK_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Table

Table
	Name	ted_amazon_logs
	DDL	CREATE TABLE "TEDSTRUIK"."TED_AMAZON_LOGS" ( "LOG_DATE" DATE NOT NULL ENABLE, "PROD_PK_ID" NUMBER, "ERROR_MESSAGE" VARCHAR2(4000), "HTML" CLOB, "DEBUG_INFO" VARCHAR2(4000), CONSTRAINT "TED_AMAZON_LOGS_PROD_K" FOREIGN KEY ("PROD_PK_ID") REFERENCES "TEDSTRUIK"."TED_AMAZON_PRODUCTS" ("PK_ID") ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645) TABLESPACE "USERS" LOB ("HTML") STORE AS SECUREFILE ( TABLESPACE "USERS" ENABLE STORAGE IN ROW 4000 CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES )

Table

Table
	Name	ted_amazon_prices
	DDL	CREATE TABLE "TEDSTRUIK"."TED_AMAZON_PRICES" ( "PK_ID" NUMBER NOT NULL ENABLE, "PROD_PK_ID" NUMBER NOT NULL ENABLE, "DATE_START" DATE NOT NULL ENABLE, "DATE_END" DATE, "DATE_LAST_CHECK" DATE, "PRICE" NUMBER NOT NULL ENABLE, CONSTRAINT "TED_AMAZON_PRIC_PK_ID" PRIMARY KEY ("PK_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "TED_AMAZON_PRIC_PROD_FK" FOREIGN KEY ("PROD_PK_ID") REFERENCES "TEDSTRUIK"."TED_AMAZON_PRODUCTS" ("PK_ID") ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Package

Package
	Name	ted_http
	Source	package ted_http is function build_http_url ( p_username in varchar2 , p_password in varchar2 , p_domain in varchar2 , p_port in number , p_path in varchar2 , p_query_string in varchar2 , p_anchor in varchar2 ) return varchar2 ; procedure http_post ( p_url_in in varchar2 , p_post_or_get in varchar2 , p_http_or_https in varchar2 , p_data_in in clob , p_data_type in varchar2 default null , p_proxy_in in varchar2 default null , p_no_proxy_domains_in in varchar2 default null , p_username_in in varchar2 default null , p_password_in in varchar2 default null , o_http_status_code out varchar2 , o_http_reason_phrase out varchar2 , o_http_response_body out clob ) ; function ping ( p_host_name varchar2 , p_port number default 80 ) return varchar2 -- Y/N ; end ted_http;

Package Body

Package Body
	Name	ted_http
	Source	package body ted_http is c_https_wallet_path constant varchar2(4000) := '/home/oracle'; c_http_version constant varchar2(100) := utl_http.http_version_1_1; c_data_type constant varchar2(100) := 'text/html;charset=UTF-8'; c_timeout constant pls_integer := 5; c_user_agent constant varchar2(100) := 'Fiddler'; function clob_to_blob ( p_clob in clob ) return blob is v_blob blob := utl_raw.cast_to_raw('x'); v_dest_offsset integer := 1; v_src_offsset integer := 1; v_lang_context integer := dbms_lob.default_lang_ctx; v_warning integer; begin if p_clob is not null then dbms_lob.converttoblob ( dest_lob => v_blob , src_clob => p_clob , amount => dbms_lob.lobmaxsize , dest_offset => v_dest_offsset , src_offset => v_src_offsset , blob_csid => dbms_lob.default_csid , lang_context => v_lang_context , warning => v_warning ) ; return v_blob; else return null; -- needed to prevent ora-22275 when p_clob is null end if; exception when others then raise_application_error(-20000,sqlerrm); end clob_to_blob; function clob_length_in_bytes ( p_clob in clob ) return number is -- Use this when you want to get the exact size in bytes of a clob in case of multibyte character set (e.g. AL32UTF8). -- dbms_lob.getlength gives length in characters, not in bytes, so for e.g. http content-length this is not useable. v_blob blob := utl_raw.cast_to_raw('x'); v_result number; begin if p_clob is not null then v_blob := clob_to_blob(p_clob); v_result := dbms_lob.getlength(v_blob); return v_result; else return null; -- needed to prevent ora-22275 when p_clob is null end if; exception when others then raise_application_error(-20000,sqlerrm); end clob_length_in_bytes; function build_http_url ( p_username in varchar2 , p_password in varchar2 , p_domain in varchar2 , p_port in number , p_path in varchar2 , p_query_string in varchar2 , p_anchor in varchar2 ) return varchar2 is v_result varchar2(32000); begin -- Source info: http://en.wikipedia.org/wiki/Uniform_Resource_Locator v_result := ''; if p_username is not null and p_password is not null then v_result := v_result || p_username || ':' || p_password || '@'; end if; v_result := v_result || p_domain; if p_port is not null then v_result := v_result || ':' || trim(p_port); end if; if p_path is not null then v_result := v_result || p_path; end if; if p_query_string is not null then v_result := v_result || '?' || p_query_string; end if; if p_anchor is not null then v_result := v_result || '#' || p_anchor; end if; return v_result; exception when others then raise_application_error(-20000,sqlerrm); end build_http_url; procedure http_post ( p_url_in in varchar2 , p_post_or_get in varchar2 , p_http_or_https in varchar2 , p_data_in in clob , p_data_type in varchar2 default null , p_proxy_in in varchar2 default null , p_no_proxy_domains_in in varchar2 default null , p_username_in in varchar2 default null , p_password_in in varchar2 default null , o_http_status_code out varchar2 , o_http_reason_phrase out varchar2 , o_http_response_body out clob ) is v_http_req utl_http.req; v_http_resp utl_http.resp; begin -- Original source: http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/ -- To do: -- implement both POST & GET (only diff is parameters in body or in url? also give that choice, but default should be the spec standard) -- check p_post_or_get if upper(p_post_or_get) not in ('POST', 'GET') then raise_application_error(-20000,'p_post_or_get must be POST or GET'); end if; -- Actions required for https case p_http_or_https when 'http' then null; when 'https' then utl_http.set_wallet('file:' || c_https_wallet_path); else raise_application_error(-20000,'p_http_or_https must be http or https'); end case; -- When going through a firewall, pass requests through this host. -- Specify sites inside the firewall that don't need the proxy host. if p_proxy_in is not null then utl_http.set_proxy(proxy => p_proxy_in, no_proxy_domains => p_no_proxy_domains_in); end if; -- Added by Ted: can be used for debugging e.g. ORA-29263: HTTP protocol error utl_http.set_detailed_excp_support(enable => true); -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes, rather than just returning the text of the error page. utl_http.set_response_error_check(enable => false); -- Begin the request v_http_req := utl_http.begin_request(url => p_url_in, method => upper(p_post_or_get), http_version => c_http_version); -- http_version HTTP/1.1 is needed for dthi -- Specify a user ID and password for pages that require them. if p_username_in is not null then utl_http.set_authentication(r => v_http_req, username => p_username_in, password => p_password_in); end if; -- Set the transfer timeout in seconds (default = 60) utl_http.set_transfer_timeout ( timeout => c_timeout ); case upper(p_post_or_get) when 'POST' then -- Set the HTTP request headers utl_http.set_header(r => v_http_req, name => 'User-Agent' , value => c_user_agent); utl_http.set_header(r => v_http_req, name => 'Content-Type' , value => nvl(p_data_type, c_data_type)); -- utl_http.set_header(r => v_http_req, name => 'Accept-Encoding', value => 'gzip,deflate'); --################ -- utl_http.set_header(r => v_http_req, name => 'SOAPAction' , value => '"http://tempuri.org/callXmlProcess"'); --################ utl_http.set_header(r => v_http_req, name => 'Content-Length' , value => clob_length_in_bytes(p_clob => p_data_in)); -- Write the data to the body of the HTTP request -- Note: utl_http.write_text only handles varchar2 (max 32k), so chop it into bite-size pieces for write_text declare v_chunk_size pls_integer default 1000; v_clob_chunk clob; v_offset pls_integer default 1; begin loop dbms_lob.read(p_data_in, v_chunk_size, v_offset, v_clob_chunk); v_offset := v_offset + v_chunk_size; utl_http.write_text(v_http_req, v_clob_chunk); end loop; exception when no_data_found then null; when others then raise; end; else -- Set the HTTP request headers utl_http.set_header(r => v_http_req, name => 'User-Agent', value => c_user_agent); -- if user agent is not set, some servers will return content with length 0 end case; -- Process the request and get the response v_http_resp := utl_http.get_response(v_http_req); --utl_http.set_body_charset(r => v_http_resp, charset => null); -- maybe needed to ensure correct handling of special characters? o_http_status_code := v_http_resp.status_code; o_http_reason_phrase := v_http_resp.reason_phrase; dbms_lob.createtemporary(o_http_response_body, true); declare v_clob_chunk clob := null; begin loop utl_http.read_text(v_http_resp, v_clob_chunk); dbms_lob.append(o_http_response_body, v_clob_chunk); end loop; exception when utl_http.end_of_body then null; end; -- Close the request utl_http.end_response(v_http_resp); exception when others then raise; end http_post; function ping ( p_host_name varchar2 , p_port number default 80 ) return varchar2 -- Y/N is l_tcpconnection utl_tcp.connection; begin l_tcpconnection := utl_tcp.open_connection ( remote_host => p_host_name , remote_port => p_port ) ; utl_tcp.close_connection(l_tcpconnection); return 'Y'; exception when utl_tcp.network_error then dbms_output.put_line(sqlerrm); if lower(sqlerrm) like '%does not exist%' then -- "Connect failed because target host or object does not exist" -- note: this takes 60 seconds return 'N'; elsif lower(sqlerrm) like '%operation timed out%' then -- "TNS:operation timed out" -- this means the host itself can be reached, so this is OK return 'Y'; elsif lower(sqlerrm) like '%listener%' then -- the host is accessible but there is no listener, so this is OK return 'Y'; else raise; end if; when others then raise; end ping; end ted_http;

Package

Package
	Name	ted_amazon
	Source	package ted_amazon is function build_url ( p_country_code in ted_amazon_products.country_code%type , p_product_id in ted_amazon_products.product_id%type , p_url_suffix in ted_amazon_products.url_suffix%type ) return varchar2 ; procedure upd; end ted_amazon;

Package Body

Package Body
	Name	ted_amazon
	Source	package body ted_amazon is k_upd_loop_delay_day_max constant number := 90/60/24; -- = 150% of the job interval k_lf constant varchar2(2) := chr(13) || chr(10); function build_url ( p_country_code in ted_amazon_products.country_code%type , p_product_id in ted_amazon_products.product_id%type , p_url_suffix in ted_amazon_products.url_suffix%type ) return varchar2 is v_url varchar2(1000); begin v_url := 'www.amazon.' || p_country_code || '/gp/product/' || p_product_id || p_url_suffix; return v_url; end build_url; function cleanup_tag# ( p_tag in varchar2 ) return varchar2 is v_result varchar2(4000) := p_tag; begin v_result := replace(v_result, chr(10) , null); v_result := replace(v_result, chr(13) , null); v_result := replace(v_result, chr(14844588), null); -- euro sign v_result := replace(v_result, chr(49824) , null); -- "space" before/after euro sign v_result := trim(v_result); return v_result; end cleanup_tag#; procedure debug_info# ( po_debug_info in out varchar2 , p_new in varchar2 ) is begin po_debug_info := po_debug_info || k_lf || p_new; dbms_output.put_line(p_new); end debug_info#; procedure upd is v_http_status_code varchar2( 10); v_http_reason_phrase varchar2(1000); v_url varchar2( 255); v_html clob; v_tag varchar2(4000); v_pric_row ted_amazon_prices%rowtype; v_error_message ted_amazon_logs.error_message%type; v_debug_info ted_amazon_logs.debug_info%type; cursor c_prod is select prod.* from ted_amazon_products prod for update of name nowait order by prod.pk_id ; r_prod c_prod%rowtype; begin -- clean up old logs delete from ted_amazon_logs where ( error_message is null and log_date < trunc(sysdate) - 1 ) or ( error_message is not null and log_date < trunc(sysdate) - 10 ) ; open c_prod; loop v_url := null; v_html := null; v_debug_info := null; v_error_message := null; fetch c_prod into r_prod; exit when c_prod%notfound; debug_info#(v_debug_info, 'r_prod.pk_id=' || r_prod.pk_id); v_url := build_url ( p_country_code => r_prod.country_code , p_product_id => r_prod.product_id , p_url_suffix => r_prod.url_suffix ) ; begin -- get html page ted_http.http_post ( p_url_in => v_url , p_post_or_get => 'GET' , p_http_or_https => 'https' , p_data_in => null , o_http_status_code => v_http_status_code , o_http_reason_phrase => v_http_reason_phrase , o_http_response_body => v_html ) ; debug_info#(v_debug_info, 'v_html (length)=' || length(v_html)); -- log/raise if http call went wrong if v_http_status_code <> 200 then debug_info#(v_debug_info, 'v_url=' || v_url || ' : ' || 'v_http_status_code=' || v_http_status_code); v_error_message := 'v_http_status_code=' || v_http_status_code; raise_application_error(-20000, 'v_http_status_code=' || v_http_status_code); end if; -- get product name tag, if needed if r_prod.name is null then v_tag := regexp_substr(v_html, '<span id="productTitle"(.*?)>((.|\n)*?)<\/span>', 1, 1, 'in', 2); v_tag := cleanup_tag#(v_tag); debug_info#(v_debug_info, 'v_tag (product_name)=' || v_tag); update ted_amazon_products prod set prod.name = v_tag where current of c_prod ; end if; -- get price tag -- note that this part of the code is likely to fail over time - the html of the amazon pages does change every once in a while v_tag := regexp_substr(v_html, '<span id="priceblock_ourprice"(.*?)>((.|\n)*?)<\/span>', 1, 1, 'i', 2); if v_tag is null then -- needed for e.g. https://www.amazon.de/gp/product/B088GY8BH4 v_tag := regexp_substr(v_html, '<span id="priceblock_saleprice"(.*?)>((.|\n)*?)<\/span>', 1, 1, 'i', 2); end if; v_tag := cleanup_tag#(v_tag); v_tag := replace(v_tag, '.', null); v_tag := replace(v_tag, ',', '.'); debug_info#(v_debug_info, 'v_tag (price)=' || v_tag); -- try to find an existing ted_amazon_prices row to update v_pric_row := null; begin select * into v_pric_row from ted_amazon_prices pric where pric.prod_pk_id = r_prod.pk_id and pric.date_end is null ; if sysdate > (v_pric_row.date_last_check + k_upd_loop_delay_day_max) or v_pric_row.price <> v_tag then -- the found row is too old -- OR the price has changed -- so, update date_end to the date of the last check -- AND "close" it (so we can insert a new one later on in the code) update ted_amazon_prices pric set pric.date_end = pric.date_last_check where pric.pk_id = v_pric_row.pk_id ; v_pric_row := null; else -- all is OK for an update of date_last_check update ted_amazon_prices pric set pric.date_last_check = sysdate where pric.pk_id = v_pric_row.pk_id ; end if; exception when no_data_found then null; end; -- insert into ted_amazon_prices if v_pric_row.pk_id is null then insert into ted_amazon_prices ( prod_pk_id , date_start , date_end , date_last_check , price ) values ( r_prod.pk_id , sysdate , null , sysdate , v_tag ) ; end if; exception when others then -- debug_info#(v_debug_info, 'v_url=' || v_url || ' : ' || 'sqlerrm=' || sqlerrm); -- v_error_message := dbms_utility.format_error_stack; -- insert into ted_amazon_logs ( log_date , prod_pk_id , error_message , html , debug_info ) values ( sysdate , r_prod.pk_id , dbms_utility.format_error_stack , v_html , v_debug_info ) ; -- -- raise; end; insert into ted_amazon_logs ( log_date , prod_pk_id , error_message , html , debug_info ) values ( sysdate , r_prod.pk_id , v_error_message , v_html , v_debug_info ) ; end loop; close c_prod; exception when others then debug_info#(v_debug_info, 'v_url=' || v_url || ' : ' || 'sqlerrm=' || sqlerrm); -- insert into ted_amazon_logs ( log_date , prod_pk_id , error_message , html , debug_info ) values ( sysdate , r_prod.pk_id , dbms_utility.format_error_stack , v_html , v_debug_info ) ; -- always commit what we have so far commit; raise; end upd; end ted_amazon;

Scheduled Job

Job
	Job Name	TED_AMAZON_UPD
	Job Type	PLSQL_BLOCK
	Job Action	ted_amazon.upd;
	Schedule Type	CALENDAR
	Start Date	16-NOV-20 12.00.00.000000 AM +01:00
	Repeat Interval	Freq=Hourly