ODI 12C - Procedure Purge ODI_SESSIONS - Log 1

 Di seguito una procedura pl-sql per effettuare la purge dei log ODI. La procedura permette di effettuare la delete dei record all'interno di alcune tabelle del repository di ODI.

CREATE OR REPLACE PROCEDURE purge_odi_sessions AS
    a number;
    b number;
    type tableArray IS VARRAY(15) OF VARCHAR2(30);
    tableNames tableArray;
    tableCount integer;
    query_string varchar(2000);
    filter_cond varchar(2000);
    concatString varchar(3);
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    tbl_name varchar(100);
    con_name varchar(100);
    
BEGIN    
   tableNames := tableArray('<Inserire il nome delle tabelle tra apici singoli>');
   tableCount := tableNames.count;
   filter_cond:=' ';
   concatString:='''';
    FOR i in 1 .. tableCount LOOP
      filter_cond := filter_cond || concatString ||  tableNames(i);
      concatString:=''',''';
    END LOOP;
   filter_cond:=filter_cond||'''';
    dbms_output.put_line('===========================================' );
dbms_output.put_line('=== DISABLING FORIEGN KEYS ================' );
    
query_string := 'select  UC.TABLE_NAME,UC.CONSTRAINT_NAME
             FROM USER_CONSTRAINTS  UC,
         USER_CONS_COLUMNS UCC
   WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
     AND uc.constraint_type = ''R'' and UCC.TABLE_NAME in ('||filter_cond||') 
   ORDER BY UC.TABLE_NAME,
            UC.R_CONSTRAINT_NAME,
            UCC.TABLE_NAME,
            UCC.COLUMN_NAME';
    OPEN c FOR query_string;
    LOOP
        FETCH c INTO tbl_name, con_name;
         EXIT WHEN c%NOTFOUND;
         DBMS_OUTPUT.put_line ('EXECUTING : ' || 'ALTER TABLE '||tbl_name||' DISABLE CONSTRAINT '||con_name||';');
         execute immediate 'ALTER TABLE '||tbl_name||' DISABLE CONSTRAINT '||con_name;
    END LOOP;
    CLOSE c;
         
    dbms_output.put_line('===========================================' );
dbms_output.put_line('==== CLEANING SNP_EXP_TXT_HEADER =========' );

execute immediate 'ALTER TABLE SNP_EXP_TXT_HEADER NOLOGGING';
dbms_output.put_line('EXECUTING : DELETE FROM SNP_EXP_TXT_HEADER WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS ); WITH OUT LOGGING' );
    
DELETE FROM SNP_EXP_TXT_HEADER WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS );
    
execute immediate 'ALTER TABLE SNP_EXP_TXT_HEADER LOGGING';
    
    dbms_output.put_line('===========================================' );
dbms_output.put_line('======== TRUNCATING THE DATA =============' );
    
    FOR i in 1 .. tableCount LOOP
      dbms_output.put_line('EXECUTING : ' || 'TRUNCATE TABLE '|| tableNames(i) );
      execute immediate 'TRUNCATE TABLE '|| tableNames(i);
    
    END LOOP;
 
    dbms_output.put_line('===========================================' );
dbms_output.put_line('========= ENABLING FORIEGN KEYS ===========' );
     
    OPEN c FOR query_string;
    LOOP
        FETCH c INTO tbl_name, con_name;
         EXIT WHEN c%NOTFOUND;
         DBMS_OUTPUT.put_line ('EXECUTING : ' || 'ALTER TABLE '||tbl_name||' ENABLE CONSTRAINT '||con_name||';');
         execute immediate 'ALTER TABLE '||tbl_name||' ENABLE CONSTRAINT '||con_name;
     END LOOP;
    CLOSE c;
   
   DBMS_OUTPUT.PUT_LINE('Process Completed ');
 END purge_odi_sessions;
/
-- Execute the stored proc using below commads
-- set serveroutput on;
-- exec purge_odi_sessions;

---------------------------------------------------------------------------------------------------------------------------

Funziona ma non fa altro che seguire quanto viene fatto nell'oditools utilizzato per fare la purge, quindi l'inconveniente maggiore è il tempo impiegato per cancellare i dati dalle tabelle.

ODI 12c - repository tables and their description

 In the post the description of tables present in the repository ODI 12c.

• DESIGN - MASTER - RUNTIME

ORDS - RDBMS : Retrieve a JWT Access Token Using the Auth REST Call pl-sql

Di seguito una funzione per leggere un Token JWT utilizzando una procedura pl-sql. Di seguito diamo una definizione di Token JWT:

