Start a new topic
Answered

Export Log

Hello, 

I need to export ACP_EXE_BLO from system log table STB_LOG_ACTION_PROP_ACP .

But this column is of type BLOB COMRESS.

I've try : utl_raw.cast_to_varchar2( dbms_lob.substr(utl_compress.lz_uncompress(ACP_EXE_BLO),2000,1))

but I get the error ORA-29294.


I need it to export logs in JSON format to THEIA 


Thanks for your help


Best Answer

Hi Mickael,


Semarchy xDI uses internally the java library java.util.zip.Inflater and java.util.zip.Deflater which are based on the Zlib compression for the field ACP_EXE_BLO, therefore a script action is needed.


With a scripting beanshell action called in a process, you will be able to read your BLOB_COMPRESSED table line and inflate it (decompression).


Here is a code snippet based on your environment information (Semarchy xDI 5.3.2, jdk 11 LTS) for an hardcoded action id in the table. 

For any other xDI version, this code needs to be adapted. The library versions may be upgraded.


Code snippet steps to publish the BLOB content in the session variable 

1/ Create a process with a scripting  beanshell action



2/ Action expression

import java.lang.Class;
import java.lang.ClassNotFoundException;
import java.sql.*;
import java.util.zip.*;
import java.nio.charset.StandardCharsets;
import java.util.zip.Deflater;
import java.util.zip.Inflater;

// Database connection information
String jdbcUser = "%x{$XDILOGSSCHEMA/tech:jdbcUser()}x%";
String jdbcPassword = "%x{$XDILOGSSCHEMA/tech:jdbcPassword()}x%";
String jdbcUrl = "%x{$XDILOGSSCHEMA/tech:jdbcUrl()}x%";
String jdbcClass = "%x{$XDILOGSSCHEMA/tech:jdbcDriver()}x%";
String module = "%x{$XDILOGSSCHEMA/tech:module()}x%";
String SchemaName = "%x{$XDILOGSSCHEMA/tech:schemaName()}x%";

// Connection Class
cl = com.indy.engine.core.module.classloader.ClassLoaderServiceProvider.INSTANCE.getClassLoader(module, jdbcClass);
clazz = Class.forName(jdbcClass, true, cl);
drv = clazz.newInstance();

// Connection instance
Properties props = new Properties();
props.setProperty("user", jdbcUser);
props.setProperty("password", jdbcPassword);
con = drv.connect(jdbcUrl, props);

// Input value
spSessId = "6471496801889b4d0368e0f201f402fd";
spActId = "9eccd4f700c222fd7bd317b7c6d47d3d";

// Query execution
String CallString = "";
CallString = CallString + " SELECT A.SESS_ID, A.SESS_ITER, A.ACT_ID, A.ACT_ITER, A.ACP_EXE_FORMAT, A.ACP_EXE_BLO ";
CallString = CallString + " FROM " + SchemaName + ".STB_LOG_ACTION_PROP_ACP A ";
CallString = CallString + " WHERE 1=1 " ;
CallString = CallString + " AND A.SESS_ID = '" + spSessId + "'" ;
CallString = CallString + " AND A.ACT_ID = '" + spActId + "'" ;
CallString = CallString + " AND A.ACP_SHORT_NAME = 'CORE_ACTION_TXT'  ";
CallString = CallString + " AND A.ACP_EXE_FORMAT = 'BLOB_COMPRESSED' ";

Statement s = con.createStatement();      //creating statement
ResultSet rs = s.executeQuery(CallString);   //executing statement

