Hints and tricks for Oracle and other stuff...

 If you are useing urifactory.geturi function to call URL with https it would not work.

Use:

 httpuritype('https....').getclob()

Of course before that ypu shoul set wallet location:  UTL_HTTP.set_wallet('file:/u01/...');

# posted by Domen Dolar @ 1:52 PM 0 comments

 In JAVA parameters you should use:

java -jar
-Djavax.net.ssl.trustStore=...path_to.../jre/lib/security/cacerts
-Djavax.net.ssl.trustStorePassword=...pwd.of.cacerts...
-Djavax.net.ssl.trustStoreType=jks
-Djavax.net.debug=ssl:handshake:verbose
-Djdk.tls.client.protocols=TLSv1
-Dhttps.protocols=TLSv1  YOUR_PROGRAM.jar

To populate your cacerts with your certificates use:

keytool.exe -importcert -keystore cacerts -storepass ...pwd.of.cacerts... -file my_ca_cert.crt -alias "my_ca_cert"

# posted by Domen Dolar @ 8:16 AM 0 comments

 To convert custom log timestamp into @timestamp you should do next:

variable timestamp has value like and you prepare it or read it with grok.

21-12-2022 14:33:12,001

And this value is transformed into cy, cm, .... and on the end created as date into variable timestampd. Be careful about milliseconds and millisecond delimiter. If you have . you should put . into formater.

# posted by Domen Dolar @ 8:06 AM 0 comments

You can create various transformation with SQL. You have to prepare XSLT transformation and use it. 

SQL

# posted by Domen Dolar @ 11:11 PM 0 comments

Executing with ADMIN user

Create ACL and add pages to list:

# posted by Domen Dolar @ 10:14 PM 0 comments

In oracle autonomous database you have some tricks how to read HTTPS pages from database.

First is that you do not have to create wallet with public keys of pages you will call from database.  All mayor trusted issuers have database build in. You use code: utl_http.set_wallet( '' );

 If you would like to acces untrusted sites (database do not know issuers) or HTTP sites you have to use one of providers-proxies that converts trafic to trusted HTTP site.

Do not forget to register URLs in ACL

Some samples:

# posted by Domen Dolar @ 10:05 PM 0 comments

In this sample you can see how easy is to parse data in JSON structure with SQL.

Sample SQL:

# posted by Domen Dolar @ 9:50 PM 0 comments

 In this sample you can see how to create many files from one template file in windows batch script.

Templeta file:

teplate.txt

# posted by Domen Dolar @ 9:30 PM 0 comments

 Here is sample how to parse XML in Phyton. XML has namespaces.

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

import xml.etree.ElementTree as ET

xml = '<?xml version="1.0" encoding="UTF-8"?><application xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/application_6.xsd" version="6">  <display-name>ORDS-EAR</display-name>  <module>    <web>      <web-uri>ORDS-WAS-19.1.0.092.1545.war</web-uri>      <context-root>/ords</context-root>    </web>  </module></application>';

tree = ET.ElementTree(ET.fromstring(xml))

#tree = ET.ElementTree(ET.from('application.xml'))

root = tree.getroot()

ns = {'xsi': 'http://www.w3.org/2001/XMLSchema-instance',

      'javaee': 'http://java.sun.com/xml/ns/javaee',

      'schemaLocation': 'http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/application_6.xsd'

      }

war_name = '';

for modvar in root.findall('javaee:module', ns):

    for webvar in modvar.findall('javaee:web', ns):

        urivar = webvar.find('javaee:web-uri', ns);

        war_name = urivar.text;

print (war_name);

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

# posted by Domen Dolar @ 10:55 AM 0 comments

 If you have in XML LIST more than 300.000 items you will get error "ORA-31186: Document contains too many nodes"

select
      extractvalue(value(x), '//LIST/ITEM') ITEM,
from  table_with_xml t
   ,  table(xmlsequence(extract(t.xml_col,'//LIST'))) x  

If you have like 450.000 items, you can split XML like ... in two parts and combine them with union all

select
      extractvalue(value(x), '//LIST/ITEM') ITEM,
from  (select 
          substr(xml_col, 1 , instr(xml_col, '</ITEM>',1 , 
          250000  -- first 250.000 items
          ))||'/ITEM></LIST>' xml_col
       from table_with_xml
       ) t
   ,  table(xmlsequence(extract(t.xml_col,'//LIST'))) x  
