why oracle user defined function shows error in Oracle Sql Developer 4.0.3.16 -


in oracle sql developer version 1.5.4, same code doesn't give error. connected oracle 11g database server.

create or replace function getdate return varchar2 v_datevalue date; begin v_datevalue := '01-apr-2015'; return (cast(v_datevalue varchar2)); end; /

enter image description here

v_datevalue date; begin v_datevalue := '01-apr-2015';

this bug in code. never ever depend on implicit data type conversion. issue not tool fact assigning string date data type.

you might lucky see implicit conversion without error, if , if locale-specific nls_date_format same have used in literal.

it compiled why mark error in worksheet

the error @ run time , not @ compile time. data type conversion happens @ run time , not @ compile time. so, function compile successfully, however, error out when execute it.

root cause

sql> alter session set nls_date_format='mm/dd/yyyy';  session altered.  sql> sql> create or replace   2    function getdate   3      return varchar2   4      5      v_datevalue date;   6    begin   7      v_datevalue := '01-apr-2015';   8      return (cast(v_datevalue varchar2));   9    end;  10    /  function created.  sql> sql> select getdate dual; select getdate dual        * error @ line 1: ora-01858: non-numeric character found numeric expected ora-06512: @ "lalit.getdate", line 6   sql>

so, happened? changing nls_date_format shows bug in code.

implicit date conversion

sql> alter session set nls_date_format='dd-mon-yyyy';  session altered.  sql> sql> select getdate dual;  getdate ----------------------------------------------------- 01-apr-2015  sql>

now, same format of literal, code managed give output because oracle has done implicit data type conversion.

fix

always use to_date explicitly convert literal date. never depend on implicit conversion. to_date @ individual sql statement level, override nls settings of client tool.

sql> alter session set nls_date_format='mm/dd/yyyy';  session altered.  sql> sql> create or replace   2    function getdate   3      return varchar2   4      5      v_datevalue date;   6    begin   7      v_datevalue := to_date('01-apr-2015','dd-mon-yyyy');   8      return (cast(v_datevalue varchar2));   9    end;  10    /  function created.  sql> sql> select getdate dual;  getdate ------------------------------------------------------------- 04/01/2015  sql>

Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more