// Compute resultset
while(rs.next()){

  String sess_id = rs.getString("SESS_ID");
  String act_id = rs.getString("ACT_ID");
  String sess_iter = rs.getString("SESS_ITER");
  String act_iter = rs.getString("ACT_ITER");
  String act_exe_format = rs.getString("ACP_EXE_FORMAT");

  java.sql.Blob blob = rs.getBlob("ACP_EXE_BLO");
  
  // BufferedInputStream
  byte[] data = new byte[(int) blob.length()];
  BufferedInputStream instream = null;
  try {
    instream = new BufferedInputStream(blob.getBinaryStream());
    instream.read(data);
  } catch (Exception ex) {
    throw new Exception(ex.getMessage());
  } finally {
    instream.close();
  }
  
  // ByteArrayInputStream
  int length = 0;
  int chunk = 32765;
  ByteArrayInputStream bis = new ByteArrayInputStream(data);
  byte[] buffer = new byte[chunk];
  ByteArrayOutputStream bos = new ByteArrayOutputStream();
  while ((length = bis.read(buffer, 0, chunk)) != -1) {
    bos.write(buffer, 0, length);
  }
  bos.close();
  bis.close();
  
  // decompresser Inflater
       Inflater decompresser = new Inflater(true);
       decompresser.setInput(buffer, 0, chunk);
    
  // max size, impossible to be higher than 32765 kr
  byte[] result = new byte[32765];
  int resultLength = decompresser.inflate(result);
  decompresser.end();
  
  // Decode the bytes into a String
  String outstr = new String(result, 0, resultLength, "UTF-8");
  __ctx__.publishVariable("~/SESS#"+sess_id+"#ACT#"+act_id,  outstr); 
}
con.close();    //closing connection

 


Answer

Hi Mickael,


Semarchy xDI uses internally the java library java.util.zip.Inflater and java.util.zip.Deflater which are based on the Zlib compression for the field ACP_EXE_BLO, therefore a script action is needed.


With a scripting beanshell action called in a process, you will be able to read your BLOB_COMPRESSED table line and inflate it (decompression).


Here is a code snippet based on your environment information (Semarchy xDI 5.3.2, jdk 11 LTS) for an hardcoded action id in the table. 

For any other xDI version, this code needs to be adapted. The library versions may be upgraded.


Code snippet steps to publish the BLOB content in the session variable 

1/ Create a process with a scripting  beanshell action



2/ Action expression

import java.lang.Class;
import java.lang.ClassNotFoundException;
import java.sql.*;
import java.util.zip.*;
import java.nio.charset.StandardCharsets;
import java.util.zip.Deflater;
import java.util.zip.Inflater;

// Database connection information
String jdbcUser = "%x{$XDILOGSSCHEMA/tech:jdbcUser()}x%";
String jdbcPassword = "%x{$XDILOGSSCHEMA/tech:jdbcPassword()}x%";
String jdbcUrl = "%x{$XDILOGSSCHEMA/tech:jdbcUrl()}x%";
String jdbcClass = "%x{$XDILOGSSCHEMA/tech:jdbcDriver()}x%";
String module = "%x{$XDILOGSSCHEMA/tech:module()}x%";
String SchemaName = "%x{$XDILOGSSCHEMA/tech:schemaName()}x%";

// Connection Class
cl = com.indy.engine.core.module.classloader.ClassLoaderServiceProvider.INSTANCE.getClassLoader(module, jdbcClass);
clazz = Class.forName(jdbcClass, true, cl);
drv = clazz.newInstance();

// Connection instance
Properties props = new Properties();
props.setProperty("user", jdbcUser);
props.setProperty("password", jdbcPassword);
con = drv.connect(jdbcUrl, props);

// Input value
spSessId = "6471496801889b4d0368e0f201f402fd";
spActId = "9eccd4f700c222fd7bd317b7c6d47d3d";

// Query execution
String CallString = "";
CallString = CallString + " SELECT A.SESS_ID, A.SESS_ITER, A.ACT_ID, A.ACT_ITER, A.ACP_EXE_FORMAT, A.ACP_EXE_BLO ";
CallString = CallString + " FROM " + SchemaName + ".STB_LOG_ACTION_PROP_ACP A ";
CallString = CallString + " WHERE 1=1 " ;
CallString = CallString + " AND A.SESS_ID = '" + spSessId + "'" ;
CallString = CallString + " AND A.ACT_ID = '" + spActId + "'" ;
CallString = CallString + " AND A.ACP_SHORT_NAME = 'CORE_ACTION_TXT'  ";
CallString = CallString + " AND A.ACP_EXE_FORMAT = 'BLOB_COMPRESSED' ";

Statement s = con.createStatement();      //creating statement
ResultSet rs = s.executeQuery(CallString);   //executing statement

