<plugin>
      <groupId>${jooq.groupId}</groupId>
      <artifactId>jooq-codegen-maven</artifactId>
      <version>${jooq.version}</version>

      <executions>
          <!-- Generate the required class from the database -->
          <execution>
              <id>generate-jooq</id>
              <phase>generate-sources</phase>
              <goals>
                  <goal>generate</goal>
              </goals>
          </execution>
      </executions>

      <configuration>
          <!-- сюда подставляются настройки соединения с бд -->
          <jdbc>
              <driver>${driver-class-name}</driver>
              <url>${url}</url>
              <user>${username}</user>
              <password>${password}</password>
          </jdbc>

          <generator>
              <database>                
                  <name>${jooq.generator.db.dialect}</name>
                  <includes>.*</includes>
                  <inputSchema>${jooq.schema}</inputSchema>
              </database>
              <!-- Generate classes for tables and records -->
              <generate>
                  <globalTableReferences>true</globalTableReferences>
                  <globalUDTReferences>false</globalUDTReferences>
                  <fluentSetters>true</fluentSetters>
                  <javaTimeTypes>true</javaTimeTypes>
              </generate>
              <!-- Configure the target package and directory -->
              <target>
                  <packageName>ru.sportmaster.wfm.jooq.db</packageName>
                  <directory>target/generated-sources/jooq</directory>
              </target>
          </generator>
      </configuration>
  </plugin>

/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class VDemoTable extends TableImpl<VDemoTableRecord> {
     
     .......................
     
    /**
     * The column <code>V_DEMO_TABLE.A</code>.
     */
    public final TableField<VDemoTableRecord, Byte> A = createField(DSL.name("A"), SQLDataType.TINYINT, this, "");

    /**
     * The column <code>V_DEMO_TABLE.B</code>.
     */
    public final TableField<VDemoTableRecord, String> B = createField(DSL.name("B"), SQLDataType.VARCHAR(5), this, "");

    ........................
}

/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetNumValue extends AbstractRoutine<BigDecimal> {
    
    /**
     * The parameter <code>GET_NUM_VALUE.RETURN_VALUE</code>.
     */
    public static final Parameter<BigDecimal> RETURN_VALUE = 
      Internal.createParameter("RETURN_VALUE", SQLDataType.NUMERIC, 
                               false, false);

    ..........
}

/**
 * Convenience access to all stored procedures and functions.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {

    /**
     * Call <code>GET_NUM_VALUE</code>
     */
    public static BigDecimal getNumValue(
          Configuration configuration
    ) {
        GetNumValue f = new GetNumValue();

        f.execute(configuration);
        return f.getReturnValue();
    }
    .....  
}

create or replace package demo_test_pkg is

-- коллекция целых чисел
type t_ints is table of integer;

-- рекорд
type t_rec is record
(
    a number(2),
    b varchar2(10 char)
);
-- курсор рекордов
type t_cur is ref cursor return t_rec;

-- коллекция рекордов
type t_tab is table of t_rec;

procedure get_min_max
(
    p_ints in t_ints,
    p_min out integer,
    p_max out integer
);

function get_sys_refcursor return sys_refcursor;

function get_refcursor return t_cur;

function get_collection return t_tab;

function get_collection_pipe return t_tab pipelined;

end demo_test_pkg;
/
grant execute on demo_test_pkg to app_user;

