Java Solutions: September 2020

There are several ways to fix the ORA-00932: inconsistent datatypes: expected - got CLOB exception.

Let's say you have a clobColumn in the clobTable with the CLOB datatype. You can use it in the WHERE clause of a select, update, or delete statement by one of the following ways.

1. Use the dbms_lob.compare method

SELECT * from clobTable

WHERE dbms_lob.compare(clobColumn, to_clob('<the value of the column>')) = 0

2. If the clobColumn value is less than 4000 characters, you can use the to_char function.

SELECT * from clobTable

WHERE to_char(clobColumn) = '<the value of the column>';

3. Use like instead of equal.

SELECT * from clobTable

WHERE clobColumn like '<the value of the column>';

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

You received such a error when you started to connect to your Oracle database via JDBC.

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.tds.db.DB.connectToDB(DB.java:118)

This means that the TNS listener is running, but it cannot connect you to the database. It may due to:

the database has not been started up,
the database has not registered with the listener, e.g. because the database was started before the listener. (When the database starts, it registers itself with a listener if it is already running. If the listener isn't running, the database doesn't register itself, and if the listener starts, it doesn't go looking for databases that might register with it.)

To fix the problem:

1. login as the oracle user

>$ su - oracle

enter your oracle user password

2. Execute the below command

>$ lsnrctl status

If you see these in your output:

Services Summary...

Service "your database service" has 1 instance(s).

Instance "your database service", status READY, has 1 handler(s) for this service...

It means that your database is up and running.

3. Login to sqlplus as sysdba

>$ sqlplus /NOLOG

SQL> connect / as sysdba;

4. If your database is not running according to step 2, start the database.

SQL> STARTUP

5. Register the listener

SQL> conn system

SQL> alter system set local_listener = 'ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)' scope = both;

SQL> alter system register;

SQL> quit

6. Check to see if things are right.

$ lsnrctl status

You should see something like below.

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-SEP-2020 17:32:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 02-SEP-2020 16:12:28

Uptime 16 days 1 hr. 20 min. 6 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /12c/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

Listener Log File /12c/app/oracle/diag/tnslsnr/oraclelog/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracleserver)(PORT=5500))(Security=(my_wallet_directory=/12c/app/oracle/admin/db/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "your database service" has 1 instance(s).

Instance "your database service", status READY, has 1 handler(s) for this service...

Service "oracleXDB" has 1 instance(s).

Instance "your database service", status READY, has 1 handler(s) for this service...

The command completed successfully

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Java Solutions

Friday, September 25, 2020

Use clob column in the where clause in an Oracle database; ORA-00932: inconsistent datatypes: expected - got CLOB

Sunday, September 20, 2020

java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor