Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

comparing Last create date with sysdate

User_R4VJDJun 9 2021 — edited Jun 9 2021

Create date
Null

select * from table1
where create date < TRUNC(SYDATE)
Requirement : I have check create date should be less than sysdate.
if column is NULL, how to perform

Comments

Mike Kutz

The ability for your Compute node (VM) to access your ADB seems to indicate that you do not have a firewall issue when talking in that direction. (I suspect you will need tell OCI to allow the Compute Node to be accessed by the ADB node [on specific ports])
Right off, it sounds like Tomcat needs the tnsnames.ora file found within the .ZIP file (that you download from the OCI Web page) in addition to the specific Wallet (also in .ZIP file).
Which documents/sites are you following?
What have you tried?
What were the results? (Especially error codes. Maybe log files)
note I haven't used Jasper Reports myself.

Mohamed Heba

Thank you, Mike, for your reply.
Yes, i can't see any network or firewall between the 2 servers. I eliminated this by connecting to the database from the same server using SQL.
I couldn't find a proper documentation i can follow to resolve this issue. only few links below with no success.
[Jasperreportsintegration on linux and autonomus database · Issue #74 · daust/JasperReportsIntegration](https://github.com/daust/JasperReportsIntegration/issues/74) (0 Bytes)and
Connecting to Autonomous Transaction Processing with Tomcat - YouTube
I downloaded the wallet and modified the DataSource as mentioned in these links as below.
[datasource:JasperDS]
type=jdbc
name=default
url=jdbc:oracle:thin:@adb.us-ashburn-1.oraclecloud.com:1521/APEXDB
username=dbusername
password=DB_Password

[datasource:JasperDS1]
type=jdbc
name=default
url="jdbc:oracle:thin:@apexdb_high?TNS_ADMIN=/tmp/dbwalet"
username=dbusername
password=DB_Password

[datasource:JasperDS2]
type=jdbc
name=default
url=jdbc:oracle:thin:@apexdb_high
username=dbusername
password=DB_Password

[datasource:JasperDS3]
type=jdbc
name=default
url=jdbc:oracle:thin:@adb.eu-frankfurt-1.oraclecloud.com:1521/APEXDB
username=dbusername
password=DB_Password

[datasource:JasperDS4]
type=oracle.ucp.jdbc.PoolDataSource
name=default
url="jdbc:oracle:thin:@apexdb_high?TNS_ADMIN=/tmp/dbwalet"
username=dbusername
password=DB_Password
-- Errors Received
13-Jan-2022 01:17:09.898 SEVERE [http-nio-8080-exec-9] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception
java.lang.RuntimeException: Could not connect via JDBC: IO Error: The Network Adapter could not establish the connection
at de.oc.utils.Utils.throwRuntimeException(Unknown Source)
at de.oc.db.ConnectionUtility.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.AppConfig.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.Test.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
13-Jan-2022 01:17:21.362 SEVERE [http-nio-8080-exec-6] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception
java.lang.RuntimeException: Could not connect via JDBC: IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]"
at de.oc.utils.Utils.throwRuntimeException(Unknown Source)
at de.oc.db.ConnectionUtility.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.AppConfig.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.Test.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
13-Jan-2022 01:17:25.253 SEVERE [http-nio-8080-exec-4] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception
java.lang.NullPointerException
at de.oc.integration.jasper.webapp.Test.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
13-Jan-2022 01:17:28.471 SEVERE [http-nio-8080-exec-7] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception
java.lang.RuntimeException: Could not connect via JDBC: IO Error: Connection reset
at de.oc.utils.Utils.throwRuntimeException(Unknown Source)
at de.oc.db.ConnectionUtility.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.AppConfig.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.Test.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
13-Jan-2022 01:17:31.965 SEVERE [http-nio-8080-exec-8] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception
java.lang.RuntimeException: Could not connect via JDBC: IO Error: Unknown host specified
at de.oc.utils.Utils.throwRuntimeException(Unknown Source)
at de.oc.db.ConnectionUtility.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.AppConfig.getConnection(Unknown Source)
at de.oc.integration.jasper.webapp.Test.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)