union all
select
      extractvalue(value(x), '//LIST/ITEM') ITEM,
from  (select
          '<LIST><ITEM>'||  
          substr(xml_col, instr(xml_col, '</ITEM>',1 ,
          200000  -- last 200.000 items
          )+length('</ITEM>') ) xml_col
       from table_with_xml
       ) t
   ,  table(xmlsequence(extract(t.xml_col,'//LIST'))) x
;

# posted by Domen Dolar @ 9:35 PM 0 comments

 You have often to count number of occurrences of one string X in text.

You can use only two methods to do that.

Here is example for PL/SQL, you can use this trick in all languages just use right length and replace function. X is 'T' and the text is 'TEXT'

count  := (length('TEXT')-length( replace('TEXT','T','' ))) / length('T')

result is 2

# posted by Domen Dolar @ 9:06 PM 0 comments

• \[(?<timestamp>(.*))\]\s - Parses timestamp in [] with space at the end and content is parsed into element timestamp
• (\[(?<loglevel>(.*))\]\s+) - Parses level in [] with space at the end and content is parsed into element loglevel
• ...

# posted by Domen Dolar @ 9:48 AM 0 comments

Sample how to decode X509 in PL/SQL.

-- JAVA PROCEDURE FOR DECODE X509 CERTIFICATE. SAMPLE FOR ISSUERDN
-- MORE on X509Certificate https://docs.oracle.com/javase/8/docs/api/java/security/cert/X509Certificate.html
create or replace and compile java source named testx509src as
import java.security.cert.*;
  import java.io.*;
  import java.sql.*;
  import java.security.Principal;
  import oracle.sql.CHAR;

  public class TestX509 {
      public static oracle.sql.CHAR getIssuer(CHAR cert)
              throws SQLException, IOException, CertificateException {

          String aa = new String(cert.getString());

          CertificateFactory cf = CertificateFactory.getInstance("X.509");
          ByteArrayInputStream bytes = new ByteArrayInputStream(aa.getBytes());
          X509Certificate c = (X509Certificate) cf.generateCertificate(bytes);

          Principal principal = c.getIssuerDN();
          String issuerDn = principal.getName();
          
          oracle.sql.CHAR AAA = new oracle.sql.CHAR(issuerDn, null );
          return AAA;

      }
  }
/  

--PL/SQL INTERFACE FOR JAVA CALL
CREATE OR REPLACE FUNCTION CERT_getIssuer(cert in varchar2)
  RETURN VARCHAR2
  AS LANGUAGE JAVA
  NAME 'TestX509.getIssuer(oracle.sql.CHAR) return oracle.sql.CHAR';
/
--TEST SCRIPT
declare
  x509certificate varchar2(4000);
begin
x509certificate := '-----BEGIN CERTIFICATE-----
MIIBfzCCASmgAwIBAgIQWFSKzCWO2ptOAc2F3MKZSzANBgkqhkiG9w0BAQQFADAa
MRgwFgYDVQQDEw9Sb290Q2VydGlmaWNhdGUwHhcNMDExMDE5MTMwNzQxWhcNMzkx
MjMxMjM1OTU5WjAaMRgwFgYDVQQDEw9Vc2VyQ2VydGlmaWNhdGUwXDANBgkqhkiG
9w0BAQEFAANLADBIAkEA24gypa2YFGZHKznEWWbqIWNVXCM35W7RwJwhGpNsuBCj
NT6KEo66F+OOMgZmb0KrEZHBJASJ3n4Cqbt4aHm/2wIDAQABo0swSTBHBgNVHQEE
QDA+gBBch+eYzOPgVRbMq5vGpVWooRgwFjEUMBIGA1UEAxMLUm9vdCBBZ2VuY3mC
EMlg/HS1KKqSRcg8a30Za7EwDQYJKoZIhvcNAQEEBQADQQCYBIHBqQQJePi5Hzfo
CxeUaYlXmvbxVNkxM65Pplsj3h4ntfZaynmlhahH3YsnnA8wk6xPt04LjSId12RB
PeuO
-----END CERTIFICATE-----';
dbms_output.put_line( cert_getissuer(x509certificate) );
end;
/

# posted by Domen Dolar @ 10:37 PM 0 comments