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;

/

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..

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 ;

--

Return statement in PL/SQL Procedure.

Question: Can we use RETURN Statement in PL/SQL Procedure.?
Ans: Yes, we can !! ;)

Honestly, as far as I remember, I've never used return statements in my PL/SQL Procedures.
How about you ??

Of coarse, in PL/SQL functions RETURN statement is a mandatory thing. But what about procedures ??

Lets see how and where we can use of return statement in a procedure.
- You have procedure, that contains few lines of code.
- After validating certain condition, you don't need to proceed further. You want control to be returned to calling procedure/main program.

** Couple of approaches here **

One way of doing it is, handling USER defined EXCEPTIONs.
- you can check how to use  USER defined Exceptions. I am not covering it here. ;)
I used this approach many times until now. Big advantage of this way is, you can trap the ERROR code in your calling program.

Another way is having RETURN statement in your sub-routine/child program.
For example:
== sub-routine ==
procedure CHILD_PROC
-- declarations
begin
-- pl/sql statements
if CONDITION = TRUE then
RETURN ;
else
-- do something
end if ;
end CHILD_PROC ;

=== calling program ==
procedure MAIN_PROC
-- declarations
begin
-- pl/sql statements
-- call to child proc
CHILD_PROC();
-- few more statements
end MAIN_PROC ;

Here is some sample code.

-- child procedure.

create or replace procedure child_proc(p_boolean BOOLEAN) as
begin
dbms_output.put_line('in child_proc before return call');
if p_boolean then
return ;
end if ;
dbms_output.put_line('in child_proc after return');
end;
/

-- main procedure

create or replace procedure main_proc(p_boolean boolean) as
begin
dbms_output.put_line('before child_proc call');
child_proc(p_boolean) ;
dbms_output.put_line('after child_proc call');
end;
/

-- execution and  result check --

[1]

begin main_proc(TRUE); end ;

--output --

before child_proc call

in child_proc before return call

after child_proc call

Statement processed.

0.00 seconds

[2]

begin main_proc(FALSE); end ;

--output --

before child_proc call

in child_proc before return call

in child_proc after return

after child_proc call

Statement processed.

0.00 seconds

Distinct your data in PL/SQL tables

Question: How do you eliminate duplicate values from a PL/SQL table.

Answer: It is easy to select unique values from database table, with the help of SELECT DISTINCT query.
But if you have some data in PL/SQL table, then it is very difficult to eliminate duplicate records.

But Oracle 10g made it easy to filter out duplicate data from PL/SQL table or Nested table as well.
This is possible in 10g with a concept called - MULTISET 

See following example..
-------
declare
type nested_typ is table of varchar2(200);
v_test_type nested_typ := nested_typ();
v_temp nested_typ ;
begin
v_test_type.extend(8);
-- read values into array.
v_test_type(1) := 'NEW YORK';
v_test_type(2) := 'SYDNEY';
v_test_type(3) := 'SINGAPORE';
v_test_type(4) := 'PERTH';
v_test_type(5) := 'NEW YORK';
v_test_type(6) := 'NEW YORK';
v_test_type(7) := 'DELHI';
v_test_type(8) := 'PERTH';

dbms_output.put_line('Cities before distinct..'||chr(10)||'------------------------');
-- display values before distinct..
for i in v_test_type.first..v_test_type.last
loop
dbms_output.put_line(v_test_type(i));
end loop ;
-- assign all values to v_temp, which are in v_test_type
v_temp := v_test_type ;
-- take ditinct values out of v_temp, v_test_type into v_test_type
v_test_type := v_test_type MULTISET UNION DISTINCT v_temp ;
dbms_output.put_line(chr(10)||'...'||chr(10)||'Cities after distinct..'||char(10)||'-----------------------');
-- display values after distinct..
for i in v_test_type.first..v_test_type.last
loop
dbms_output.put_line(v_test_type(i));
end loop ;
end ;
/
out put
========
Cities before distinct..


------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
...
Cities after distinct..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI

PL/SQL procedure successfully completed.
===
In the same way we can use other SET operations like UNION, UNION ALL, MINUS, INTERSECT on pl/sql tables.

Here is another way of doing it..


DECLARE
            presidents_t person_names_t ;
            presidents_t1 person_names_t ;
            cursor cur_user is select user_name from fnd_user where rownum < 5;
            cursor cur_user1 is select user_name from fnd_user where rownum < 10;

BEGIN
    open cur_user ;
    fetch cur_user bulk collect into presidents_t ;
    close cur_user ;
  
     open cur_user1 ;
    fetch cur_user1 bulk collect into presidents_t1 ;
    close cur_user1 ;

          
    FOR rec IN (SELECT column_value
                  FROM TABLE (CAST (presidents_t AS person_names_t))
                          union
                        SELECT column_value
                  FROM TABLE (CAST (presidents_t1 AS person_names_t))
                 )
        LOOP
            DBMS_OUTPUT.PUT_LINE(rec.column_value);
        END LOOP;
END;
/

Sort your nested table data.

Question: How do I sort data which is in a nested table.?

Answer: You might've enjoyed the power of set operations(MINUS,INTERSECT,UNION) in SQL many times.
For example - To combine to sets of data, to eliminate duplicated data among two sets etc.

How about using similar set operations in PL/SQL or Nested tables. ?
With Oracle 10g we can have Multiset operations on Nested tables.

Have you ever come accross a situation, where you need to sort data which is in a nested table.
Below is the sample code to sort nested table and then display it.
---

declare
-- I used one nested table type, which is already available(seeded) in APPS schema.
-- If required, create one table type as you reuired.

v_arr VARCHAR2_TABLE_200 := VARCHAR2_TABLE_200();

begin

v_arr:= VARCHAR2_TABLE_200 ('NEW YORK','SYDNEY', 'SINGAPORE','PERTH', 'NEW DELHI','',NULL,'LONDON');

            -- just print and see, how data is stored in nested table.
                dbms_output.put_line(chr(10)'Cities before sorting..'chr(10)'-----------------------');

for i in v_arr.first..v_arr.last
loop
         dbms_output.put_line(nvl(v_arr(i),'NULL'));

end loop ;

-- watch out NVL function above... wanted to show 'NULL' word, where ever there is NULL.
select cast(multiset(
select * from table(v_arr)
order by 1 asc NULLS FIRST) as VARCHAR2_TABLE_200)
into v_arr from dual;

-- NULLS FIRST in above statement is to display nulls first and then data. Default is nulls displayed last.
-- see if data is sorted or not...

dbms_output.put_line(chr(10)'Cities after sorting..'chr(10)'-----------------------');

for i in v_arr.first..v_arr.last
loop
          dbms_output.put_line(nvl(v_arr(i),'NULL'));
end loop ;
end ;
/

---- here's the output. -------

Cities before sorting..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW DELHI
NULL
NULL
LONDON

Cities after sorting..
-----------------------
NULL
NULL
LONDON
NEW DELHI
NEW YORK
PERTH
SINGAPORE
SYDNEY

PL/SQL procedure successfully completed.
..