RESTful Web Services from Oracle, HTTP Methods GET and POST

I share example how to consuming RESTful Web Services from Oracle, methods GET and POST with PL/SQL Anonymous Block, for this example i use http://jsonplaceholder.typicode.com/ json.

1. GET

DECLARE 
    req     utl_http.req; 
    res     utl_http.resp; 
    urlrest VARCHAR2(100) := 'http://jsonplaceholder.typicode.com/posts/1'; 
    name    VARCHAR2(4000); 
    buffer  VARCHAR2(4000); 
BEGIN 
    req := utl_http.Begin_request(urlrest, 'GET', ' HTTP/1.1'); 
    utl_http.Set_header(req, 'user-agent', 'mozilla/4.0'); 
    utl_http.Set_header(req, 'content-type', 'application/json'); 
    res := utl_http.Get_response(req); 

    LOOP 
        utl_http.Read_line(res, buffer); 
        dbms_output.Put_line(buffer); 
    END LOOP; 

    utl_http.End_response(res); 
EXCEPTION 
    WHEN utl_http.end_of_body THEN 
      utl_http.End_response(res); 
END; 

Test:

2. POST

DECLARE 
    req     utl_http.req; 
    res     utl_http.resp; 
    urlrest VARCHAR2(4000) := 'http://jsonplaceholder.typicode.com/posts'; 
    name    VARCHAR2(4000); 
    buffer  VARCHAR2(4000); 
    json    VARCHAR2(4000) := '{"title":"foo","body":"bar","userId":1}'; 
BEGIN 
    req := utl_http.Begin_request(urlrest, 'POST', ' HTTP/1.1'); 
    utl_http.Set_header(req, 'user-agent', 'mozilla/4.0'); 
    utl_http.Set_header(req, 'content-type', 'application/json'); 
    utl_http.Set_header(req, 'Content-Length', Length(json)); 
    utl_http.Write_text(req, json); 
    res := utl_http.Get_response(req); 

    BEGIN 
        LOOP 
            utl_http.Read_line(res, buffer); 

            dbms_output.Put_line(buffer); 
        END LOOP; 

        utl_http.End_response(res); 
    EXCEPTION 
        WHEN utl_http.end_of_body THEN 
          utl_http.End_response(res); 
    END; 
END; 

Test:

Kotlin - Multi-Threading in parallel and wait until all threads finish.

I share example how to execute Multi-Threading in parallel on Kotlin, is very useful if you want send a lot process simultaneously, In java you can use parallel stream and exist a lot examples, but it's almost the same.

I use an ExecutorService to manage pools of threads.

import java.util.concurrent.Executors
import java.util.concurrent.TimeUnit

fun main(args: Array<String>) {
    println("Started.....!")

    var listStr = arrayOf("test1", "test2", "test3",
            "test4", "test5", "test6", "test7", "test8", "test9", "test10")

    var es = Executors.newCachedThreadPool()

    for (item in listStr) {
        es.execute {
            println("item -> $item")
        }
    }

    es.shutdown()
    var finished = es.awaitTermination(1, TimeUnit.MINUTES)
    println("\\nFinished all threads $finished")

}

Run test: check test 3 execute after test 4 and test 2 execute after test 3. 

References:

https://winterbe.com/posts/2015/04/07/java8-concurrency-tutorial-thread-executor-examples/

https://howtodoinjava.com/java/multi-threading/executorservice-invokeall/

https://crunchify.com/how-to-run-multiple-threads-concurrently-in-java-executorservice-approach/

https://stackoverflow.com/questions/7939257/wait-until-all-threads-finish-their-work-in-java

Kotlin - Spring Data JPA calling Oracle Function

There are two way to call function in Spring Data JPA  with kotlin, I shares examples.

Create function in oracle.

CREATE OR replace FUNCTION Myfunction(value IN VARCHAR2) 
RETURN NUMBER 
IS 
  a NUMBER; 
  b NUMBER; 
BEGIN 
    RETURN a + b; 
END; 

Example 1, it won't work if your function is using DML statementes, but you can add annotation @Modifying annotation. 

import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.CrudRepository
import org.springframework.data.repository.query.Param

interface MyRepository: CrudRepository<YOUR_MODEL, YOUR_ID_MODEL>{
    @Query(nativeQuery = true, value = "SELECT myfunction(:value) FROM dual")
    fun myfunction(@Param("value") value: String?): Int?
}

Example 2: Create customer repository.

2.1 create custom repository

interface MyRepositoryCustom {

    fun myFunction(param1: String?): Int?
}

2.2 create a class, i use  SimpleJdbcCall.

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource
import org.springframework.jdbc.core.namedparam.SqlParameterSource
import org.springframework.jdbc.core.simple.SimpleJdbcCall


class MyRespositoryImpl : MyRepositoryCustom {

    @Autowired
    lateinit var jdbcTemplate: JdbcTemplate

    override fun myFunction(param1: String?): Int? {
        val jdbcCall = SimpleJdbcCall(jdbcTemplate).withFunctionName("myfunction")

        val paramMap: SqlParameterSource = MapSqlParameterSource()
                .addValue("value", param1)

        return jdbcCall.executeFunction(Int::class.java, paramMap)
    }
}

2.3 I extend customer repository on principal repository.

interface MyRepository: CrudRepository<YOUR_MODEL, YOUR_ID_MODEL>, MyRepositoryCustom {

That it is.

References:

https://stackoverflow.com/questions/45867348/spring-data-jpa-calling-oracle-function