Un JSON Web Token è un token di accesso standardizzato secondo RFC 7519 e consente lo scambio sicuro di dati tra due parti. Contiene tutte le informazioni importanti su un’entità, in modo che non sia necessaria alcuna interrogazione del database e che la sessione non debba essere memorizzata sul server.

Un token JWT si presenta nel seguente formato:

Token = base64Header + ‘.’ + base64Payload + ‘.’ + signature

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.dyt0CoTl4WoVjAHI9Q_CwSKhl6d_9rhM3NrXuJttkao

Per poter decodificare quanto presente nel seguente Token possiamo utilizzare la seguente funzione:

create or replace FUNCTION get_token_information (
         p_token VARCHAR2
    ) RETURN VARCHAR2 AS
        l_token apex_jwt.t_token;
        l_keys apex_t_varchar2;
        l_l_keys varchar2(4000);
        v_l_keys varchar2(4000);
        v_alg varchar2(20);
    BEGIN
     l_token := apex_jwt.decode (p_value => p_token);
    ------------------------------------------------- 
    ------Header 
    ------------------------------------------------- 
    sys.dbms_output.put_line('--- Header ---');
    apex_json.parse(l_token.header);
    l_keys := apex_json.get_members('.');
    for i in 1 .. l_keys.count loop
        l_l_keys:=l_keys(i)||'='||apex_json.get_varchar2(l_keys(i));
               v_alg:=apex_json.get_varchar2(l_keys(i));
        dbms_output.put_line(l_l_keys);
    end loop;
    sys.dbms_output.put_line('--- Payload ---');
    ------------------------------------------------- 
    ------ Payload  ---
    ------------------------------------------------- 
    apex_json.parse(l_token.payload);
    l_keys := apex_json.get_members('.'); 
     dbms_output.put_line('numero oggetti:' ||l_keys.count);
   for j in 1 .. l_keys.count loop 
        sys.dbms_output.put_line(l_keys(j)||'='||apex_json.get_varchar2(l_keys(j)));
    if j<l_keys.count then
    l_l_keys:=l_keys(j)||'='||apex_json.get_varchar2(l_keys(j));
  end if;
    end loop;
    return 'OK'
 END get_token_information; 

Eseguendo la funzione ottiene quanto segue:

DECLARE
  P_TOKEN VARCHAR2(200);
  v_Return VARCHAR2(200);
BEGIN
  P_TOKEN := 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.dyt0CoTl4WoVjAHI9Q_CwSKhl6d_9rhM3NrXuJttkao';
  v_Return := GET_TOKEN_MAIL(
    P_TOKEN => P_TOKEN
  );
 dbms_output.put_line(v_Return);
END;

L'output sarà il seguente:

ORDS - POST Up a BLOB to an Oracle Table via REST

Below the step for load a file binary into a blob field in a oracle table.

• STEP - 1 : Enable the schema oracle for using a REST ORDS.

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,

                       p_schema => 'TEST',

                       p_url_mapping_type => 'BASE_PATH',

                       p_url_mapping_pattern => 'test',

                       p_auto_rest_auth => FALSE);

    commit;

END;

• STEP -2 : Create a table

CREATE TABLE "TEST"."MEDIA_BLOB" 

   ( "ID" NUMBER 

                       GENERATED BY DEFAULT 

                                    AS IDENTITY MINVALUE 1 

                                    MAXVALUE 9999999999999999999999999999 

                                   INCREMENT BY 1 START WITH 1 

                                    BOCACHE  ORDER  

                                    NOCYCLE  NOKEEP  

                                    NOSCALE  NOT NULL ENABLE, 

"TITLE" VARCHAR2(100 BYTE), 

"CONTENT_TYPE" VARCHAR2(100 BYTE), 

"FILE_NAME" VARCHAR2(100 BYTE), 

"CONTENT" BLOB

   )

• STEP - 3: Create Module REST

-- Generated by Oracle SQL Developer REST Data Services 19.2.1.247.2212

-- Exported REST Definitions from ORDS Schema Version 19.2.0.r1991647

-- Schema: TEST   Date: Sun Nov 29 18:22:54 IST 2020

--

