How to replace a string in a CLOB file in SQL

We can replace the key with respective value in a CLOB in sql with replace function.

test_clob     CLOB;      // Type of test_clob is CLOB


test_clob := replace(test_clob, 'http', 'https');

This command will replace all occurrence of http with https from test_clob and store that in same file.


Note:  Don't change this in buffer while writing data to CLOB. With this it will replace but after last occurrence of the key it will not append data to the file.

test_lob.writeappend(test_clob, length(buffer), REPLACE(buffer,'http','https'));

Oracle wallet for connecting to a SSL(HTTPS) enabled application by SQL/PL-SQL query

If we want to connect to an application having SSL configuration (running over https) then it will throw error of certificate.

To solve this issue we need to create a wallet in DB and use that wallet in code.

Steps to create wallet:

1)   orapki wallet create -wallet ${ORACLE_HOME}/<wallet_name> -pwd <password>
      For e.g. orapki wallet create -wallet ${ORACLE_HOME}/https_wallet -pwd ****

2)    orapki wallet add -wallet https_wallet -cert <certificate.cer> -trusted_cert -pwd ******

     After adding certificate you can verify the certificate is present in wallet. You are done with server side changes of wallet.

In client side code you need to set the wallet before connecting to application.

     utl_http.set_wallet( 'file:/oravl01/oracle/12.1.0.1/owm/https_wallet' , '****' );

  After this you can connect to the application

req  := utl_http.begin_request( 'https://<user>:<pass>@<host>:<port>/test' );
resp := utl_http.get_response(req);

How to get all table names in a schema in Oracle database

Metadata of tables are maintained in USER_TABLES table.


We can get all table names in a Oracle DB by :

 SELECT TABLE_NAME FROM USER_TABLES;

To get the number of rows present in a table :

select NUM_ROWS from user_tables WHERE TABLE_NAME='<TABLE_NAME>'

We can get all information related to tables in a schema by

select * from user_tables

To get a table information : 

desc <table_name>

Name        Null?              Type         
-------      --------              -------------
TITLE   NOT NULL VARCHAR2(80) 
VALUE                      VARCHAR2(80) 
PARRENT                 VARCHAR2(80) 
URL                          VARCHAR2(400)
NAME                      VARCHAR2(80) 
ICON                        VARCHAR2(80) 
STATUS                   VARCHAR2(80) 
ID      NOT NULL    NUMBER(38) 

JSON parsing with conditional filter with jq + shell

JSON parsing with conditional filter

For e.g. We have a json as below. We want to get the "format" field value when name is "Test".

{
  "name": "maven-snapshots",
  "format": "maven2",
  "type": "proxy",
  "url": "http://<host>:<port>/repository/maven-snapshots"
}
{
  "name": "Test",
  "format": "yum",
  "type": "hosted",
  "url": "http://<host>:<port>/repository/Test"
}
{
  "name": "Test_Group",
  "format": "maven2",
  "type": "group",
  "url": "http://<host>:<port>/repository/Test_Group"
}


We can get  it by:      jq -r '.[] | select(.name=="Test") | .format' 
 This will filter the "name" field and if it matches then return the "format" field value for that array element.

We can parse this from URL by below command.

curl -s -u admin:admin123 -X GET 'http://<host>:<port>/service/rest/v1/repositories' | jq -r '.[] | select(.name=="Test")|.format'

To get the number of elements in JSON array

 jq length 

To get the type of object 

jq type 

To get values with comma separated in same line 

jq  -r '.[] | [.date, .count, .word] | @csv'

"2011-01-12 13:14",17,"This"
"2011-01-13 21:30",4711,"That"