Escaping characters in select statement withing a cursor in Oracle

Question

I have a Function defined in a package that uses a REF CURSOR and IF ELSE logic to assign a select statement to the cursor. The Function executes with an ORA-01722: invalid number error. I believe this is due to a mistake in the escape characters:

FUNCTION getReportData(
    P_DATE_FROM IN DATE,
    P_DATE_TO IN DATE,
    PERIOD_TYPE IN INTEGER)
 
RETURN RPTTCI1328_TABLE PIPELINED IS TYPE cursorOutput IS REF CURSOR;
   
    CUR_ROW RPTTCI1328_ROW;
    cur_getByPeriodFilter cursorOutput;
    c_stmt_str VARCHAR2 (4000);
 
  BEGIN

IF PERIOD_TYPE = 1 THEN
 c_stmt_str :=
' SELECT TO_CHAR(tcis_case.offence_datetime, ''yyyy'') YEAR, tcis_case.status, COUNT(tcis_case.ticket_no) TICKET_NO, ' ||
' SUM(tcis_part_payment.AMT_ORIGINAL) ORIGINAL, ' ||
' SUM(tcis_part_payment.AMT_PAID) PAID, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''A'' THEN 1 ELSE 0 END) A, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''D'' THEN 1 ELSE 0 END) D, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''F'' THEN 1 ELSE 0 END) F, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''N'' THEN 1 ELSE 0 END) N, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''O'' THEN 1 ELSE 0 END) O, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''INF'' THEN 1 ELSE 0 END) INF, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''WFR'' THEN 1 ELSE 0 END) WFR ' ||
' FROM tcis_case ' ||
' join tcis_part_payment on tcis_case.tcis_case_id = tcis_part_payment.tcis_case_id ' ||
' join tcis_person on tcis_case.tcis_case_id = tcis_person.tcis_case_id ' ||
' where tcis_person.person_no = 1 ' ||
' AND tcis_case.unit = ''M'' ' ||
' AND tcis_case.sub_unit = ''P'' ' ||
' AND tcis_case.status IN (''P'', ''C'') ' ||
' AND (''' || P_DATE_FROM ||''' IS NULL OR  tcis_case.offence_datetime >= TO_DATE(TO_CHAR(''' || P_DATE_FROM ||''', ''yyyy''),''yyyy'')) ' ||
' AND (''' || P_DATE_TO ||''' IS NULL OR tcis_case.offence_datetime < TO_DATE(TO_CHAR(''' || P_DATE_TO ||''', ''YYYY''),''yyyy'')) ' ||
' GROUP BY to_char(tcis_case.offence_datetime, ''yyyy''), tcis_case.status '; 
END If;
DBMS_OUTPUT.put_line (c_stmt_str); 
      OPEN cur_getByPeriodFilter FOR c_stmt_str;
      LOOP
         FETCH cur_getByPeriodFilter INTO
           CUR_ROW.YEAR,
           CUR_ROW.STATUS,                                           
           CUR_ROW.TICKET_COUNT,                                            
           CUR_ROW.ORIGINAL,                       
           CUR_ROW.PAID, 
           CUR_ROW.A,
           CUR_ROW.D,
           CUR_ROW.F,                                        
           CUR_ROW.N,                       
           CUR_ROW.O,       
           CUR_ROW.INF,
           CUR_ROW.WFR;                                                    
           EXIT WHEN cur_getByPeriodFilter%NOTFOUND;
           PIPE ROW(CUR_ROW);
        END LOOP;
       CLOSE cur_getByPeriodFilter;
     END;

The command from sqlplus is:

select pkg_name.function('DD-MMM-YY','DD-MMM-YY', 1) from dual;

`AMT_ORIGINAL`and `AMT_PAID` are most probably VARCHAR2, aren't they`? Check your numeric NLS setting then. Also consider using *bind variables* in the cursor definition. — Marmite Bomber, Feb 21 '21 at 14:19

Littlefoot · Answer 1 · 2021-02-21T14:48:46.433

This looks like an excerpt of a larger "code" you composed as a string. This is usually done when there's something dynamic you're working with, such as passing table or column names as procedure's parameters. What is your reason to do that? Maybe I didn't notice it, but - I don't see anything dynamic here.

If that's so, you can skip all those single quotes and write a nice, "normal" SELECT statement, without escaping anything.

However, if you insist on it (or there's really a reason I didn't see), consider using the q-quoting mechanism. This is what you're doing now (using zillion of single quotes; difficult to read, follow and debug):

SQL> create or replace function f_test (par in varchar2)
  2    return varchar2
  3  is
  4    l_str varchar2(200);
  5    l_res varchar2(1);
  6  begin
  7    l_str := 'select ''A'' first_column from dual';
  8    execute immediate l_str into l_res;
  9    return l_res;
 10  end;
 11  /

Function created.

SQL> select f_test(null) from dual;

F_TEST(NULL)
-------------------------------------------------------------
A

SQL>

Or, you can do it this way:

SQL> create or replace function f_test (par in varchar2)
  2    return varchar2
  3  is
  4    l_str varchar2(200);
  5    l_res varchar2(1);
  6  begin
  7    l_str := q'[select 'A' first_column from dual]';        --> this
  8    execute immediate l_str into l_res;
  9    return l_res;
 10  end;
 11  /

Function created.

SQL> select f_test(null) from dual;

F_TEST(NULL)
------------------------------------------------------------
A

SQL>

So:

use the q keyword,
open a single quote
use e.g. square bracket (if you don't use it in your code; if you do, use something else)
write your query as you usually do
close the bracket
close the single quote

As simple as that.

Comment you posted says that nothing much happened (except that code is now, probably, somewhat simpler).

I don't have your data so I can't test it myself - you'll have to find the culprit yourself, there's just too much code.

Error you got says (for example) this:

SQL> select to_number('A') from dual;
select to_number('A') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

Oracle tells you the position of the error - can you find something out of it?

This was wonderful and very helpful! I had looked at the q'[]'; solution but still cannot find a way to incorporate the parameters P_DATE_TO and P_DATE_FROM as they are still not recognized. — Daniel Grindstaff, Feb 21 '21 at 11:35
Ok, got the q'[]' notation working with parameters and now I am back to the same error of ORA-01722: invalid number. So I am back where I started from. — Daniel Grindstaff, Feb 21 '21 at 12:50

score 0 · Answer 2 · answered Feb 21 '21 at 17:22

A mistake in the escape characters will lead to a problem with the OPEN of the cursor

ORA-01722: invalid number error is runtime error where you fail to convert a string to a number in the FETCH.

You may try to figure out which column it is by trial and error (replace stepwise each numeric column with null), but is is most probably AMT_ORIGINALor AMT_PAID.

If the column is VARCHAR2 and you see values such as 100,50 or 100.50 the problem is that yours NLS_NUMERIC_CHARACTERSdoes not match.

You must explicit convert the string to number with the proper setting. e.g. for 100.50

sum(to_number(AMT_PAID, '9999999D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))

Check this answer for more details.

Additional Comments

if you pass in a DATE parameter you do not need the double conversion TO_DATE(TO_CHAR( use simple the parameter
you do not need a dynamic cursor, if you would use bind variables which is recommended. See the simplified implementation below
You may want to you a dynamic cursor to get efficient execution plan for the search with and without the date range - see details here

Simplified Implementation

CREATE or replace  FUNCTION getReportData(
    P_DATE_FROM IN DATE,
    P_DATE_TO IN DATE)
 RETURN RPTTCI1328_TABLE PIPELINED IS 

   CURSOR cur_getByPeriodFilter 
   IS
   SELECT to_char(offence_datetime,'YYYY') year,
   /* process mumeric strings with decimal point e.g. 100.50 */
   sum(to_number(AMT_PAID, '9999999D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))  
   from tab
   where tab.offence_datetime >= P_DATE_FROM and tab.offence_datetime < P_DATE_TO
   group by  to_char(offence_datetime,'YYYY')
   ;   
   CUR_ROW RPTTCI1328_ROW := RPTTCI1328_ROW(null,null);
   
BEGIN
      OPEN cur_getByPeriodFilter;
      LOOP
         FETCH cur_getByPeriodFilter INTO
           CUR_ROW.YEAR,
           CUR_ROW.PAID;
           EXIT WHEN cur_getByPeriodFilter%NOTFOUND;
           PIPE ROW(CUR_ROW);
      END LOOP;        
END;
/

My Understanding is that you need a REF CURSOR to use IF ELSE... — Daniel Grindstaff, Feb 22 '21 at 01:11

Escaping characters in select statement withing a cursor in Oracle

2 Answers2