Posted on

Handling – ORA-01858: a non-numeric character was found where a numeric was expected

Requirement – Executing an sql query and found very weird error.ORA-01858: a non-numeric character was found where a numeric was expected.

Solutions –

the query i am executing 

Select Count(*) As Amount, To_Char(Dactiondate, 'YYYY-MM') "month" From Docmeta,Revisions,Workflowhistory,
(Select Ddocname As Docname,Max(Drevisionid) As Revisionid From Revisions Group By Ddocname) A Where  Revisions.Ddocname=A.Docname
And Revisions.Drevisionid=A.Revisionid And  Revisions.Did=Docmeta.Did
And  Workflowhistory.Ddocname=Revisions.Ddocname And Daction='Approve' And ((  Dwfname='InternalBUReviewProcess'
And Trunc(Dactiondate)<Trunc(To_Date(Substr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)), Instr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)),Duser,1,1)+(Length(Duser)) + 2, 8),'MM-DD-YY') ))
AND UPPER(xIsRushRelease) = UPPER('true')
And  Dactiondate Between To_Date ('2013-06' , 'YYYY-MM') And Last_Day(To_Date ( '2013-08 23:59:59' ,'YYYY-MM hh24:mi:ss'))
 
Group By To_Char(Dactiondate, 'YYYY-MM') Order By To_Char(Dactiondate, 'YYYY-MM')

It is working fine when, i am executing in toad.but when i am adding another clause i.e UPPER(xIsRushRelease) = UPPER(‘true’), it start giving this error.Big problem,not able to understand what went wrong.
This sql query is very big.

ORA-01858 occurs in string-to-DATE conversions, when the string being converted doesn’t match the format string. You can expect errors like that whenever you store date information in string columns.

I thought of creating an sql function and checking , where it getting wrong.

i created a function like –

create or replace function getDateNew (DUSER IN varchar2,Dwfstepname IN varchar2,Xreviewerdeadline IN varchar2)
return date is

  l_sysdate date;

begin

 select cast(To_Date(Substr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)), 
Instr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)),Duser,1,1)+(Length(Duser)) + 2, 8),'MM-DD-YY')as date) Into L_Sysdate
From Dual;
   Return L_Sysdate;

End;

Ok, now i am using function in my query like

Select Duser,Xreviewerdeadline,Dactiondate,Docmeta.Did,Xbu,Xoccasionevent,Getdatenew(Duser,Dwfstepname,Xreviewerdeadline) as deadline, Trunc(Dactiondate) As Dactiondate From Docmeta,Revisions,
Workflowhistory, 
(Select Ddocname As Docname,Max(Drevisionid) As Revisionid From Revisions Group By Ddocname) A Where  
 Revisions.Ddocname=A.Docname And Revisions.Drevisionid=A.Revisionid And  Revisions.Did=Docmeta.Did 
AND Daction='Approve' And Workflowhistory.Ddocname=Revisions.Ddocname 
And   Dwfname='InternalBUReviewProcess' 
And Trunc(Dactiondate)<Trunc(Getdatenew(Duser,Dwfstepname,Xreviewerdeadline))

Its working fine.But when i added another clause , it again giving me same error.I am totally frustrated.Then , i try to handle the exception in my function.There is some exception coming, when i am adding another clause i.e Xbu=Upper(‘H IM SY’)

I need to handle the exception in my function.Finally adding exception part in my query like

create or replace function getDateNew (DUSER IN varchar2,Dwfstepname IN varchar2,Xreviewerdeadline IN varchar2)
return date is

  l_sysdate date;

begin

 select cast(To_Date(Substr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)), 
Instr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)),Duser,1,1)+(Length(Duser)) + 2, 8),'MM-DD-YY')as date) Into L_Sysdate
From Dual;
 DBMS_OUTPUT.PUT_LINE(L_Sysdate);
  Return L_Sysdate;
    DBMS_OUTPUT.PUT_LINE(L_Sysdate);
  Exception 
  when others then return null;

End;

After handling exception, it working fine.I handled exception.

Happy coding with Vinay kumar in Techartifact….

Posted on

Set value in attribute in binding in Oracle ADF

Requirement – how to set value of attribute value in binding…

Solutions – you can use this expression –

JsfUtils.setExpressionValue("#{ManagedBean.empName}", "Vinay");

You have to add this method in JsfUtils class.

Note to call the getters and setters via your EL expression, you don’t include the get/set prefix.

 public static void setExpressionValue(String expression, Object newValue) {
    FacesContext facesContext = getFacesContext();
    Application app = facesContext.getApplication();
    ExpressionFactory elFactory = app.getExpressionFactory();
    ELContext elContext = facesContext.getELContext();
    ValueExpression valueExp = elFactory.createValueExpression(elContext, expression, Object.class);

    //Check that the input newValue can be cast to the property type
    //expected by the managed bean.
    //If the managed Bean expects a primitive we rely on Auto-Unboxing
    //I could do a more comprehensive check and conversion from the object
    //to the equivilent primitive but life is too short
    Class bindClass = valueExp.getType(elContext);
    if (bindClass.isPrimitive() || bindClass.isInstance(newValue)) {
      valueExp.setValue(elContext, newValue);
    }
  }

Happy coding in Techartifact with Vinay…

Posted on

Setting Styles & Formula in excel file using Apache POI

Requirement – Setting styles on excel cells

Solutions– You can set FONT,Color, Bold etc.Use cell.setCellStyle(style) method.

Following is code for setting styles

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Style example");
<strong> //Example 1 of setting styles in cell</strong>
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName(HSSFFont.FONT_ARIAL); 
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
 
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Setting text in BOLD and in Arial font");
cell.setCellStyle(style);
 
<strong>  //Example 2 of setting styles in cell</strong>
font = workbook.createFont();
font .setItalic(true);  // setting cell value as italic
font setFontHeightInPoints((short) 12);   //setting font height.
font.setColor(HSSFColor.GREEN.index);  // setting color as green
style = workbook.createCellStyle();
style.setFont(font);
 
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("Setting text in italic & green");
cell.setCellStyle(style);
 
try {
    FileOutputStream out = new FileOutputStream(new File("C:\\Vinaystyle.xls"));
    workbook.write(out);
    out.close();
    System.out.println("Excel written successfully..");
     
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

You can also set the background color for the rows as well in sheet

  // Aqua background
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.BIG_SPOTS);
row.setRowStyle(style);

Setting Formula in Excel –

    Row row= sheet.createRow(1);
    row.createCell(0).setCellValue(100);
    rpw.createCell(1).setCellValue(200);
    row.createCell(2).setCellValue(300);
    row.createCell(3).setCellFormula("SUM(A1:A4)");
  or 
    cell.setCellFormula("A2*B3*C4")

For evaluating formula you can use –

FormulaEvaluator evaluator = 
workbook.getCreationHelper().createFormulaEvaluator();

evaluateFormulaCell(Cell cell) will check to see if the supplied cell is a formula cell. If it isn’t, then no changes will be made to it. If it is, then the formula is evaluated. The value for the formula is saved alongside it, to be displayed in excel. The formula remains in the cell, just with a new value.

Happy coding with Vinay in techartifact