// Compute resultset
while(rs.next()){

  String sess_id = rs.getString("SESS_ID");
  String act_id = rs.getString("ACT_ID");
  String sess_iter = rs.getString("SESS_ITER");
  String act_iter = rs.getString("ACT_ITER");
  String act_exe_format = rs.getString("ACP_EXE_FORMAT");

  java.sql.Blob blob = rs.getBlob("ACP_EXE_BLO");
  
  // BufferedInputStream
  byte[] data = new byte[(int) blob.length()];
  BufferedInputStream instream = null;
  try {
    instream = new BufferedInputStream(blob.getBinaryStream());
    instream.read(data);
  } catch (Exception ex) {
    throw new Exception(ex.getMessage());
  } finally {
    instream.close();
  }
  
  // ByteArrayInputStream
  int length = 0;
  int chunk = 32765;
  ByteArrayInputStream bis = new ByteArrayInputStream(data);
  byte[] buffer = new byte[chunk];
  ByteArrayOutputStream bos = new ByteArrayOutputStream();
  while ((length = bis.read(buffer, 0, chunk)) != -1) {
    bos.write(buffer, 0, length);
  }
  bos.close();
  bis.close();
  
  // decompresser Inflater
       Inflater decompresser = new Inflater(true);
       decompresser.setInput(buffer, 0, chunk);
    
  // max size, impossible to be higher than 32765 kr
  byte[] result = new byte[32765];
  int resultLength = decompresser.inflate(result);
  decompresser.end();
  
  // Decode the bytes into a String
  String outstr = new String(result, 0, resultLength, "UTF-8");
  __ctx__.publishVariable("~/SESS#"+sess_id+"#ACT#"+act_id,  outstr); 
}
con.close();    //closing connection

 

Hello, 

The example :

INSERT INTO ETD_LOG_VAL_1Q.STB_LOG_ACTION_PROP_ACP

(SESS_ID, SESS_ITER, ACT_ID, ACT_ITER, ACP_NAME, ACP_SHORT_NAME, ACP_TYPE, ACP_CUMUL, ACP_EXE_VAR, ACP_EXE_CLO, ACP_EXE_BLO, ACP_EXE_FORMAT, ACP_SRC_VAR, ACP_SRC_CLO, ACP_SRC_BLO, ACP_SRC_FORMAT, PTY_TYPE_N, PCA_TYPE_N, ACP_NUM, ACP_BND_VAR, ACP_BND_CLO, ACP_BND_BLO, ACP_BND_FORMAT)

