Using Oracle Database

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:@192.168.157.128: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 functions

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)


Using the Oracle procedures

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.

Ref Cursor