declare
  l_clob clob;
  l_json apex_json.t_values;
  l_temp varchar2(1000);
  l_clob_tab apex_application_global.vc_arr2;
  
tp_article_id NUMBER(10,2);
article_number NUMBER(10,2);
on_sale NUMBER(10,2);
retail_price NUMBER(10,2);
balance_weight NUMBER(10,2);
valve_stem NUMBER(10,2);
excise_tax NUMBER(10,2);
tire_fee NUMBER(10,2);
disposal NUMBER(10,2);
balance_labor NUMBER(10,2);
install_fee NUMBER(10,2);
rear_article NUMBER(10,2);
road_hazard_amount NUMBER(10,2);
shop_hazard_supply_amount NUMBER(10,2);
retail_tax_amount NUMBER(10,2);
wheel_balance_tax_amount NUMBER(10,2);
valve_stem_tax_amount NUMBER(10,2);
excise_tax_tax_amount NUMBER(10,2);
disposal_tax_amount NUMBER(10,2);
shop_hazard_supply_tax_amount NUMBER(10,2);
road_hazard_tax_amount NUMBER(10,2);
total NUMBER(10,2);
rear_on_sale NUMBER(10,2);
rear_retail_price NUMBER(10,2);
rear_balance_weight NUMBER(10,2);
rear_valve_stem NUMBER(10,2);
rear_excise_tax NUMBER(10,2);
rear_tire_fee NUMBER(10,2);
rear_disposal NUMBER(10,2);
rear_balance_labor NUMBER(10,2);
rear_install_fee NUMBER(10,2);
front_discount_amount NUMBER(10,2);
rear_discount_amount NUMBER(10,2);
promo_id NUMBER(10,2);
tpms_vsk_article_number NUMBER(10,2);
tpms_vsk_amount NUMBER(10,2);
tpms_labor_article_number NUMBER(10,2);
tpms_labor_amount NUMBER(10,2);
tpms_vsk_tax_amount NUMBER(10,2);
email_address varchar2(100);
request_date date;
store_number NUMBER(10,2);
first_name varchar2(50);
last_name varchar2(50);
quantity NUMBER(10,2);
model_year NUMBER(10,2);
make_name varchar2(50);
model_name varchar2(50);
submodel varchar2(50);
web_site varchar2(50);

cursor t2 is 
select payload  from tp_tire_quotes_ledger where payload is not null;

  function xxxx(pl_clob in clob) return apex_application_global.vc_arr2 is
    c_max_vc2_size pls_integer := 8100; -- Bug with dbms_lob.substr 8191
    l_offset pls_integer := 1;
    l_clob_length pls_integer;
    vl_clob_tab apex_application_global.vc_arr2;
  begin
    l_clob_length := dbms_lob.getlength(pl_clob);
 
    while l_offset <= l_clob_length loop
      vl_clob_tab(vl_clob_tab.count + 1) :=
        dbms_lob.substr (
         lob_loc => pl_clob,
         amount => least(c_max_vc2_size, l_clob_length - l_offset +1 ),
         offset => l_offset);
 
      l_offset := l_offset + c_max_vc2_size;
    end loop;
    return vl_clob_tab;
  end;
begin
open t2;
--for rec_1 in t2 
loop
fetch t2 into l_clob;
EXIT WHEN t2%NOTFOUND;
--fetch t2 into l_clob;
-- select payload into l_clob from tp_tire_quotes_ledger where quote_id = 1072697;
--l_clob := rec_1;
 
  -- Convert clobtotable
l_clob_tab := xxxx(l_clob);

  -- Parse clob as JSON
  apex_json.parse(
    p_values => l_json,
    p_source => l_clob_tab,
    p_strict => true);
    
