CODEWITHSUNDEEP
javasqljdbc
Thiyagarajan
Thiyagarajan

Reputation: 15

java.sql.SQLException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

I have scussfully read B details from Config properti file .Now i'm trying to excute the query by passing from and to date values through command line arguments.I'm getting below exception . please let me know how to pass date values in to my query via command line arguments. Note: I have created excutalbel Jar file with all library files.

Output:
java -jar CreateExcelFile1.jar 2014/01/20 2014/01/24

        Command line arguments length is 2
       Array index 0 value is 2014/01/20
      Array index 1 value is 2014/01/24
       Connection Successful
         java.sql.SQLException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Code:

   public class CreateExcelFile extends DbConnection{
   public static void main(String[] args) {
  try{
System.out.println("Command line arguments length is " + args.length);
System.out.println("Array index 0 value is " + args[0]);
System.out.println("Array index 1 value is " + args[1]);
      Connection con = openConnection();
      PreparedStatement st=con.prepareStatement("Select a.oi_intervention_id ,a.oi_region,a.oi_mrn,b.opi_title, a.oi_rx_nb ,a.oi_store_id,to_char(a.oi_doc_crtd_dt,'dd-MON-yy') as SheetName,to_char(a.oi_doc_crtd_dt,'dd-MON-yy hh.mi.ss,FF AM TZH:TZM') as oi_doc_crtd_dt,to_char(a.oi_doc_expry_dt,'dd-MON-yy hh.mi.ss,FF AM TZH:TZM') as oi_doc_expry_dt from rxesbopcs.OPCS_INTERVENTION a ,rxesbopcs.opcs_program_info b where trunc(a.oi_doc_crtd_dt) BETWEEN TO_DATE ('args[0]', 'yyyy/mm/dd') AND TO_DATE ('args[1]', 'yyyy/mm/dd') and b.opi_program_id = a.oi_program_id order by a.oi_doc_crtd_dt",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
    ResultSet rs=st.executeQuery();
     createXls(rs);
     System.out.println("Your excel file has been generated!");
      closeConnection(con);
    } catch ( Exception ex ) {
     System.out.println(ex);
     }
     }

Upvotes: 0

Views: 3652

Answers (2)

bosmacs
bosmacs

Reputation: 7483

I think you need to put placeholders in the query string where you want to insert variable values, e.g. instead of 

 BETWEEN TO_DATE ('args[0]', 'yyyy/mm/dd')

you would put

 BETWEEN TO_DATE (?, 'yyyy/mm/dd')

and then call st.setString(1, args[0]); before executing the query, and similarly for other parameters.

You may want to review how to use prepared statements.

Upvotes: 1

fvu
fvu

Reputation: 32973

That's not how PreparedStatements are given parameters (sql spread over a couple of lines to improve readability):

PreparedStatement st=con.prepareStatement("Select a.oi_intervention_id 
     ,a.oi_region,a.oi_mrn,b.opi_title, a.oi_rx_nb 
     ,a.oi_store_id,to_char(a.oi_doc_crtd_dt,'dd-MON-yy') as SheetName,
     to_char(a.oi_doc_crtd_dt,'dd-MON-yy hh.mi.ss,FF AM TZH:TZM') as oi_doc_crtd_dt,
     to_char(a.oi_doc_expry_dt,'dd-MON-yy hh.mi.ss,FF AM TZH:TZM') as oi_doc_expry_dt
  from rxesbopcs.OPCS_INTERVENTION a ,rxesbopcs.opcs_program_info b 
  where trunc(a.oi_doc_crtd_dt) BETWEEN TO_DATE (?, 'yyyy/mm/dd') AND 
      TO_DATE (?, 'yyyy/mm/dd') and b.opi_program_id = a.oi_program_id 
  order by a.oi_doc_crtd_dt",
    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

st.setString(1, args[0]);
st.setString(2, args[1]);
ResultSet rs=st.executeQuery();

The Oracle tutorial on JDBC, including PreparedStatements is quite good.

Upvotes: 0

Related Questions