PL/SQL REST API Call to receive JSON Payload

 declare

  req utl_http.req;

  res utl_http.resp;

  lv_url varchar2(4000) := 'http://<host>:<port>/<something>/';

  buffer varchar2(32767);

  lj_json_obj  json_object_t;

BEGIN

dbms_output.put_line(lv_url);

req := utl_http.begin_request(lv_url,'POST','HTTP/1.1');

                 utl_http.set_header(req,'Content-type','application/json');

                 utl_http.set_header(req, 'Accept', 'application/json');

                 res := utl_http.get_response(req);

  -- process the response from the HTTP call

  begin

      utl_http.read_text(res, buffer,1000000);

      lj_json_obj := json_object_t(buffer);

      utl_http.end_response(res);

  exception

    when utl_http.end_of_body then

      utl_http.end_response(res);

     end ;

end if;

end;

/

Python- Pandas

Read individual field values from a data frame

db_df = pd.read_sql("select brms_file_check_notify_pkg.decrypt(a.login_id) password_dr, 

a.* from file_check_notification a"                    " where sysdate > next_run and file_check_id = 3"                       , ebsconn)
ebsconn.close()
print('DB is queried for tab1..')
hostname = db_df.loc[0].at['host']

username = db_df.loc[0].at['login_id']
password = db_df.loc[0].at['password_dr']
location = db_df.loc[0].at['location']
subject  = db_df.loc[0].at['email_subject']
protocol = db_df.loc[0].at['protocol_type']

Numbers to Words Converter

Oracle Analytic Functions - Difference between ORDER BY and PARTITION BY

ORDER BY - brings up the running total/sum by certain repeating column
PARTITION BY - brings up the GROUP total by certain repeating column

Consider following simple table.

This projects example contains 2 projects - ABC and CAB
Each project contains 2 tasks and its cost respectively.

By using same Analytic functions with two different window, can get "running totals" and "group totals"

select project_id, project_name, task_no, cost
,sum(cost) over(order by project_id,task_no) running_total_by_project_task
,sum(cost) over(order by project_id) running_total_by_project
,sum(cost) over(partition by project_id) total_cost_by_project
,sum(cost) over(partition by task_no) total_cost_by_task
from temp_project_details;

Output below..

Getting Concurrent Request Id within Sql Loader based Concurrent Programs

None of the following will work in SQL LOADER.
- fnd_global.conc_request_id
- fnd_profile.value('conc_request_id')

First will update request_id as -1 and second will update as blank/NULL

You may consider this.. it works for me.

- Create a function, something like below...
===
CREATE OR REPLACE PACKAGE BODY

IS

-- define global variable..

   gn_sqlldr_req_id     NUMBER ;

FUNCTION get_request_id RETURN NUMBER
IS
ln_req_id NUMBER ;
BEGIN


IF gn_sqlldr_req_id IS NULL then

select max(request_id)
INTO gn_sqlldr_req_id
from fnd_concurrent_requests where  concurrent_program_id  in (
select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name =' YOUR_PROG_SHORT_NAME')   ;
END IF ;

RETURN gn_sqlldr_req_id ;

END ;

====
Your SQL Loader control file..
==
load data
INFILE '/tmp/XMITINVD.TXT'
INTO TABLE xxinv_TABLE_stg
APPEND
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(

---

---

---

request_id ".get_request_id" -- your function here

--

--

--

)

This worked for me. 

How to divide huge data into batches

Here is my answer on the subject above.

Using an Analytical function – NTILE, I was able to divide big volume of data into multiple batches.

After dividing data into multiple batches, it’s easy to implement multi-threading, without missing any record.

Following query divides Items(for the Org: 489 ) data in to 5 equal batches, and returns MIN and MAX Item ids from each batch.

 select min(inventory_item_id) min_item_id , max(inventory_item_id) max_item_id,

     count(*) batch_count, batch batch_num

    from ( select inventory_item_id, NTILE(5) over (order by inventory_item_id) batch

               from mtl_system_items_b

               where organization_id  = 679 -- 048

            )    group by batch

Query Output:

MIN_ITEM_ID	MAX_ITEM_ID	BATCH_COUNT	BATCH_NUM
158281	299596	7092	1
299598	400344	7092	2
509671	589229	7091	4
589230	961093	7091	5
400345	509659	7092	3

I used this batching concept to process my data into multi-threading. Sample code below.

Sample Code:

---

for rec in (select min(inventory_item_id) min_item_id , max(inventory_item_id) max_item_id,

count(*) batch_count, batch batch_num

from ( select inventory_item_id, ntile(5) over (order by inventory_item_id) batch

                      from mtl_system_items_b

                      where organization_id  = <489>)         group by batch

) loop

               print.log_message ('batch_num:'||rec.batch_num

                              ||':min_item_id:' ||rec.min_item_id

                              ||':max_item_id:'||rec.max_item_id

                              ||':batch_count:'||rec.batch_count);

               ln_request_id := 0;

begin

               -- submit programs in batchhes..

      ln_request_id :=

               fnd_request.submit_request (

                   application   => 'XXCUST',

                   program       => 'concp_short_name',

                   argument1     => xv_item_code,

                   argument2     => rec.min_item_id,

                   argument3     => rec.max_item_id,            

                   argument4     => xn_org_id          

                   );

              

commit;

end loop ;

--

BI Publisher Bursting - Delivering/sending emails conditionally

Here is the sample code to send emails conditionally from BI/XML Publisher bursting feature.

Problem Statement:

Generate payslips for all the employees at one shot and email the payslips to respective employee.

1. Alter EMP table and added a column called email to hold emails of each individual employees.

And then update email column to add email Ids where you want your emails to be delivered.

2. Create a Data template Sample code available here

3. Default package Sample code available here

4.Define a concurrent program and assign the concurrent program to a responsibility as per your convenience.

5. Design a simple RTF template and register the same. Sample template available here

6. Register data template, created in #1 above

7. Register control file for bursting. Sample code available here

Package Sample code here.

8. Submit the concurrent program defined in #4. If everything goes fine,
you should be able to see emails delivered at the email ids provided.

Sample Data in XML format.

9. Sample output below..