BEGIN

  ORDS.ENABLE_SCHEMA(

      p_enabled             => TRUE,

      p_schema              => 'TEST',

      p_url_mapping_type    => 'BASE_PATH',

      p_url_mapping_pattern => 'test',

      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(

      p_module_name    => 'loadblob',

      p_base_path      => '/blob/',

      p_items_per_page =>  25,

      p_status         => 'PUBLISHED',

      p_comments       => NULL);      

  ORDS.DEFINE_TEMPLATE(

      p_module_name    => 'loadblob',

      p_pattern        => 'file',

      p_priority       => 0,

      p_etag_type      => 'HASH',

      p_etag_query     => NULL,

      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(

      p_module_name    => 'loadblob',

      p_pattern        => 'file',

      p_method         => 'POST',

      p_source_type    => 'plsql/block',

      p_items_per_page =>  0,

      p_mimes_allowed  => '',

      p_comments       => NULL,

      p_source         => 

'declare 

 image_id integer;

begin

 insert into media_blob (title,content_type,content) 

             values  (:title,:content_type,:body)

             returning id into image_id;

 :status_code := 201;

 :location := image_id;

end;'

      );

  ORDS.DEFINE_PARAMETER(

      p_module_name        => 'loadblob',

      p_pattern            => 'file',

      p_method             => 'POST',

      p_name               => 'location',

      p_bind_variable_name => 'location',

      p_source_type        => 'RESPONSE',

      p_param_type         => 'INT',

      p_access_method      => 'OUT',

      p_comments           => NULL);      

  ORDS.DEFINE_PARAMETER(

      p_module_name        => 'loadblob',

      p_pattern            => 'file',

      p_method             => 'POST',

      p_name               => 'title',

      p_bind_variable_name => 'title',

      p_source_type        => 'HEADER',

      p_param_type         => 'STRING',

      p_access_method      => 'IN',

      p_comments           => NULL);      

  ORDS.DEFINE_HANDLER(

      p_module_name    => 'loadblob',

      p_pattern        => 'file',

      p_method         => 'GET',

      p_source_type    => 'json/collection',

      p_items_per_page =>  25,

      p_mimes_allowed  => '',

      p_comments       => NULL,

      p_source         => 

'select * from media_blob'

      );

  COMMIT; 

END;

• STEP - 4 : Insert file into table using a curl

curl -s -o response.txt -w "%{http_code}" 

--location 

--request POST 'http://localhost:9999/ords/test/blob/file' 

--header 'title: test' 

--header 'Content-Type: image/gif' 

--data-binary '@/home/oracle/export.xlsx'

• STEP - 5 : In the table

ORDS - Impaginazione dei risultati di output di un REST

Di seguito un esempio di come introdurre la paginazione all'interno di un REST, questo può essere utile se il numero di record è elevato e si vuole restituire un risultato il prima possibile in modo da fare vedere prima i primi 1000 record e così via.

Supponiamo di richiamare un pl-sql nel nostro handler:

Vediamo prima la struttura del pl-sql e poi dell'handler. Di seguito la struttura della procedura pl-sql richiamata in un package.


PROCEDURE getElement (  
                          user_id                   in  varchar2,
                          element_type_cd           in  varchar2     default null,

---- Parametri impaginazione                            
                            page        in number default 1,
                            numrows     in number default 1000,
---- Fine Parametri impaginazione

                           
                       OUT_COUNT         OUT NUMBER,            
                       OUT_ELEMENT_TYPES OUT  SYS_REFCURSOR)
AS

--- <dichiarazioni di variabili >
...
---------------------------------------------
---- calcolo paginazione
---------------------------------------------
l_page number := (page * numrows) - numrows;
l_rows number := numrows;

BEGIN


------------------------------
--- count
--------------------------------------------------------
SELECT  COUNT(1) INTO out_count
   FROM    ....

IF OUT_COUNT = 0 THEN
  RAISE NO_DATA_FOUND;
END IF;

------------------------------
--- sys_refcursor
------------------------------

  OPEN OUT_ELEMENT_TYPES FOR
WITH
  js AS (SELECT ...     )
            )
  SELECT  js.*, jt.*
FROM js , jt
offset l_page rows fetch next l_rows rows only;


END getElement;

A questo punto vediamo la definizione della get:

BEGIN
 
GETELEMENT(
    USER_ID => :USER_ID,
    ELEMENT_TYPE_CD => :ELEMENT_TYPE_CD,
    PAGE => nvl(:pag,1),
    NUMROWS => nvl(:numrows,1000),
    OUT_COUNT => :OUT_COUNT,
    OUT_ELEMENT_TYPES => :OUT_ELEMENT_TYPES
  );


END;

 A questo punto occorre definire le variabili di input ed output del REST.

A questo punto basta effettuare una chiamata REST con SOAP_UI o POSTMAN fornendo in input le varibile difinite dal package, se PAGE o NUMROWS non saranno valorizzate in automatico passiamo 1 e 1000, per andare avanti nella paginazione effettuare una seconda chiamata e passare un nuovo numero di pagina o di righe da ritornare.

Finito.