SHARE
TWEET

Untitled

a guest Oct 10th, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PACKAGE BODY pck_extract IS
  2.  
  3.    e_extraction EXCEPTION;
  4.  
  5.    -- **********************************************
  6.    -- * INTITALIZES THE t_info_extractions TABLE   *
  7.    -- **********************************************
  8.    PROCEDURE initialize_extractions_table (p_clean_before BOOLEAN) IS
  9.       v_source_table VARCHAR2(100);
  10.    BEGIN
  11.       BEGIN
  12.          pck_log.write_log('  Initializing data required for extraction ["INITIALIZE_EXTRACTIONS_TABLE"]');
  13.          IF p_clean_before=TRUE THEN
  14.             pck_log.write_log('    Deleting previous data');
  15.             DELETE FROM t_info_extractions;
  16.             pck_log.write_log('      Done!');
  17.  
  18.             pck_log.write_log('    Deleting %_new and %_old data');
  19.             DELETE FROM t_data_managers_new;
  20.             DELETE FROM t_data_managers_old;
  21.             DELETE FROM t_data_stores_new;
  22.             DELETE FROM t_data_stores_old;
  23.             DELETE FROM t_data_celsius;
  24.             pck_log.write_log('      Done!');
  25.          END IF;
  26.       EXCEPTION
  27.          WHEN OTHERS THEN
  28.             pck_log.write_uncomplete_task_msg;
  29.             RAISE e_extraction;
  30.       END;
  31.  
  32.       INSERT INTO t_info_extractions (last_timestamp,source_table_name) VALUES (NULL,'view_produtos@DBLINK_SADSB');
  33.       INSERT INTO t_info_extractions (last_timestamp,source_table_name) VALUES (NULL,'view_promocoes@DBLINK_SADSB');
  34.       INSERT INTO t_info_extractions (last_timestamp,source_table_name) VALUES (NULL,'view_vendas@DBLINK_SADSB');
  35.       INSERT INTO t_info_extractions (last_timestamp,source_table_name) VALUES (NULL,'view_linhasvenda@DBLINK_SADSB');
  36.       INSERT INTO t_info_extractions (last_timestamp,source_table_name) VALUES (NULL,'view_linhasvenda_promocoes@DBLINK_SADSB');
  37.       INSERT INTO t_info_extractions (last_timestamp,source_table_name) VALUES (NULL,'view_clientes@DBLINK_SADSB');
  38.       pck_log.write_log('    Done!');
  39.    EXCEPTION
  40.       WHEN OTHERS THEN
  41.          pck_log.write_uncomplete_task_msg;
  42.          RAISE e_extraction;
  43.    END;
  44.  
  45.  
  46.  
  47.    -- ********************************************************************
  48.    -- *                     TABLE_EXTRACT                                *
  49.    -- *                                                                  *
  50.    -- * EXTRACT NEW AND CHANGED ROWS FROM SOURCE TABLE                   *
  51.    -- * IN                                                               *
  52.    -- *   p_source_table: the source table/view to use                   *
  53.    -- *   p_attributes_src: list of attributes to extract from           *
  54.    -- *   p_attributes_dest: list of attributes to fill                  *
  55.    -- *   p_dsa_table: name of the t_data_* table to fill                *
  56.    -- ********************************************************************
  57.   PROCEDURE table_extract (p_source_table VARCHAR2, p_DSA_table VARCHAR2, p_attributes_src VARCHAR2, p_attributes_dest VARCHAR2) IS
  58.       v_end_date TIMESTAMP;
  59.       v_start_date t_info_extractions.LAST_TIMESTAMP%TYPE;
  60.       v_sql  VARCHAR2(1000);
  61.    BEGIN
  62.       pck_log.write_log('  Extracting data ["TABLE_EXTRACT ('||UPPER(p_source_table)||')"]');
  63.       pck_log.rowcount(p_DSA_table,'Before');    -- Logs how many rows the destination table initially contains
  64.  
  65.       -- CLEAN DESTINATION TABLE
  66.       EXECUTE IMMEDIATE 'DELETE FROM '||p_dsa_table;
  67.  
  68.          --  find the date of change of the last record extracted in the previous extraction
  69.          v_sql:='SELECT last_timestamp FROM t_info_extractions WHERE UPPER(source_table_name)='''||UPPER(p_source_table)||'''';
  70.          EXECUTE IMMEDIATE v_sql INTO v_start_date;
  71.  
  72.          --    ---------------------
  73.          --   |   FISRT EXTRACTION  |
  74.          --    ---------------------
  75.         IF v_start_date IS NULL THEN
  76.  
  77.             -- FIND THE DATE OF CHANGE OF THE MOST RECENTLY CHANGED RECORD IN THE SOURCE TABLE
  78.             -- SOMETHING IS MISSING
  79.             v_sql:='SELECT MAX(src_last_changed) from '||p_source_table;
  80.  
  81.             EXECUTE IMMEDIATE v_sql INTO v_end_date;
  82.  
  83.  
  84.             -- EXTRACT ALL RELEVANT RECORDS FROM THE SOURCE TABLE TO THE DSA
  85.             -- SOMETHING IS MISSING
  86.            
  87.             v_sql:='INSERT INTO '||p_dsa_table||' ('||p_attributes_dest||') SELECT '||p_attributes_src||' FROM '||p_source_table||' WHERE src_last_changed <= :1';
  88.            
  89.             EXECUTE IMMEDIATE v_sql USING v_end_date;
  90.  
  91.             -- UPDATE THE t_info_extractions TABLE
  92.            
  93.             v_sql:='UPDATE t_info_extractions SET last_timestamp = :1 WHERE UPPER(source_table_name)='''||UPPER(p_source_table)||'''';
  94.            
  95.             EXECUTE IMMEDIATE v_sql USING v_end_date;
  96.          ELSE
  97.          --    -------------------------------------
  98.          --   |  OTHER EXTRACTIONS AFTER THE FIRST  |
  99.          --    -------------------------------------
  100.             -- FIND THE DATE OF CHANGE OF THE MOST RECENTLY CHANGED RECORD IN THE SOURCE TABLE
  101.             v_sql:='SELECT MAX(src_last_changed) from '||p_source_table||' where src_last_changed > :1';
  102.  
  103.             EXECUTE IMMEDIATE v_sql INTO v_end_date USING v_start_date;
  104.  
  105.             IF v_end_date>v_start_date THEN
  106.                -- EXTRACT ALL RELEVANT RECORDS FROM THE SOURCE TABLE TO THE DSA
  107.                v_sql:='INSERT INTO '||p_dsa_table||' ('||p_attributes_dest||') SELECT '||p_attributes_src||' FROM '||p_source_table||' WHERE src_last_changed > :1 AND src_last_changed <= :2';
  108.  
  109.                EXECUTE IMMEDIATE v_sql USING v_start_date, v_end_date;
  110.  
  111.                -- UPDATE THE t_info_extractions TABLE
  112.                -- SOMETHING IS MISSING
  113.                v_sql:='UPDATE t_info_extractions SET last_timestamp = :1 WHERE UPPER(source_table_name)='''||UPPER(p_source_table)||'''';
  114.            
  115.              EXECUTE IMMEDIATE v_sql USING v_end_date;
  116.            
  117.             END IF;
  118.          END IF;
  119.  
  120.       pck_log.write_log('    Done!');
  121.       pck_log.rowcount(p_DSA_table,'After');    -- Logs how many rows the destination table now contains
  122.    EXCEPTION
  123.       WHEN OTHERS THEN
  124.          pck_log.write_uncomplete_task_msg;
  125.          RAISE e_extraction;
  126.    END;
  127.  
  128.  
  129.    -- **************************************************************
  130.    -- *                       FILE_EXTRACT                         *
  131.    -- *                                                            *
  132.    -- * EXTRACT ROWS FROM SOURCE FILE                              *
  133.    -- * IN                                                         *
  134.    -- *    p_external_table: the external table to use             *
  135.    -- *    p_attributes_src: list of attributes to extract         *
  136.    -- *    p_attributes_dest: list of attributes to fill           *
  137.    -- *    p_dsa_table_new: name of the t_data_*_new table to fill *
  138.    -- *    p_dsa_table_old: name of the t_data_*_old table to fill *
  139.    -- **************************************************************
  140.    PROCEDURE file_extract (p_external_table VARCHAR2, p_attributes_src VARCHAR2, p_attributes_dest VARCHAR2, p_dsa_table_new VARCHAR2, p_dsa_table_old VARCHAR2) IS
  141.       v_sql  VARCHAR2(1000);
  142.    BEGIN
  143.       pck_log.write_log('  Extracting data ["FILE_EXTRACT ('||UPPER(p_external_table)||')"]');      
  144.       pck_log.rowcount(p_dsa_table_new,'Before');    -- Logs how many rows the destination table initially contains
  145.  
  146.       -- CLEAN _old TABLE
  147.       EXECUTE IMMEDIATE 'DELETE FROM '||p_dsa_table_old;
  148.  
  149.       -- Copy data from NEW to OLD
  150.       EXECUTE IMMEDIATE 'INSERT INTO '||p_dsa_table_old||' ('||p_attributes_dest||') SELECT '||p_attributes_dest||' FROM '||p_dsa_table_new;
  151.  
  152.       -- CLEAN _new TABLE
  153.       EXECUTE IMMEDIATE 'DELETE FROM '||p_dsa_table_new;
  154.  
  155.       -- Copy data from Ext file to New table
  156.       EXECUTE IMMEDIATE 'INSERT INTO '||p_dsa_table_new||' ('||p_attributes_dest||') SELECT '||p_attributes_src||' FROM '||p_external_table;
  157.  
  158.       -- records the operation's SUCCESSFUL ending
  159.       pck_log.write_log('    Done!');
  160.       pck_log.rowcount(p_dsa_table_new,'After');    -- Logs how many rows the destination table now contains
  161.    EXCEPTION
  162.       WHEN OTHERS THEN
  163.          pck_log.write_uncomplete_task_msg;
  164.          RAISE e_extraction;
  165.    END;
  166.  
  167.  
  168.  
  169.    -- ********************************************************************
  170.    -- *                TABLE_EXTRACT_NON_INCREMENTAL                     *
  171.    -- *                                                                  *
  172.    -- * EXTRACT ROWS FROM SOURCE TABLE IN NON INCREMENTAL WAY            *
  173.    -- * IN: (same as table_extract)                                      *
  174.    -- ********************************************************************
  175.    PROCEDURE table_extract_non_incremental (p_source_table VARCHAR2, p_DSA_table VARCHAR2, p_attributes_src VARCHAR2, p_attributes_dest VARCHAR2) IS
  176.       v_sql  VARCHAR2(1000);
  177.    BEGIN
  178.       pck_log.write_log('  Extracting data ["TABLE_EXTRACT_NON_INCREMENTAL ('||UPPER(p_source_table)||')"]');
  179.       pck_log.rowcount(p_DSA_table,'Before');    -- Logs how many rows the destination table initially contains
  180.       -- LIMPAR A TABELA DESTINO
  181.       EXECUTE IMMEDIATE 'DELETE FROM '||p_DSA_table;
  182.  
  183.       -- extrair TODOS os registos da tabela fonte para a tabela correspondente na DSA
  184.       v_sql:='INSERT INTO '||p_DSA_table||'('|| p_attributes_dest||',rejected_by_screen) SELECT '||p_attributes_src||',''0'' FROM '||p_source_table;
  185.       EXECUTE IMMEDIATE v_sql;
  186.  
  187.       pck_log.write_log('    Done!');    
  188.       pck_log.rowcount(p_DSA_table,'After');    -- Logs how many rows the destination table now contains
  189.    EXCEPTION
  190.       WHEN OTHERS THEN
  191.          pck_log.write_uncomplete_task_msg;
  192.          RAISE e_extraction;
  193.    END;
  194.  
  195.  
  196.    PROCEDURE web_extract (p_src_link VARCHAR2, p_DSA_table VARCHAR2, p_src_attributes VARCHAR2, p_target_attributes VARCHAR2) IS
  197.       l_data CLOB;
  198.  
  199.       PROCEDURE store_json_data (p_data VARCHAR2, p_src_attributes VARCHAR2, p_target_attributes VARCHAR2, p_target_table VARCHAR2) IS
  200.          l_pos_inicial PLS_INTEGER;
  201.          l_pos_final PLS_INTEGER;
  202.          l_pos_atual_json PLS_INTEGER;
  203.          l_pos_final_registo PLS_INTEGER;
  204.          l_pos_atributo PLS_INTEGER;
  205.          l_pos_dados_inicial PLS_INTEGER;
  206.          l_pos_dados_final PLS_INTEGER;
  207.          l_registo VARCHAR2(1000);
  208.          l_atributo VARCHAR2(40);
  209.          v_sql VARCHAR2(500);
  210.          l_valores VARCHAR2(100);
  211.          l_pos_virgula  PLS_INTEGER;
  212.          l_string_to_parse  VARCHAR2(500) := p_src_attributes||',';
  213.          l_pos_atual_parse_src PLS_INTEGER;
  214.          l_aux PLS_INTEGER;
  215.       BEGIN
  216.          l_pos_inicial:= INSTR(l_data, '[');
  217.          l_pos_final:= INSTR(l_data, ']', l_pos_inicial);
  218.          l_pos_atual_json := l_pos_inicial;
  219.          /* ENQUANTO “]” não atingido FAZ */
  220.          LOOP
  221.             -- reinicia a lista de valores a inserir
  222.             l_valores:='';
  223.             l_pos_atual_json:=INSTR(l_data,'{',l_pos_atual_json);
  224.             -- termina quando não houver mais registos json para ler
  225.             EXIT WHEN l_pos_atual_json=0;
  226.  
  227.             l_pos_final_registo:=INSTR(l_data,'}',l_pos_atual_json);
  228.             -- lê o registo atual dos dados JSON
  229.             l_registo:=SUBSTR(l_data,l_pos_atual_json,l_pos_final_registo-l_pos_atual_json);
  230.             -- lê atributos solicitados, atualmente só 1 permitido
  231.             l_pos_atual_parse_src:=1;
  232.             l_aux:=0;
  233.             LOOP  -- faz o parse dos atributos origem, 1 a um; por cada um lê-o registo JSON
  234.                l_pos_virgula:=INSTR(l_string_to_parse,',',l_pos_atual_parse_src);
  235.                EXIT WHEN l_pos_virgula=0;
  236.                IF (l_aux>0) THEN
  237.                   l_valores:=l_valores||',';
  238.                END IF;
  239.                l_aux:=l_aux+1;
  240.                l_atributo:='"'||SUBSTR(l_string_to_parse,l_pos_atual_parse_src,l_pos_virgula-l_pos_atual_parse_src)||'": ';
  241.                l_pos_atual_parse_src:=l_pos_virgula+1;
  242.                -- procura o atributo dentro do registo JSON
  243.                l_pos_atributo:=INSTR(l_data,l_atributo,l_pos_atual_json);
  244.                IF (l_pos_atributo>0) THEN
  245.                   l_pos_dados_inicial:=l_pos_atributo+LENGTH(l_atributo);
  246.                   l_pos_dados_final:=INSTR(l_data,',',l_pos_dados_inicial);
  247.                   l_valores:=l_valores||REPLACE(SUBSTR(l_data,l_pos_dados_inicial,l_pos_dados_final-l_pos_dados_inicial),'"');
  248.                END IF;
  249.                -- stores the data into the target table          
  250.             END LOOP;   -- fim do parse dos atributos origem
  251.             l_pos_atual_json:=l_pos_final_registo;
  252.             v_sql:='INSERT INTO '||p_target_table||'('||p_target_attributes||') VALUES ('||l_valores||')';
  253.             EXECUTE IMMEDIATE v_sql;
  254.          END LOOP;
  255.       END;
  256.  
  257.    BEGIN
  258.       pck_log.write_log('  Extracting data ["WEB_EXTRACT ('||UPPER(p_src_link)||')"]');
  259.       pck_log.rowcount(p_DSA_table,'Before');    -- Logs how many rows the destination table initially contains
  260.       -- LIMPAR A TABELA DESTINO
  261.       EXECUTE IMMEDIATE 'DELETE FROM '||p_DSA_table;
  262.  
  263.       /* read the JSON data from the webpage */
  264.       l_data:=pck_utilities.read_web_data(p_src_link);
  265.  
  266.       /* parse the data to store the necessary data*/
  267.       store_json_data (l_data, p_src_attributes, p_target_attributes, p_DSA_table);
  268.  
  269.       pck_log.write_log('    Done!');    
  270.       pck_log.rowcount(p_DSA_table,'After');    -- Logs how many rows the destination table now contains
  271.    EXCEPTION
  272.       WHEN OTHERS THEN
  273.          pck_log.write_uncomplete_task_msg;
  274.          RAISE e_extraction;
  275.    END;
  276.  
  277.    -- ***************************************************************************************
  278.    -- *                                        MAIN                                         *
  279.    -- *                                                                                     *
  280.    -- * EXECUTES THE EXTRACTION PROCESS                                                     *
  281.    -- * IN                                                                                  *
  282.    -- *     p_initialize: TRUE=t_info_extractions will be cleaned and then filled           *
  283.    -- ***************************************************************************************
  284.    PROCEDURE main (p_initialize BOOLEAN) IS
  285.    BEGIN
  286.       pck_log.clean;
  287.       pck_log.write_log('*****  EXTRACT  EXTRACT  EXTRACT  EXTRACT  EXTRACT  EXTRACT  EXTRACT  *****');      -- DUPLICATES THE LAST ITERATION AND THE CORRESPONDING SCREEN SCHEDULE
  288.  
  289.       -- INITIALIZE THE EXTRACTION TABLE t_info_extractions
  290.       IF p_initialize = TRUE THEN
  291.          initialize_extractions_table(TRUE);
  292.       END IF;
  293.  
  294.       -- EXTRACT FROM SOURCE TABLES
  295.  
  296.       -- SOMETHING IS MISSING: maybe... a table extraction
  297.      
  298.       table_extract('view_clientes@dblink_sadsb',
  299.       't_data_customers',
  300.       'src_cod_cliente,src_numero_cartao,src_nome,src_morada,src_localidade,src_distrito,src_codigo_postal,src_telefone,src_sexo,src_idade,src_estado_civil',
  301.       'id,card_number,name,address,location,district,zip_code,phone_nr,gender,age,marital_status');
  302.      
  303.       table_extract('view_vendas@dblink_sadsb',
  304.       't_data_sales',
  305.       'src_id,src_sale_date,src_store_id,src_customer_id',
  306.       'id,sale_date,store_id,customer_id');
  307.      
  308.       table_extract('view_produtos@dblink_sadsb','t_data_products','src_id,src_name,src_brand,src_width,src_height,src_depth,src_pack_type,src_calories_100g,src_liq_weight,src_category_id','id,name,brand,width,height,depth,pack_type,calories_100g,liq_weight,category_id');
  309.       table_extract('view_linhasvenda@dblink_sadsb', 't_data_linesofsale', 'src_id,src_sale_id,src_product_id,src_quantity,src_ammount_paid,src_line_date', 'id,sale_id,product_id,quantity,ammount_paid,line_date');
  310.       table_extract('view_promocoes@dblink_sadsb', 't_data_promotions','src_id,src_name,src_start_date,src_end_date,src_reduction,src_on_outdoor,src_on_tv','id,name,start_date,end_date,reduction,on_outdoor,on_tv');
  311.       table_extract('view_linhasvenda_promocoes@dblink_sadsb', 't_data_linesofsalepromotions','src_line_id,src_promo_id','line_id,promo_id');
  312.       table_extract_non_incremental('view_categorias@dblink_sadsb', 't_data_categories', 'src_id,src_name', 'id,name');
  313.  
  314.       -- SOMETHING IS MISSING: maybe... a file extraction
  315.       file_extract ('t_ext_stores',
  316.                     'name,refer,building,address,zip_code,city,district,phone_nrs,fax_nr,closure_date',
  317.                      'name,reference,building,address,zip_code,location,district,telephones,fax,closure_date','t_data_stores_new', 't_data_stores_old');
  318.                      
  319.       file_extract ('t_ext_managers', 'refer,man_name,man_since',
  320.                     'reference,manager_name,manager_since', 't_data_managers_new', 't_data_managers_old');
  321.  
  322.       -- now the web data
  323.       NULL;
  324.  
  325.       pck_log.write_log('  All extracted data commited to database.');
  326.    EXCEPTION
  327.       WHEN e_extraction THEN
  328.          pck_log.write_halt_msg;
  329.          ROLLBACK;
  330.       WHEN OTHERS THEN
  331.          ROLLBACK;
  332.          pck_log.write_uncomplete_task_msg;
  333.          pck_log.write_halt_msg;
  334.    END;
  335.  
  336. END pck_extract;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top