use the same date format in syds, syde as you then use in the d1, d2 'to_date' statements i am didn't get actully iam using same date format right, SYDS:='0602'||SYO ĭ2:=to_date(SYDE,'MM/DD/YYYY') You first seem to have decided to use a MMDDYYYY format for SYDS and SYDE, but when you set the values for d1 and d2 you told to_date that they were in a MM/DD/YYY format.ĭ1 := TO_DATE('2-jun-' || TO_CHAR(SYSDATEĭ2 := TO_DATE('2-sep-' || TO_CHAR(SYSDATEĭbms_output.put_line('a=' || a || ',b=' || b) ĮND (I had to use the WITH clause to make for my not having a CROP_SALE table). Try this:Įxecute immediate 'select to_char(DATE_OF_JOIN,''YY''),Īnd to_char(DATE_OF_JOIN,''YYYY'')=to_char(sysdate,''YYYY'')Īnd to_date(to_char(DATE_OF_JOIN),''MM/DD/YYYY'') > :B1Īnd to_date(DATE_OF_JOIN,''MM/DD/YYYY'') < :B2Įnd Edit: Fixed the to_date function that was operating on a date. Or even better, use bind variables like this: The ORA-01858 can be considered as a warning to users are jumping between various tables and data sets at a quick pace and are letting the formatting concerns slip through the cracks. You need to either concatenate the values like this: Despite the needs of the program, it would be impossible for any one individual to strictly adhere to the parameters of Oracle on every occasion. Second, in the execute immediate statement you are putting in the text "d1" and "d2", not the values of d1 and d2. ORA-01858: Ein nicht numerisches Zeichen, das dort gefunden wurde, wo eine Ziffer erwartet wurde - sql, oracle, ora-01858 Orakel Unterschied zwischen zwei Daten - SQL, Oracle SonarQube. First, fix the date format to remove the slashes (since the source has no slashes). There are two types of errors that I see.
Oracle apex sql error 01858 how to#
In this the error is coming in dates d1&d2 referring but i didn't get how to slove it. Select to_char(sysdate,'MM'),to_char(sysdate,'DD'),to_char(sysdate,'YYYY') into SMO,SDO,SYO from dual Įxecute immediate 'select ||to_char(DATE_OF_JOIN,''YY''),COUNT(TOTAL_NO_STUDENTS) from CROP_SALE where class=7 and to_char(DATE_OF_JOIN,''YYYY'')=to_char(sysdate,''YYYY'') and to_date(to_char(DATE_OF_JOIN),''MM/DD/YYYY'') > to_date(''d1'',''MM/DD/YYYY'') and to_date(DATE_OF_JOIN,''MM/DD/YYYY'') < to_date(''d2'',''MM/DD/YYYY'') group by to_char(DATE_OF_JOIN,''YY'')' into a,b There are two types of errors that I see. Like trying to get the no of students joined in between particular dates. I followed the above method again to turn debug and trace on for my own session - so even when you are in the APEX Builder you can see what APEX is doing behind the scenes.
I tried a query while dealing with dates i got error but i didn't get how to avoid it. I was testing our APEX Office Print plugin in APEX 18.1 and for some reason APEX was behaving differently than earlier versions, but I didn't understand why.