/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetMinMax extends AbstractRoutine<java.lang.Void> {

    /**
     * The parameter <code>DEMO_TEST_PKG.GET_MIN_MAX.P_MIN</code>.
     */
    public static final Parameter<BigInteger> P_MIN = ...

    /**
     * The parameter <code>DEMO_TEST_PKG.GET_MIN_MAX.P_MAX</code>.
     */
    public static final Parameter<BigInteger> P_MAX = ...

/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class TTabRecord extends ArrayRecordImpl<TRecRecord> { ....
  
/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class TRecRecord extends UDTRecordImpl<TRecRecord> implements Record2<Byte, String> {
    /**
     * Getter for <code>DEMO_TEST_PKG.T_REC.A</code>.
     */
    public Byte getA() {
        return (Byte) get(0);
    }
   
    /**
     * Getter for <code>DEMO_TEST_PKG.T_REC.B</code>.
     */
    public String getB() {
        return (String) get(1);
    }
    ......

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>techclub</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>techclub</name>
    <description>techclub</description>
    
    <properties>
        <java.version>11</java.version>
        <jooq.groupId>org.jooq.pro-java-11</jooq.groupId>
        <jooq.version>3.15.0</jooq.version>
        <jooq.generator.db.dialect>org.jooq.meta.oracle.OracleDatabase</jooq.generator.db.dialect>
        <jooq.schema>dev_user</jooq.schema>
        <!--db driver-->
        <ojdbc10.version>19.7.0.0</ojdbc10.version>
        <junit-jupiter>5.8.1</junit-jupiter>

    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>${junit-jupiter}</version>
            <scope>test</scope>
        </dependency>

        <!--db access-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc10</artifactId>
            <version>${ojdbc10.version}</version>
        </dependency>
        <!-- jooq -->
        <dependency>
            <groupId>${jooq.groupId}</groupId>
            <artifactId>jooq</artifactId>
            <version>${jooq.version}</version>
        </dependency>
        <dependency>
            <groupId>${jooq.groupId}</groupId>
            <artifactId>jooq-meta</artifactId>
            <version>${jooq.version}</version>
        </dependency>
        <dependency>
            <groupId>${jooq.groupId}</groupId>
            <artifactId>jooq-codegen</artifactId>
            <version>${jooq.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.jooq</groupId>
                    <artifactId>jooq</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <!--считываем настройки-->
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>properties-maven-plugin</artifactId>
                <version>1.0.0</version>
                <executions>
                    <execution>
                        <phase>initialize</phase>
                        <goals>
                            <goal>read-project-properties</goal>
                        </goals>
                        <configuration>
                            <files>
                                <!--отсюда берутся логин/пароль-->
                                <file>src/main/resources/application.properties</file>
                            </files>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>${jooq.groupId}</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>

                <executions>
                    <!-- Generate the required class from the database -->
                    <execution>
                        <id>generate-jooq</id>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>

                <configuration>
                    <!-- Configure the database connection -->
                    <jdbc>
                        <driver>${driver-class-name}</driver>
                        <url>${url}</url>
                        <user>${username}</user>
                        <password>${password}</password>
                    </jdbc>

                    <generator>
                        <database>
                            <!-- <tableValuedFunctions>true</tableValuedFunctions>--> <!--?!-->
                            <!-- Configure the used database dialect -->
                            <name>${jooq.generator.db.dialect}</name>

                            <includes>.*</includes>
                            <inputSchema>${jooq.schema}</inputSchema>

                        </database>
                        <!-- Generate classes for tables and records -->
                        <generate>
                            <globalTableReferences>true</globalTableReferences>
                            <globalUDTReferences>false</globalUDTReferences>
                            <fluentSetters>true</fluentSetters>
                            <javaTimeTypes>true</javaTimeTypes>
                        </generate>
                        <!-- Configure the target package and directory -->
                        <target>
                            <packageName>ru.sportmaster.wfm.jooq.db</packageName>
                            <directory>target/generated-sources/jooq</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>

        </plugins>
    </build>

</project>

package ru.techclub.jooq;

import org.jooq.Configuration;
import org.jooq.Record;
import org.jooq.Record2;
import org.jooq.Result;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import ru.sportmaster.wfm.jooq.db.Routines;
import ru.sportmaster.wfm.jooq.db.Tables;
import ru.sportmaster.wfm.jooq.db.packages.DemoTestPkg;
import ru.sportmaster.wfm.jooq.db.packages.demo_test_pkg.GetMinMax;
import ru.sportmaster.wfm.jooq.db.packages.demo_test_pkg.udt.records.TIntsRecord;
import ru.sportmaster.wfm.jooq.db.packages.demo_test_pkg.udt.records.TTabRecord;
import ru.sportmaster.wfm.jooq.db.tables.records.VDemoTableRecord;
import ru.sportmaster.wfm.jooq.db.udt.records.TInnerObjRecord;
import ru.sportmaster.wfm.jooq.db.udt.records.TTabObjRecord;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.LocalDate;

import static org.jooq.impl.DSL.count;

@SpringBootTest
public class Demo {
    /* основной бин из библиотеки jooq, который мы будем использовать для
    обращения к бд */
    @Autowired
    Configuration conf;

    @Test
    void getView() {

        Result<VDemoTableRecord> result = conf.dsl().selectFrom(Tables.V_DEMO_TABLE).fetch();

        result.forEach(record -> {
            System.out.println(record.getA());
            System.out.println(record.getB());
        });
    }

    @Test
    void getViewGroupBy() {
        Result<Record2<Byte, Integer>> agg = conf.dsl()
                .select(Tables.V_DEMO_TABLE.A, count())
                .from(Tables.V_DEMO_TABLE)
                .where(Tables.V_DEMO_TABLE.A.eq((byte) 1))
                .groupBy(Tables.V_DEMO_TABLE.A)
                .fetch();

        agg.forEach(record -> {
            System.out.println(record.component1());
            System.out.println(record.component2());
        });
    }

    @Test
    void getNumValue() {
        BigDecimal r = Routines.getNumValue(conf);
        System.out.println(r);
    }

    @Test
    void getMinMax() {
        GetMinMax r = DemoTestPkg.getMinMax(
                                conf,
                                new TIntsRecord(BigInteger.ONE, BigInteger.TWO, BigInteger.TEN)
                      );

        System.out.println(r.getPMin());
        System.out.println(r.getPMax());
    }

    @Test
    void getSysRefCursor() {
        Result<Record> result = DemoTestPkg.getSysRefcursor(conf);
        Integer r = result.get(0).get("A", Integer.class);
        System.out.println(r);
    }

    @Test
    void getRefCursor() {
        Result<Record> result = DemoTestPkg.getRefcursor(conf);
        Integer r = result.get(0).get("A", Integer.class);
        System.out.println(r);
    }

    @Test
    void getCollection() {
        TTabRecord r = DemoTestPkg.getCollection(conf);
        r.forEach(record -> {
            System.out.println(record.getA());
            System.out.println(record.getB());
        });
    }

    @Test
    void getCollectionPipe() {

        TTabRecord r = DemoTestPkg.getCollectionPipe(conf);
        r.forEach(record -> {
            System.out.println(record.getA());
            System.out.println(record.getB());
        });
    }

    @Test
    void getCollectionObj() {

        TTabObjRecord r = Routines.getCollectionObj(
                conf,
                new TInnerObjRecord(LocalDate.now(), BigDecimal.ONE, "World!")
        );

        r.forEach(record -> {
            System.out.println(record.getDtWithTime());
            System.out.println(record.getFObj().getDt());
            System.out.println(record.getFObj().getNumVal());
            System.out.println(record.getFObj().getStrVal());
        });
    }
}

create table demo_table
(
    a number(2),
    b varchar2(5)
);

insert into demo_table(a, b) values (1, 'hello');
insert into demo_table(a, b) values (1, 'hell');
insert into demo_table(a, b) values (2, 'world');

commit;

create or replace view v_demo_table
as
select * from demo_table;

grant read on v_demo_table to app_user;

-- функция возвращающая число
create or replace function get_num_value return number
as
begin
    return 2; 
end;
/
grant execute on get_num_value to app_user;

create or replace type t_inner_obj as object
(
    dt date,
    num_val number,
    str_val varchar2(100 char)
);
/
create or replace type t_obj as object
(
    dt_with_time timestamp,
    f_obj t_inner_obj
);
create or replace type t_tab_obj is table of t_obj;
/

create or replace function get_collection_obj
(
    p_inner_obj in t_inner_obj
) return t_tab_obj
as
   v_res t_tab_obj;
begin
    select 
        t_obj
        (
            dt_with_time => sysdate, 
            f_obj => p_inner_obj
        ) 
    bulk collect into 
        v_res 
    from 
        dual;
        
    return v_res;
end;
/

grant execute on t_tab_obj to app_user;
grant execute on t_obj to app_user;
grant execute on t_inner_obj to app_user;
grant execute on get_collection_obj to app_user;


create or replace package demo_test_pkg is

-- коллекция целых чисел
type t_ints is table of integer;

-- рекорд
type t_rec is record
(
    a number(2),
    b varchar2(10 char)
);
-- курсор рекордов
type t_cur is ref cursor return t_rec;

-- коллекция рекордов
type t_tab is table of t_rec;


procedure get_min_max
(
    p_ints in t_ints,
    p_min out integer,
    p_max out integer
);

function get_sys_refcursor return sys_refcursor;

function get_refcursor return t_cur;

function get_collection return t_tab;

function get_collection_pipe return t_tab pipelined;

end demo_test_pkg;
/
create or replace package body demo_test_pkg is

procedure get_min_max
(
    p_ints in t_ints,
    p_min out integer,
    p_max out integer
)
as   
begin   
    p_min := p_ints.first;
    p_max := p_ints.first;
    for i in p_ints.first .. p_ints.last loop
        if p_ints(i) > p_max then
          p_max := p_ints(i);
        end if;
        if p_ints(i) < p_min then
          p_min := p_ints(i);
        end if;        
    end loop;
end;

function get_sys_refcursor return sys_refcursor
  as
     v_res sys_refcursor;
  begin
    open v_res for select 1 as a, 'hello' as b from dual
     union all select 2, 'world!' from dual;
     return v_res;
  end;

function get_refcursor return t_cur
as
  v_res t_cur;
begin
    open v_res for select 1 as a, 'hello' as b from dual
     union all select 2, 'world!' from dual;
     return v_res;
  
end;  

function get_collection return t_tab
as
  v_res t_tab;
begin
    select q.* bulk collect into v_res from (select 1 as a, 'hello' as b from dual
     union all select 2, 'world!' from dual) q;
     
     return v_res;
     
end;

function get_collection_pipe return t_tab pipelined
as
begin
   for i in (select 1 as a, 'hello' as b from dual
     union all select 2, 'world!' from dual) loop
     pipe row(i);
     end loop;
end;

end demo_test_pkg;
/
grant execute on demo_test_pkg to app_user;