CODEWITHSUNDEEP
oracle-databasescalajdbc
Monami Sen
Monami Sen

Reputation: 129

Retrieving data from oracle table using scala jdbc giving wrong results

I am using scala jdbc to check whether a partition exists for an oracle table. It is returning wrong results when an aggregate function like count(*) is used.

I have checked the DB connectivity and other queries are working fine. I have tried to extract the value of count(*) using an alias, But it failed. Also tried using getString. But it failed.

Class.forName(jdbcDriver)
var connection = DriverManager.getConnection(jdbcUrl,dbUser,pswd)
val statement = connection.createStatement()

 try{
   val sqlQuery = s""" SELECT COUNT(*) FROM USER_TAB_PARTITIONS WHERE 
   TABLE_NAME = \'$tableName\' AND PARTITION_NAME = \'$partitionName\' """
   val resultSet1 = statement.executeQuery(sqlQuery)

   while(resultSet1.next())
   {
      var cnt=resultSet1.getInt(1)
      println("Count="+cnt)

      if(cnt==0)
         // Code to add partition and insert data
      else
        //code to insert data in existing partition
   }

  }catch(Exception e) { ... }

The value of cnt always prints as 0 even though the oracle partition already exists. Can you please let me know what is the error in the code? Is this giving wrong results because I am using scala jdbc to get the result of an aggregate function like count(*)? If yes, then what would be the correct code? I need to use scala jdbc to check whether the partition already exists in oracle and then insert data accordingly.

Upvotes: 0

Views: 119

Answers (1)

Popeye
Popeye

Reputation: 35920

This is just a suggestion or might be the solution in your case.

Whenever you search the metadata tables of the oracle always use UPPER or LOWER on both side of equal sign.

Oracle converts every object name in to the upper case and store it in the metadata unless you have specifically provided the lower case object name in double quotes while creating it.

So take an following example:

-- 1
CREATE TABLE "My_table_name1" ... -- CASE SENSISTIVE

-- 2
CREATE TABLE My_table_name2 ... -- CASE INSENSITIVE

In first query, we used double quotes so it will be stored in the metadata of the oracle as case sensitive name.

In second query, We have not used double quotes so the table name will be converted into the upper case and stored in the metadata of the oracle.

So If you want to create a query against any metadata in the oracle which include both of the above cases then you can use UPPER or LOWER against the column name and value as following:

SELECT * FROM USER_TABLES WHERE UPPER(TABLE_NAME) = UPPER('<YOUR TABLE NAME>');

Hope, this will help you in solving the issue.

Cheers!!

Upvotes: 1

Related Questions