This tutorial shows how to use the Oracle database connection in the Java reports. For example, let's use the following Oracle connection string:
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl;user=sys as sysdba;password=oracle
Using the Oracle functions
For example we will use the function with one IN parameter:
create or replace function doubling(param1 IN INTEGER) return INTEGER is
begin
return param1 * 2;
end;
We need to create the parameter for input and column for the function output. Change the query in the following way:
@Column1 = call doubling(@Parameter1)
Using the Oracle procedures
For example, we will use the procedure with one IN and one OUT parameter:
create or replace procedure doublingProc
(
paramIn IN INTEGER,
paramOut OUT INTEGER
) is
BEGIN
paramOut := paramIn * 2;
END;
We need to create the parameter for input parameter and column for output. Change the query in the following way:
call doublingProc(@Parameter1, @Column1)
Ref Cursor
For example we will use the procedure with one IN and one OUT ref cursor parameter:
create or replace procedure get_test2
(
p_col_value in varchar2,
res_cursor out sys_refcursor
) is
begin
open res_cursor for 'select * from test where column1 = :col_value' using p_col_value;
end;
We need to create the parameter for input. For ref cursor we must use the
@refcursor
parameter name. Change the query in the following way:
call get_test2(@Parameter1, @refcursor)
Click the 'Retrieve Columns' button and it will fill the columns according to the ref cursor result.
На скриншоте ниже Вы можете увидеть результат выполнения данного кода: