I couldn't detect the character matching error I got in the pl sql block below-CodePudding

DECLARE
D            VARCHAR2(20):='''HH24:MI:SS''';
G            VARCHAR2(20):='''MM''';
E            VARCHAR2(20):='''06:00:00''';
F            VARCHAR2(20):='''23:59:59''';
T            VARCHAR2(200):='''YYYY/MM/DD HH24:MI:SS''';
A            VARCHAR2(200);
B            VARCHAR2(200);
L_BODY       CLOB;
TYPE resp_time IS RECORD 
              ( servis            varchar2(200),
                operasyon         varchar2(200),
                total             varchar2(200),
                avg_brt           varchar2(200),
                month_            varchar2(200)
                );
TYPE resp_time2 IS TABLE OF resp_time INDEX BY PLS_INTEGER;
resp_time3 resp_time2;
TYPE l_cursor is  REF CURSOR;
 c l_cursor;
BEGIN
SELECT CHR(39)||to_char(trunc(sysdate-1),'YYYY/MM/DD hh24:mi:ss')||CHR(39) into A FROM dual;
SELECT CHR(39)||to_char(trunc(sysdate-1),'YYYY/MM')||'/23 00:00:00'||CHR(39) into B FROM dual;
OPEN C FOR 'SELECT  * FROM (  SELECT  service ,
                                       CASE
                                       WHEN operation is null
                                       THEN
                                       ''operasyon_yok''
                                       ELSE
                                       operation
                                       END operas,
                                    COUNT (1),
                                    ROUND (AVG (brt)) avg_,
                                    to_char(datetime, '||G||')
                             FROM SERVICELOG
                             WHERE  TO_CHAR(datetime, '||T||') BETWEEN  
to_date('||B||','||T||') AND to_date('||A||','||T||')
                             AND    TO_CHAR(datetime, '||D||') BETWEEN ('||E||') AND ('||F||')
                             GROUP BY service, operation, to_char(datetime, '||G||'))
            WHERE avg_ >= 3000';
FETCH C BULK COLLECT INTO resp_time3;
CLOSE C;

    l_body:=chr(12)||  chr(12)||'SERVICE REPONSE TIME'||chr(12)||  chr(12);   
    l_body:=l_body||'</TABLE>'||  chr(12);
    l_body:=l_body||'<TABLE BORDER=1 BGCOLOR="#000000">'; 
    l_body:=l_body||'<TR BGCOLOR="#FFFFFF">'; 
    l_body:=l_body||'<TH>servis';
    l_body:=l_body||'<TH>operasyon';
    l_body:=l_body||'<TH>total';
    l_body:=l_body||'<TH>avg_brt';
    l_body:=l_body||'<TH>month_';

FOR indx in 1 .. resp_time3.count
LOOP
    l_body:=l_body||'<TR>';
    l_body:=l_body||'<TD>'||resp_time3(indx).servis||'</TD>';
    l_body:=l_body||'<TD>'||resp_time3(indx).operasyon||'</TD>';
    l_body:=l_body||'<TD>'||resp_time3(indx).total||'</TD>';
    l_body:=l_body||'<TD>'||resp_time3(indx).avg_brt||'</TD>';
    l_body:=l_body||'<TD>'||resp_time3(indx).month_||'</TD>';

END LOOP;

END;

out put as below;

Error report -
ORA-12801: error signaled in parallel query server P039, instance mwlogdbp02:mwlogdb1 (1)
ORA-01861: literal does not match format string
ORA-06512: at line 40
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.

CodePudding user response：

It's very simple, but it was solved when I corrected the "datetime" I'm amazed how it escaped my eye.

CodePudding user response：

Error indicates it's a quote mess. I'd rewrite that code with 2 changes:

Use bind variables, not concatenation.
Use a cursor for loop so you don't need to quote the query statement.

For you code this would be:

DECLARE
  D            VARCHAR2(20):='HH24:MI:SS';
  G            VARCHAR2(20):='MM';
  E            VARCHAR2(20):='06:00:00';
  F            VARCHAR2(20):='23:59:59';
  T            VARCHAR2(200):='YYYY/MM/DD HH24:MI:SS';
  A            DATE;
  B            DATE;
  L_BODY       CLOB;
BEGIN
  a := TO_DATE('23-'||TO_CHAR(TRUNC(sysdate),'MON-YYYY'),'DD-MON-YYYY');
  b := TRUNC(SYSDATE-1);

  l_body:=chr(12)||  chr(12)||'SERVICE REPONSE TIME'||chr(12)||  chr(12);   
  l_body:=l_body||'</TABLE>'||  chr(12);
  l_body:=l_body||'<TABLE BORDER=1 BGCOLOR="#000000">'; 
  l_body:=l_body||'<TR BGCOLOR="#FFFFFF">'; 
  l_body:=l_body||'<TH>servis';
  l_body:=l_body||'<TH>operasyon';
  l_body:=l_body||'<TH>total';
  l_body:=l_body||'<TH>avg_brt';
  l_body:=l_body||'<TH>month_';
    
  FOR r IN 
  (
  SELECT  * FROM (  SELECT  service as servis,
                           CASE
                           WHEN operation is null
                           THEN
                           'operasyon_yok'
                           ELSE
                           operation
                           END as operasyon,
                        COUNT (1) as total,
                        ROUND (AVG (brt)) as avg_brt,
                        to_char(datetime, g) as month_
                 FROM SERVICELOG
                 WHERE  TO_CHAR(datetime, t) BETWEEN a AND b
                 AND    TO_CHAR(datetime, d) BETWEEN e AND f
                 GROUP BY service, operation, to_char(datetime, g))
              WHERE avg_ >= 3000
  ) LOOP
      l_body:=l_body||'<TR>';
      l_body:=l_body||'<TD>'||r.servis||'</TD>';
      l_body:=l_body||'<TD>'||r.operasyon||'</TD>';
      l_body:=l_body||'<TD>'||r.total||'</TD>';
      l_body:=l_body||'<TD>'||r.avg_brt||'</TD>';
      l_body:=l_body||'<TD>'||r.month_||'</TD>';
  END LOOP;

END;

I don't have access to your data model but this should work.

To summarize, here is an example of how to use bind variables for the format:

declare
  l_fmt VARCHAR2(100) := 'DD-MON-YYYY';
begin
  -- bad practice
  dbms_output.put_line(TO_CHAR(SYSDATE,''||l_fmt||''));
  -- good practice
  dbms_output.put_line(TO_CHAR(SYSDATE,l_fmt));
end;

If you really want to escape quotes, then use q-quote syntax instead of escaping quotes with quotes: Example:

declare
  l_fmt1 VARCHAR2(100) := 'DD-MON-YYYY';
  l_fmt2 VARCHAR2(100) := 'DD-MON-YYYY HH24:MI:SS';
begin
  FOR r IN 
  (
    SELECT q'!I'd love to 'quote'!' s, TO_CHAR(SYSDATE   LEVEL,l_fmt1) as date1, TO_CHAR(SYSDATE   LEVEL,l_fmt2) as date2
      FROM DUAL connect by LEVEL < 5
  ) LOOP
    dbms_output.put_line('s: '||r.s||', date1: '||r.date1||', date2: '||r.date2);
  END LOOP;
end;