VALUES('6471496801889b4d0368e0f201f402fd', 1, '9eccd4f700c222fd7bd317b7c6d47d3d', 1, 'INSER_METN_TR6R_S/INSER_WRK0_WJ81_A7_TR6R_1/S1-GTS-Staging/STAGING/Register Sub Query/CORE_ACTION_TXT', 'CORE_ACTION_TXT', 'String', NULL, NULL, NULL, '78DAED1C5D6FDBB6F6D9FC157C9384D89E28D98E0334051459C69C5A96AFC434C993813B74DBC3860DDD86E102FDF1979F12295112ED3869D2AAD8224B3C24CFF7393CA404FEFAF4DBA79FFE1E8DEEF3457E88FC0326D7699CC5198C0AB84507FA730CEACD09BE61EDC181FD2600C55DEA82382A1278FF63B283F87689A6AB28CED23DBC49F07D429EAD229C4027F08370E223F29F03A3DD4A7B3A9B84BE03C1883E77C16834627FD8587196ED1FE02EC370B383AEE323DF1943275C2EE865B6B8A2970089BB25BDCC17337E0939246220218344CB80DDCD437E99711076E7876C1424DA42365130E397391F651EF051E6ECEE8A7747885F585B18048E0729FA9498E2EEA6C0B90B31F683697143FE8E215A90FFA107AFA1B3E2A0590EEB047362E76C72B4F0ED878C1D0AE8913FA40BA62201A39A78D26C57C04D017777DB2D6965403EF9916C09D84481218FC884803D2700E406784CFCE1218D731C2763756C17DF2E1E0F84009C476B8A0A1329A54D6B78F75EB4306AF0EDE59AE84B7697C7F0DD3524A361976A0542A5AE144C551845C04894AEA378BF4AF69C7FFB644BF9B74FF68C7FA2A3CA0EDA238D3FC6C983C21026109F5080E88DEC55EB7608A629FE406752FA95AC1C71666A80EC29E5A7D2282707B249F2BAC179C6F7D981C31F36BB0D3EA00EF6975CAE33F97D378F5F057FE32CDA26459C4892D20F39CEC73ED56FBFE233FDA1D21FAD5607A2B6F8C7C26524AA544B02897A270F8441317657D1235CE7590A5B813D780111312CDA0BD1C94D2E4B6253E27C4F718E02CE2D26798A390364E2AC88A36C457510AE2075D01A1FCEA04A734D9582A627379B73973D1F6FD0AD7EEA892AC703D5111AE7AA71EB9D22E9608282498884A439F166C850831C99439C019E83336E8CBC56ED36C6421E1EFCAB050F584B7E77C9EFD805F90B1994E8DD8C07C1B90882AC03126D48F4E383F18741C06361C0217D1EF6C2408F4A9671493116DEAB3B2C8D747F2A355D094EAABDF056E3B86A40E5D90312979E302EF20C49A45D40573117FDFAE9D40935535A915A59B6CE01D575F80D1FA12BBC956AB6FB5AD508DE5F1B323C302AFD419BB9F07915BDEFD08D57ABF9C7A8FE0B288579F0B359C05126A0916B436F8DE0168A159275435079A17905BFD73EBAE3A5487A173262529458F26515315F7F023C44CB215A0ED1F2AB474BFB7039C4CB215E1E152FBF46B8BCD4C265F0AD948AF02AFA7AA5223AB9E5FA7E79E0F06FAD5474247FED4A459D3597D34A2D4788E24A1345F01D278E5A74D3825BC822E885ACCEC1AA3C57D2F931694FF86C7D6C1962CFE4E2B1EEA0DB732BDB9057C7F015867013CDE748B23AB224AE1D13837634A068D1D6468B1AC9D6A08EDFB33A5AA534C8979EDCAE04F08A22EBF16B7C575FB6F01D4A15A0B40DB6E952B38E3CF620B12914405535E9A0F926955DDDC724CA0D1DBF7C81CEC4A117065D82B74C74813CE8AEB33C8D3074AEAE1C4F8C4098EAB97C2767823C176FD2A4C051BAF7A0BA1F2371EA418A4E52E145CDD4B19E66B75297FE5D298B84AB974E883F54F54731CED6D188F2F902CE9357780EAFA72E4107E7F76DC6E2C1FACF69FDEA1E87A507904742C4A1902E37D0EB053CCFBC1DF3FC49966596D53354E56A064FF35DA459484BB39E65C1DC2CC23ECB2AD9A6F06B53F6ED5A4FBF5695B1AFF4B66EE134AB9E6AC9D354EFD4740F3C636DF788CAEEB36EE2D4ABB54A48F78F30B980995CF2F094458D59C10DB52AC3AEDE79EDCE98BD9C9ABCBCC2DCE565521791B954F592FEBCC566DD62B76AF1CAA907FF38F8C7AFEE1F43E91F839376525E8F6BECDB30E9DD2FE9DD2EB1D9124133C94FBA2732EE238D9A02214CBE2160F4DF0D9FE071276E74379B9B68572E8F44EA6839E875E7A0485D7435C4760CDEA0C553AAA8439195572D92F39ACCCF4B59A9255AABD1AA8CA29F1FD2CD8E8ADE3F52EED4540E2C0C6CA9332F984F17AE81B6D35BF78D2887493B4CA74C5C6095B8946CD152178BCC45E96899BB543D4ECC5E6C92177D9293D2175175653CB8BF5DAC4BDD22CA97DFE477B8DF492DA4A6A2276BEADBD652179C7018AAC17932410BEF59906BB0FF52B2DF5401300981C6DF3E29D81A5E2B45A02B651299A3923256D9B39618B79D1F23526C019B89BCC153FCAF92F81C9DD12C2577C373715778E3C1B5BDA46B3B591DCDFC2E558F7A090B9134465389350FD62AA5F3E8F595D4EB59A4A7EA834EBF159D76DDFA06F625094DD14E0DA5CA42509ECB924072B40B681C07EFFB87C17B398AD79B26047EA971F485E8347A785657EAD68D5C7937D95C2F31AC3D4B56230B930A504960AC99543D7D60ABE0B1E378E54A585B27D035A2C2B6E0C0578D5A6A450BFED152BC3CA9ADFEA5A0187E4DB86A5CB2542FE26DBC2703431AD7DC1AF7545E9B5E519FC9CABCF9A5844A4BFD52376A0ADB101175C0507860280A4CD0D3ACBE56A1B44B49B40265D7A973431E51DB9A6D9C84769B15184F3BA9EC96A7150413CA0E9E72C4B8B6DDEAAB6722B462A625FA6117FA56D8D7906FC1DD0EF5F2A70A523EE53F0057CB1953CBE4A1E922CC2AC95F626E2A66F576B2A88618E0A8BE28709AE9D43E07B09EA6B8C8349FA87F104085912EA2720D734115DDAA5BD3EF4534DE7EE6706429456FC0CF9FFFF87DE42AFF30FA0F11F1E13EFFE04F555709B565963CCBCEFF6D933586D91D4E72789B1103D3C650108050BDD3C7C8EAEB387640EA5AEB211E02CAE6E677321AB071E6010B34931DF6A7945D90F116D860C584D0838D8021BF810801EBE96AF591B8E896927905B56650EF3B81E2A4D8E0585486593A493208602D95CA5F43CD7B032B9928DEBE57261AAC2E936E895C3289505B0436126146DB2B13015549853FE0CCBCA65CD41A841330094C21C48E0E6045840D0935025AD46A3EA1614D8132A9551DA85BADEA844A77917E2C08A5D80F20DB5CA828DDEC762DE68698B9218D294A9CBED6772BE26DB1C763FAF98790BA72862E7D4E32B38872CE21976DB64F1CA58DCC96ADB84C2B0A3A2CDA427B96BAEED088D8CF7A0AD5CBFA2532B25E1D43FF8A4FC78955564B3284A00B085125CC1EAFBD563CBF563402FD1E5B2D31F5FB6C1D5A72B7DE80F1A32CEBC15D5C4AF4342A803D09C710D082BE8E3C71288544BF05FB34C1BB295DFA40B60002DD9A4B97487D9ACB61A4F7ED993687E2F527D0332F7D43AA7F660E555A0D7FB08AD66901D5F764FC1F50F043A86654C01E55A6959D98222B4C511D535462DAFCA08D6CB7FCA48D11BCF6511B7D62BE0B29BDC615F927561A9435DD4167F108C5420F74393DBE4EEC737B02AA727CEC814532C3E17AD3193EBE39F2D82900E814BE8DE86B822FC5DE382009FEFDF5D3E74F23175D23BA9620F0D0354FEDF87EE09826DDE79B155B93EF9388AD9C7EF9FCC73F7FC2FFFEAF89DDD830F4180E5FF97A99AF7CB5737AF8A0D7F041AFE3B56678B5FABCAF56BF15FBFCB6DEA23E85EBC3892B23FB86D355DFECE9AAE3C43C1CA61A76675FE830D5531573383B75CCD9292D580E671E86330F673EF3A0EAD7A90700348FF0A42DFF316C2ECF00F83F177F2CD7', 'BLOB_COMPRESSED', NULL, NULL, NULL, NULL, 0, NULL, 2287, NULL, NULL, NULL, NULL);

Hi Mickael,



Can you send us a line with INSERT SQL of your table?


In Dbeaver for example


Best regards,

Mathias

Hello, Stéphanie,


Yes, I can confirm that we republish with an external Oracle editor.

We would like to be able to query the Semarchy log Oracle database to extract data from the STB_LOG_ACTION_PROP_ACP table from the ACP_EXE_VAR, ACP_EXE_CLO, ACP_EXE_BLO columns. The problem is that we can't decompress the ACP_EXE_BLO field while extracting the other fields.

Thanks for your help 

Hi Denis, can you confirm if you reproduce this issue using a query tool outside of xDI, like DBeaver or SQL developer? This seems to be a pure oracle issue that we could investigate on the internet to help you.

Please confirm your oracle version as well as there seems to be a logged bug for this function on older oracle versions : https://support.oracle.com/knowledge/Oracle%20Database%20Products/1544982_1.html 


Best regards,

Stéphanie.

Login to post a comment