tp_article_id := (apex_json.get_varchar2(p_path=>'tireQuoteId',p_values=>l_json));
article_number := (apex_json.get_varchar2(p_path=>'tire.article',p_values=>l_json));
on_sale := 0;
retail_price := (apex_json.get_varchar2(p_path=>'tire.retailPrice',p_values=>l_json));
balance_weight := 0; 
valve_stem := (apex_json.get_varchar2(p_path=>'quoteItem.valveStem',p_values=>l_json));
excise_tax := 0;
tire_fee := (apex_json.get_varchar2(p_path=>'quoteItem.stateEnvironmentalFee',p_values=>l_json));
disposal := (apex_json.get_varchar2(p_path=>'quoteItem.scrapTireRecyclingCharge',p_values=>l_json));
balance_labor :=(apex_json.get_varchar2(p_path=>'quoteItem.wheelBalance',p_values=>l_json));
install_fee := 0;
rear_article := (apex_json.get_varchar2(p_path=>'rearTire.article',p_values=>l_json)); 
road_hazard_amount := 0;
shop_hazard_supply_amount := (apex_json.get_varchar2(p_path=>'quoteItem.shopSupplies',p_values=>l_json));
retail_tax_amount := (apex_json.get_varchar2(p_path=>'cart.totalTax.value',p_values=>l_json));
wheel_balance_tax_amount := 0;
valve_stem_tax_amount := 0;
excise_tax_tax_amount := 0;
disposal_tax_amount := 0;
shop_hazard_supply_tax_amount := 0;
road_hazard_tax_amount := 0;
total := (apex_json.get_varchar2(p_path=>'cart.totalPriceWithTax.value',p_values=>l_json));
rear_on_sale :=0;
rear_retail_price := (apex_json.get_varchar2(p_path=>'rearTire.retailPrice',p_values=>l_json));
rear_balance_weight := 0;
rear_valve_stem := 0;
rear_excise_tax := 0;
rear_tire_fee := 0;
rear_disposal := 0;
rear_balance_labor := 0;
rear_install_fee := 0;
front_discount_amount := 0;
rear_discount_amount := 0;
promo_id := 0;
tpms_vsk_article_number := 0;
tpms_vsk_amount := (apex_json.get_varchar2(p_path=>'quoteItem.tpmsValveServiceKit',p_values=>l_json));
tpms_labor_article_number :=0;
tpms_labor_amount :=(apex_json.get_varchar2(p_path=>'quoteItem.tpmsValveServiceKitLabor',p_values=>l_json));
tpms_vsk_tax_amount :=0;
email_address :=(apex_json.get_varchar2(p_path=>'emailAddress',p_values=>l_json));
--request_date :=(apex_json.get_varchar2(p_path=>'createdDate',p_values=>l_json));
store_number :=(apex_json.get_varchar2(p_path=>'storeNumber',p_values=>l_json));
first_name :=(apex_json.get_varchar2(p_path=>'firstName',p_values=>l_json));
last_name :=(apex_json.get_varchar2(p_path=>'lastName',p_values=>l_json));
quantity :=0;
model_year :=(apex_json.get_varchar2(p_path=>'vehicleFitment.year',p_values=>l_json));
make_name :=(apex_json.get_varchar2(p_path=>'vehicleFitment.make',p_values=>l_json));
model_name :=(apex_json.get_varchar2(p_path=>'vehicleFitment.model',p_values=>l_json));
submodel :=(apex_json.get_varchar2(p_path=>'vehicleFitment.submodel',p_values=>l_json));
web_site :=(apex_json.get_varchar2(p_path=>'cart.site',p_values=>l_json));
 
insert into temp_quotes_table values (tp_article_id,article_number,on_sale,retail_price,balance_weight,valve_stem,excise_tax,tire_fee,disposal,balance_labor,install_fee,rear_article,road_hazard_amount,shop_hazard_supply_amount,retail_tax_amount,wheel_balance_tax_amount,valve_stem_tax_amount,excise_tax_tax_amount,disposal_tax_amount,shop_hazard_supply_tax_amount,road_hazard_tax_amount,total,rear_on_sale,rear_retail_price,rear_balance_weight,rear_valve_stem,rear_excise_tax,rear_tire_fee,rear_disposal,rear_balance_labor,rear_install_fee,front_discount_amount,rear_discount_amount,promo_id,tpms_vsk_article_number,tpms_vsk_amount,tpms_labor_article_number,tpms_labor_amount,tpms_vsk_tax_amount,email_address,request_date,store_number,first_name,last_name,quantity,model_year,make_name,model_name,submodel,web_site);

commit;
 end loop;
 close t2;

end;