Select on Oracle - Java

Question

I’m trying to run a connection test in the database and extract some information, just Sfor study even, but I’m bumping into the following error:

Exception in thread "main" java.sql.SQLException: ORA-00923: palavra-chave FROM não localizada onde esperada

How can I fix the error of this code and there is a simpler way to run a select in oracle database or this code is enough?

I want the query to show all values of column 1 (ICCID) in the console.

package Principal;
import java.sql.*;


public class Principal {

         public static void main(String[] args) throws Exception {

                   Connection conexao = ObterConexao();

                   Statement statement = conexao.createStatement();


                   String query = "SELECT a.rp_package_value AS ICCID,"                         
                           + "c.rrs_resource_value AS IMSI,"
                           + "("
                          + "CASE"
                           + " WHEN (SUBSTR (c.rrs_resource_value, 6, 1) = SUBSTR (a.rp_package_value, 9, 1))"
                            + "AND (SUBSTR (c.rrs_resource_value, 6, 2) <> '00')"
                            + "THEN 'Valid'"
                            + "ELSE 'Invalid'"
                          + "END ) AS IMSI_CHECK ,"
                          + "rrs_resource_sts Status, rp_package_sts"
                        + "FROM mtaapp20.rm_packages a,"
                          + "mtaapp20.rm_package_content b,"
                          + "mtaapp20.rm_resource_stock c"
                        + "WHERE a.rp_package_value IN"
                          + "(SELECT RRS_RESOURCE_VALUE"
                          + "FROM mtaapp20.rm_resource_stock c"
                          + "WHERE c.rrs_resource_tp_id = 6"
                          + "AND c.rrs_resource_pool = 30"              
                          + "AND SUBSTR (RRS_RESOURCE_VALUE, 9, 2) like '%1%' "
                          + "AND c.rrs_resource_sts = 'ASSIGNED'"
                          + ")"
                        + "AND a.rp_package_id = b.rpc_package_id"
                        + "AND c.rrs_resource_sts = a.rp_package_sts"
                        + "AND b.rpc_component_tp_id = 5"
                        + "AND b.rpc_component_vl_id = c.rrs_id"
                        + "ORDER BY a.sys_creation_date DESC";





                   ResultSet resultSet = statement.executeQuery(query);


                   if (resultSet.next()) {

                            System.out.println(resultSet.getObject(2));

                   }

         }


         private static Connection ObterConexao() {

                   Connection conexao = null;


                   try {

                            Class.forName("oracle.jdbc.driver.OracleDriver");

                            conexao = DriverManager.getConnection(

                                               "jdbc:oracle:thin:@BRUX:1521:T00WM11", "USER", "SENHA");

                   } catch (ClassNotFoundException e) {

                            e.printStackTrace();

                   } catch (SQLException e) {

                            e.printStackTrace();

                   }


                   return conexao;

         }

}

Answer 1

Try putting space at the end of each string, follow the code:

"rrs_resource_sts Status, rp_package_sts " + "FROM mtaapp20.rm_packages a, "

Answer 2

In several lines they are without white space at the end and at the beginning, thus, after concatenation, the text ends up being 'pasted' as in rp_package_stsFROM and SELECT RRS_RESOURCE_VALUEFROM.

Try adding a blank at the end of all lines.

If you are using Java 8 another way to write SQL can be like this:

String query = String.join(" "
             , "SELECT a.rp_package_value AS ICCID,"
             , "c.rrs_resource_value AS IMSI,"
             , "("
             , "WHEN (SUBSTR (c.rrs_resource_value, 6, 1) = SUBSTR (a.rp_package_value, 9, 1))"
             , "AND (SUBSTR (c.rrs_resource_value, 6, 2) <> '00')"
             , "THEN 'Valid'"
             ...
             ...
             ...
    );

So all rows will be added to the variable query already with the blank.