Thanks again for your time.

Mike Kutz

What Version of Jasper Reports are you using?
Did you copy over odbci8 like the person did in the bug report?
I'd have to research the correct jdbc string to use, but the ones you have posted seem too short.

Mohamed Heba

JasperReportsIntegration-2.4.0.0
Yes I am using ojdbc8
Sorry i didn't know what you mean by this "I'd have to research the correct jdbc string to use, but the ones you have posted seem too short."

I used same setting to connect to autonomous DB from my windows VM and it worked just yesterday
[datasource:TRIALPOS1]
type=jdbc
name=default
url=jdbc:oracle:thin:@apexdb_high?TNS_ADMIN=C:/Source/PCW/
username=username
password=password

Local windows VM
image.png
Cloud unix VM
image.png

MrHarris

Hi i managed to connect with both versions, old and new, i mean on jasperreportsintegration. The key is if you use old version then you have to use keystore to conect. With new version you can use standard aproach with edit sqlnet ora with corect path. Can you conect with jasperstudio on autonomus db? il provide more info tommorow with details

Mohamed Heba

Thanks @mrharris. I never used jasperstudio, Waiting for the details.

MrHarris

Can you provide connection string and driver version for jasperreport connection with autonomus database?.
If you use old version of Jasperreportsintegration 2.4 than modify ojdbc.properties file like this:
__
# Connection property while using Oracle wallets.
#oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
# FOLLOW THESE STEPS FOR USING JKS
# (1) Uncomment the following properties to use JKS.
# (2) Comment out the oracle.net.wallet_location property above
# (3) Set the correct password for both trustStorePassword and keyStorePassword.
# It's the password you specified when downloading the wallet from OCI Console or the Service Console.
javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks
javax.net.ssl.trustStorePassword=test
javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
javax.net.ssl.keyStorePassword=test
__
Be sure to uncomment these lines. Modify sqlnet.ora like this, be sure to make path to your wallet and tns , you write your own path to wallet:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u01/tomcat/apache-tomcat-8.5.63/webapps/JasperReportsIntegration/WEB-INF/Wall")))
After these changes restart tomcat and it should connect. And You have to use odbci8 like u mention in previous post.
And i notice u put wallet in tmp folder, you should not do that, make another path.

Hope it helps
Haris

Shabbir395

Struggled many days to integrate but succeed after commenting out oracle.net.wallet_location in ojdbc.properties file and uncommenting JKS settings.

user-oegidio_8426275

Hi gurus:

My Environment is working right, I want share to you my settings

Oracle Linux 8.6-x86_64
Oracle Database 23c Free
Ords 23.1.2.r1151944
Apex 23.1.0
Java-Runtime-Version: 11.0.19
Apache Tomcat 9-0-65
Jasper Report Integration 2.4.0.

  1. Edit LISTENER.ORA add the lines

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = FREE)
(ORACLE_HOME = /opt/oracle/product/23c/dbhomeFree
)
)
(SID_DESC =
(SID_NAME = FREEPDB1)
(ORACLE_HOME = /opt/oracle/product/23c/dbhomeFree
)
)
)

2. Warning Check the correct way to setting url to jdbc in file /opt/apache-tomcat-9.0.65/webapps/jri/WEB-INF/conf/application.properties
url=jdbc:oracle:thin:@//127.0.0.1:1521/FREEPDB1

3. Edit SQLNET.ORA /opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora
add
SQLNET.ALLOWED_LOGON_VERSION=8

4. ojdbc8.jar in /opt/apache-tomcat-9.0.65/lib

5. If "ORA-01017: invalid username/password; logon denied" error, then you need to re-create your password.

It works with jri 2.10.0 too.

1 - 9

Post Details

Added on Jun 9 2021
#plsql, #sqldeveloper
7 comments
775 views
2 mentions