Java call procedure oracle plsql return no data but in plsql return data

Question

I have a function plsql, run function in plsql return cursor have data But i call this function in java problem return resultset falase Help me handling problem this

enter code here

This function plsql

FUNCTION get_canh_bao(
        p_toolid        in  varchar2)
      RETURN  sys_refcursor IS

        s           varchar2(4000);
        ptype       varchar2(1000);
        pday        varchar2(1000);
        psql        varchar2(1000);
        pcreate     varchar2(1000);
        re          sys_refcursor;
        pwhere      varchar2(1000);
    BEGIN
    --
        pwhere := '';
        s := 'select b.TYPE_REPORT, b.DAY_NHAPLIEU, a.sql_cmd, a.name_createdate'
            ||' from cpcdata.tbl_config_nhaplieu a'
            ||' left join cpcdata.tbl_mainmenu b'
            ||' on a.tool_id = b.ID '
            ||' where b.show_all = 0 and b.flag = 1 and b.ID = :toolid';
        execute immediate s INTO ptype, pday, psql, pcreate using p_toolid;
                -- Tinh ngay canh bao
                if (INSTR(psql,'where') > 0 ) then
                    pwhere := ' and ';
                else
                    pwhere := ' where ';
                end if;
                s := psql ||pwhere||pcreate||' between :aDate AND :bDate';
                CASE
                    WHEN ptype = 'day'    THEN
                        s := psql ||pwhere|| ' to_char('||pcreate||',''dd/mm/yyyy'') = to_char(sysdate - '||pday||',''dd/mm/yyyy'')';
                        open re for s;
                    WHEN ptype = 'week'   THEN
                        open re for s USING TRUNC (sysdate, 'iw'), TRUNC(sysdate, 'iw') + 7 - 1/86400- pday;
                    WHEN ptype = 'month'  THEN
                        open re for s USING TRUNC (sysdate, 'mm'), to_date(LAST_DAY(TRUNC (sysdate, 'mm')) + 1 - 1/86400 - pday,'dd/mm/yyyy');
                    WHEN ptype = 'quy'    THEN
                        open re for s USING TRUNC (sysdate, 'Q'), to_date(add_months(trunc(sysdate,'Q'),3)- 1 - pday, 'dd/mm/yyyy');
                    WHEN ptype = 'year'   THEN
                        open re for s USING TRUNC (sysdate , 'YEAR'), to_date(ADD_MONTHS(TRUNC (SYSDATE,'YEAR'),12) - 1 - pday, 'dd/mm/yyyy');
                    ELSE return null;
                END CASE;
        dbms_output.put_line(''||s);
        RETURN re ;
        exception when others then
            declare
                s_err       varchar2(2000);
                str         varchar(2000);
                c_err       sys_refcursor;
            begin
                s_err := 'loi khi lay du lieu '|| sqlerrm;
                str := 'select '||s_err||' from dual';
                open c_err for str;
                return c_err;
            end;
    END;

This is code java

  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection conn =
      DriverManager.getConnection("jdbc:oracle:thin:@***:**:**",
                                  "***", "***");
  System.out.println("Connect db success ! ");
ArrayList arr = new ArrayList();
            String sql = "{? = call get_canh_bao(?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            cs.setString(2, "502");
            cs.executeQuery();
            ResultSet rs = (ResultSet)cs.getObject(1);
            System.out.println("------------ResultSet---------------"+rs.next());
            while (rs.next()) {

              System.out.println("a");
              String[] str =
              {"1" };
              arr.add(str);
            }

  System.out.println("size="+arr.size());

Result when run code in java

Connect db success ! 
------------ResultSet---------------false
size=0

Answer 1

You're populating pcreate (a string) from tbl_config_nhaplieu.name_createdate without specifying a format, so (assuming it is a date column) it is possible that the format could be different within the Java client environment, e.g. if it defaults to mm/dd/yyyy or dd-mon-yyyy instead of dd/mm/yyyy.

Later you compare it directly to dates:

s := psql || pwhere || pcreate || ' between :aDate and :bDate';

but without quoting, so you'll get a query like this:

where 01/02/2016 between :aDate and nDate

Elsewhere you convert it back to a date assuming dd/mm/yyyy format (which you didn't specify when you encoded it as a string earlier, so there's no reason it should match that format):

s := psql || pwhere || ' to_char(' || pcreate || ',''dd/mm/yyyy'') = to_char(sysdate - ' || pday || ',''dd/mm/yyyy'')';

but it's not quoted there either, so you'll get something like this:

where to_char(01/02/2016,'dd/mm/yyyy') = to_char(sysdate - 3,'dd/mm/yyyy')

which will fail.

Then there's this:

to_date(last_day(trunc(sysdate,'mm')) + 1 - 1 / 86400 - pday, 'dd/mm/yyyy')

which may not give the result you expect. For example:

select to_date(last_day(trunc(sysdate,'mm')) + 1 - 1 / 86400 - 0, 'dd/mm/yyyy') as test
from dual;

which gives me 31-JUL-2016 in SQL*Plus and SQL Developer but 31/07/0016 in PL/SQL Developer, due to different nls_date_parameter settings.

As Jon said, you need to clean up all of the date handling.

Answer 2

The problem is likely caused by one of the date conversions that uses an implicit date format.

Oracle does not have a "default" date format, the date format is always dependent on the client. Your Java client and database IDE likely have different defaults.

But don't fix the code by changing the client settings. Instead, clean up the date functions and remove all date to character conversions.

In general, when working with dates, TO_DATE and TO_CHAR should almost always be avoided. If a date needs to be manipulated, there is almost surely a date function that does not require a conversion. And dates should always be stored as dates, so you should not need to convert back and forth from the table.

The biggest problem is probably this:

    pcreate     varchar2(1000);

Change that to a date. If for some reason a.name_createdate is a string, then at least make the conversion explicit.