How to export data dictionary with Oracle SQL Developer

Oracle SQL Developer is a free development tool for Oracle database. It has a nice feature of generating data dictionary and exporting it to HTML.

To generate data dictionary create connection to your database, then select connection, right click and choose Generate DB Doc....

Then choose output directory and optionally take time to select database object types.

Click OK and wait for export to complete. This is how it looks:

Unix/AIX box : count word occurrences in text file

Command:

An approach is to transform the content of the input file with tr command so that all words are in a single line and then use grep -c to count that match count.

$ tr '[:space:]' '[\n*]' < example.txt | grep -i -c 'EMPLOYEE_NUMBER'

12c SOA - Business Rules - use of generic rule

Input: id, name, designation (like PAT, A, SA, M etc.).

Output: eligible_trutime_topup_per_fortnight (like 10,20,0 etc.)

Step1: Create SOA Project.

Now project created.

Step2: Drag and drop bpel process and select synchronous process.

Now bpel created as like below.

Step3: Prepare schema as per your requirement.

Step4: Drag and drop business rules component.

Now you can import schema input and output.

Import input schema.

Input schema imported successfully, now import output schema.

Both schema’s imported successfully.

Step5: Double click business rules.

Now create rule.

Add the condition and hard code the output value.

Add then condition.

Select target processResponce.

Hard code output value.

Add remaining conditions also.

Step6: Double click bpel process and add Business Rules.

Double click rules and and select Business rules.

After selection now showing like below image.

Click ok, Now composite automatically invoke businessrules and created activity’s.

Step7:  Now composite like below and deploy the composite and test.

Testing:

Input:

<?xml version="1.0" encoding="UTF-8"?><inputVariable>

   <part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="payload">

      <ns1:process xmlns:ns1="http://xmlns.oracle.com/POC/BussinessRulls_Poc/TopupBPEL">

            <ns1:ID>12345</ns1:ID>

            <ns1:NAME>Suri</ns1:NAME>

            <ns1:DESIGNATION>A</ns1:DESIGNATION>

        </ns1:process>

   </part>

</inputVariable>

Output:

<?xml version="1.0" encoding="UTF-8"?><outputVariable>

   <part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="payload">

      <processResponse xmlns="http://xmlns.oracle.com/POC/BussinessRulls_Poc/TopupBPEL">

         <Eligibletopup>15</Eligibletopup>

      </processResponse>

   </part>

</outputVariable>

Unix/AIX box - Certificate expiration date check

Run the following openssl command 

echo | openssl s_client -servername NAME -connect HOST:443 2>/dev/null | openssl x509 -noout –dates

Example: If you have a server name ="test.server.com" and port="443"

echo | openssl s_client -servername test.server.com -connect test.server.com:443 2>/dev/null | openssl x509 -noout -dates

12c SOA - How to check BPEL recovery Pool from SOAInfra DB

Sometimes EM Composite BPEL instances have failures and thus they are moved to BPEL recovery pool. We can check  them from SOAINFRA DB and delete the struck instances if not needed.

Query 1: based on date

select count(*) from dlv_message where dlv_type = 1 and state = 0 and receive_date like '%08-JUL-19%'

Query2: based on a period

select count(*) from dlv_message where dlv_type = 1 and state = 0 and receive_date 

between TO_DATE('2020-01-01 00:00:01', 'YYYY-MM-DD HH24:Mi:SS') and TO_DATE('2020-05-31 23:40:59', 'YYYY-MM-DD HH24:Mi:SS')

To delete:

delete from dlv_message where dlv_type = 1 and state = 0 and receive_date like '%08-JUL-19%'

12c SOA - Business Rules - use of decision table

Here I will show you how to create and use decision table using business rule.

Input: id, name, designation (like PAT, A, SA, M etc.).

Output: eligible_trutime_topup_per_fortnight (like 10,20,0 etc.)

Step1: Create SOA Project.

Now project created.

Step2: Drag and drop bpel process and select synchronous process.

Now bpel created as like below.

Step3: Prepare schema as per your requirement.

Step4: Drag and drop business rules component.

Now you can import schema input and output.

Import input schema.

Input schema imported successfully, now import output schema.

Both schema’s imported successfully.

Step5: Double click business rules.

Create decision tables.

Now decision table like below.

Select process Designation like below.

Click Actions and hard code the top up value.

After hard coding the value diagram like below.

Create another rule and give remaining conditions like below.

After creating all condition rules diagram like below.

Step6: Double click bpel process and add BusinessRules.

Double click rules and and select Business rules.

After selection now showing like below image.

Click ok, now composite automatically invoke business rules and created activities.

Step7:  Deploy the composite and test.

 

Testing:

Input:

<?xml version="1.0" encoding="UTF-8"?><inputVariable>

   <part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="payload">

      <ns1:process xmlns:ns1="http://xmlns.oracle.com/POC/DecisionTable_Poc/BPELProcess1">

            <ns1:ID>12345</ns1:ID>

            <ns1:NAME>Suri</ns1:NAME>

            <ns1:DESIGNATION>A</ns1:DESIGNATION>

        </ns1:process>

   </part>

</inputVariable>

Output:

<?xml version="1.0" encoding="UTF-8"?><outputVariable>

   <part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="payload">

      <processResponse xmlns="http://xmlns.oracle.com/POC/DecisionTable_Poc/BPELProcess1">

         <EligibleTopup>15</EligibleTopup>

      </processResponse>

   </part>

</outputVariable>