title: I just need to escape single quote in DECODE Function in oracle, need help on this?
body: <p>Here is my code : When I a'm testing with single quote for fields <code>name</code> and <code>gender</code> getting the below exception:</p>
  <pre><code>}
      sql = &quot;SELECT * FROM dummy.employee_details &quot; + &quot;WHERE name = decode('&quot; + name + &quot;','',name,'&quot; + name
                      + &quot;') AND &quot; + &quot;fname = decode('&quot; + fname + &quot;','',fname,'&quot; + fname + &quot;') AND &quot; + &quot;gender = decode('&quot;
                      + gender + &quot;','',gender,'&quot; + gender + &quot;') AND &quot; + &quot;Age = decode(&quot; + Age + &quot;,null,Age,&quot; + Age
                      + &quot;) AND &quot; + &quot;startDate = decode(to_date('&quot; + query_date
                      + &quot;','yyyy-mm-dd'),'null',startDate,to_date('&quot; + query_date + &quot;','yyyy-mm-dd')) AND &quot;
                      + &quot;salary = decode(&quot; + salary + &quot;,null,salary,&quot; + salary + &quot;)&quot;;
  } else {
      sql = &quot;SELECT * FROM dummy.employee_details &quot; + &quot;WHERE name = decode('&quot; + name + &quot;','',name,'&quot; + name
                      + &quot;') AND &quot; + &quot;fname = decode('&quot; + fname + &quot;','',fname,'&quot; + fname + &quot;') AND &quot; + &quot;gender = decode('&quot;
                      + gender + &quot;','',gender,'&quot; + gender + &quot;') AND &quot; + &quot;Age = decode(&quot; + Age + &quot;,null,Age,&quot; + Age
                      + &quot;) AND &quot; + &quot;salary = decode(&quot; + salary + &quot;,null,salary,&quot; + salary + &quot;)&quot;;
  }
  </code></pre>
  <p><strong>Exception</strong></p>
  <pre><code>java.sql.SQLSyntaxErrorException: ORA-00938: not enough arguments for function
  
      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
      at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
      at com.fadel.model.dao.impl.EmployeeDAOImpl.advanceSearch(EmployeeDAOImpl.java:177)
      at com.fadel.model.bo.Impl.EmployeeBOImpl.advanceSearch(EmployeeBOImpl.java:47)
      at com.fadel.model.view.backing.EmployeeController.listEmployees(EmployeeController.java:151)
      at com.fadel.model.view.backing.EmployeeController.getEmployees(EmployeeController.java:129)
      at sun.reflect.GeneratedMethodAccessor483.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at javax.el.BeanELResolver.getValue(BeanELResolver.java:88)
      at com.sun.faces.el.DemuxCompositeELResolver._getValue(Unknown Source)
      at com.sun.faces.el.DemuxCompositeELResolver.getValue(Unknown Source)
      at org.apache.el.parser.AstValue.getValue(AstValue.java:169)
      at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:190)
      at com.sun.faces.facelets.el.TagValueExpression.getValue(Unknown Source)
      at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
      at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182)
      at org.richfaces.component.UISequence.getValue(UISequence.java:180)
      at org.richfaces.component.UISequence.createExtendedDataModel(UISequence.java:114)
      at org.richfaces.component.UIDataTableBase.createExtendedDataModel(UIDataTableBase.java:194)
      at org.richfaces.component.UIDataAdaptor.getExtendedDataModel(UIDataAdaptor.java:467)
      at org.richfaces.component.UIDataAdaptor.getRowCount(UIDataAdaptor.java:518)
      at sun.reflect.GeneratedMethodAccessor414.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at javax.faces.component.UIComponentBase$AttributesMap.get(UIComponentBase.java:2387)
      at org.richfaces.DataScrollerUtils.eval(DataScrollerUtils.java:86)
      at org.richfaces.DataScrollerUtils.getRowCount(DataScrollerUtils.java:81)
      at org.richfaces.event.DataTablePreRenderListener.processEvent(DataTablePreRenderListener.java:124)
      at javax.faces.event.SystemEvent.processListener(SystemEvent.java:108)
      at javax.faces.event.ComponentSystemEvent.processListener(ComponentSystemEvent.java:118)
      at com.sun.faces.application.ApplicationImpl.processListeners(Unknown Source)
      at com.sun.faces.application.ApplicationImpl.invokeListenersFor(Unknown Source)
      at com.sun.faces.application.ApplicationImpl.publishEvent(Unknown Source)
      at com.sun.faces.application.ApplicationImpl.publishEvent(Unknown Source)
      at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:856)
      at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(Unknown Source)
      at com.sun.faces.renderkit.html_basic.GroupRenderer.encodeChildren(Unknown Source)
      at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:889)
      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1857)
      at com.sun.faces.context.PartialViewContextImpl$PhaseAwareVisitCallback.visit(Unknown Source)
      at org.richfaces.context.MetaComponentEncodingVisitCallback.visit(MetaComponentEncodingVisitCallback.java:83)
      at org.richfaces.context.BaseExtendedVisitContext.invokeVisitCallback(BaseExtendedVisitContext.java:103)
      at org.richfaces.context.ExtendedRenderVisitContext.invokeVisitCallback(ExtendedRenderVisitContext.java:65)
      at javax.faces.component.UIComponent.visitTree(UIComponent.java:1690)
      at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
      at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
      at javax.faces.component.UIForm.visitTree(UIForm.java:371)
      at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
      at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
      at com.sun.faces.context.PartialViewContextImpl.processComponents(Unknown Source)
      at com.sun.faces.context.PartialViewContextImpl.processPartial(Unknown Source)
      at org.richfaces.context.ExtendedPartialViewContext.processPartial(ExtendedPartialViewContext.java:264)
      at javax.faces.component.UIViewRoot.encodeChildren(UIViewRoot.java:1004)
      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1857)
      at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(Unknown Source)
      at com.sun.faces.application.view.MultiViewHandler.renderView(Unknown Source)
      at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
      at com.sun.faces.lifecycle.RenderResponsePhase.execute(Unknown Source)
      at com.sun.faces.lifecycle.Phase.doPhase(Unknown Source)
      at com.sun.faces.lifecycle.LifecycleImpl.render(Unknown Source)
      at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
      at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)
      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)
      at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: Error : 938, Position : 120, Sql = SELECT * FROM dummy.employee_details WHERE name = decode('','',name,'') AND fname = decode('','',fname,'') AND gender = decode(''','',gender,''') AND Age = decode(null,null,Age,null) AND salary = decode(null,null,salary,null), OriginalSql = SELECT * FROM dummy.employee_details WHERE name = decode('','',name,'') AND fname = decode('','',fname,'') AND gender = decode(''','',gender,''') AND Age = decode(null,null,Age,null) AND salary = decode(null,null,salary,null), Error Msg = ORA-00938: not enough arguments for function
  
      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
      ... 96 more
  </code></pre>