ORA-00942: table or view does not exist but query runs on SQL developer

Question

I got an oracle sql query which runs fine on oracle sql developer but when I run it from Java prepared statement sql exception thrown as "ORA-00942: table or view does not exist " and the user got all the privileges for mentioned 3 schemas. is there any problem with the Query ? currently I can't seems to find the problem and still debugging sql string is also can be executed in SQL developer .

SQL query

SELECT CLI_CLIENT.NAME ,CLI_CLIENT.CLIENT_ID,

AA.SEARCHES,

(SELECT COUNT(*) FROM RES_BOOKING,CLI_WEB_USER WHERE  RES_BOOKING.BOOKED_USER=CLI_WEB_USER.ADM_USER_ID

AND TRUNC(RES_BOOKING.BOOKING_DATE) BETWEEN '01-MAR-16' AND '24-MAR-16' AND CLI_WEB_USER.CLIENT_ID=CLI_CLIENT.CLIENT_ID)AS BOOKINGS

FROM CLI_CLIENT,

(SELECT CWU.CLIENT_ID,

COUNT(ST.OPERATION) AS SEARCHES 

FROM STAT.ST_TRANSACTION ST , CLI_WEB_USER CWU , CACHE.CACC_CRITERIA CC , CACHE.CACC_CRITERIA_STATS CS

 WHERE ST.USER_NAME=CWU.USERNAME

 AND ST.OPERATION LIKE 'OTA_HotelAvailRQ%'

 AND TRUNC(ST.TRS_TIMESTAMP) BETWEEN '01-MAR-16' AND '24-MAR-16'

AND CWU.CLIENT_ID IN (10975,10040)

AND CC.CRITERIA_ID=CS.CRITERIA_ID

AND CS.SESSION_ID=ST.SESSION_ID

AND CS.DISTRIBUTION_CHANNEL='W'

 GROUP BY CLIENT_ID, CWU.CLIENT_ID

)AA

WHERE CLI_CLIENT.CLIENT_ID=AA.CLIENT_ID;

Java Exception

 java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Answer 1

Check if you are connected to the database under the same user account in both SQL Developer and your java application. Most likely this is not the case and your java application has no access to one or more of the tables in the query.

Answer 2

The following is your query in a better view and performance:

select C.NAME,
       C.CLIENT_ID,
       AA.SEARCHES,
       (select count(1) 
          from RES_BOOKING R
          join CLI_WEB_USER U
            on U.ADM_USER_ID = R.BOOKED_USER
           and trunc(R.BOOKING_DATE) 
                between '01-MAR-16' 
                    and '24-MAR-16' 
           and U.CLIENT_ID = C.CLIENT_ID
        ) AS BOOKINGS
  from CLI_CLIENT C
  join 
       (select CWU.CLIENT_ID as CLIENT, 
               count(ST.OPERATION) AS SEARCHES 
          from STAT.ST_TRANSACTION ST 
          join CLI_WEB_USER CWU
            on CWU.USERNAME = ST.USER_NAME
          join CACHE.CACC_CRITERIA CC
            on CC.CRITERIA_ID = CS.CRITERIA_ID
          join CACHE.CACC_CRITERIA_STATS CS
            on CS.SESSION_ID = ST.SESSION_ID
         where ST.OPERATION like 'OTA_HotelAvailRQ%'
           and trunc(ST.TRS_TIMESTAMP) 
                between '01-MAR-16' 
                    and '24-MAR-16'
           and CWU.CLIENT_ID in (10975,10040)
           and CS.DISTRIBUTION_CHANNEL='W'
      group by CLIENT_ID, CLIENT -- OLD OF CLIENT -> CWU.CLIENT_ID
        ) AA
    on AA.CLIENT = C.CLIENT_ID; -- OLD OF AA.CLIENT -> AA.CLIENT_ID

Then divide your query to blocks and execute one by one. For example,

First part:

select C.NAME,
       C.CLIENT_ID,
       (select count(1) 
          from RES_BOOKING R
          join CLI_WEB_USER U
            on U.ADM_USER_ID = R.BOOKED_USER
           and trunc(R.BOOKING_DATE) 
                between '01-MAR-16' 
                    and '24-MAR-16' 
           and U.CLIENT_ID = C.CLIENT_ID
        ) AS BOOKINGS
  from CLI_CLIENT C;

Second part: take the code inside JOIN block, and so on. If you will get an error in that block, divide more and keep on this.