Hi.
The application I'm testing uses Delphi to run queries on Oracle DB
I'm using Oracle 12c.
The following Oracle alert log occurs:
In my opinion, there is nothing wrong with the query on Delphi.But I don't understand why Oracle marks it as PARSE ERROR.
The queries that are passed to the actual Oracle will only pass the contents of the SQL.Text variable.
If you do it like number 1, the alert log will occur, and if you modify it like number 3, the alert log will not occur.
In terms of coding, there is no problem with number 1, but I wonder why Oracle Alert Log is causing an error.
Please refer to the actual coding and alert log contents below.
----number 1 (If the actual coding error occurs in Delphi,)-----
SQL.Text := 'Select Distinct ' + #13#10 +
' Ju.JFDATE, Ju.JFJBNO, Ju.JLFCOD, Ju.JPTCNO, Ju.JPTNAM ' + #13#10 +
' , Ju.JJUBNO, Ju.JJUBGN, Ju.JJUBNF, Ju.JCHASU, Ju.JTONO1 ' + #13#10 +
' , Ju.JTONO2, Ju.JGYEAR, Ju.JHPLCE, Ju.JFPLCE, Ju.JMDRNO ' + #13#10 +
' , Pa.PAJUMN1, Pa.PAJUMN2, Pa.PAJUMNE, Pa.PAJUSEX, Pa.PABIRDT, Pa.PABIRGN ' + #13#10 +
' , Cs.CACOMNM ';
SQL.Text := SQL.Text + ////\<\<\<\<\<\<CHECK POINT1
' From JUAAAA Ju ' + #13#10 +
' Inner Join PATNAAA Pa On Ju.JLFCOD = Pa.PALFCOD ' + #13#10 +
' '; //\<\<\<\<\<\<CHECK POINT2
If Ms\_GumKd \<> '' Then
Begin
SQL.Text := SQL.Text +
' Inner Join JGKAAA Jg On Ju.JJUBNO = Jg.JGJUBNO ' + #13#10 +
' And Jg.JGGUMKD In (''A'') ' + #13#10 +
' ';
End
Else
SQL.Text := SQL.Text + ' Left Join JGKAAA Jg On Ju.JJUBNO = Jg.JGJUBNO ';
SQL.Text := SQL.Text +
' Left Join CSAAA Csrtle On Ju.JCOMNO = Cs.CACOMNO ' + #13#10 +
' Where Ju.JFDATE BetWeen :SDATE And :EDATE ';
-----------number 2 (Oracle ALERT LOG) -------------
2022-12-20T08:22:36.346916+09:00
WARNING: too many parse errors, count=100 SQL hash=0x760ea2f0
PARSE ERROR: ospid=15085, error=923 for statement:
2022-12-20T08:22:36.347396+09:00
Select Distinct
Ju.JFDATE, Ju.JFJBNO, Ju.JLFCOD, Ju.JPTCNO, Ju.JPTNAM
, Ju.JJUBNO, Ju.JJUBGN, Ju.JJUBNF, Ju.JCHASU, Ju.JTONO1
, Ju.JTONO2, Ju.JGYEAR, Ju.JHPLCE, Ju.JFPLCE, Ju.JMDRNO
, Pa.PAJUMN1, Pa.PAJUMN2, Pa.PAJUMNE, Pa.PAJUSEX, Pa.PABIRDT, Pa.PABIRGN
, Cs.CACOMNM
Additional information: hd=4fbe81b80 phd=4f86bf640 flg=0x28 cisid=960 sid=960 ciuid=960 uid=960
-------number 3 (Modify Delphi Query) --------
SQL.Text := 'Select Distinct ' + #13#10 +
' Ju.JFDATE, Ju.JFJBNO, Ju.JLFCOD, Ju.JPTCNO, Ju.JPTNAM ' + #13#10 +
' , Ju.JJUBNO, Ju.JJUBGN, Ju.JJUBNF, Ju.JCHASU, Ju.JTONO1 ' + #13#10 +
' , Ju.JTONO2, Ju.JGYEAR, Ju.JHPLCE, Ju.JFPLCE, Ju.JMDRNO ' + #13#10 +
' , Pa.PAJUMN1, Pa.PAJUMN2, Pa.PAJUMNE, Pa.PAJUSEX, Pa.PABIRDT, Pa.PABIRGN ' + #13#10 +
' , Cs.CACOMNM From JUAAAA Ju ' + #13#10 +
' Inner Join PATNAAA Pa On Ju.JLFCOD = Pa.PALFCOD ';
If Ms\_GumKd \<> '' Then
Begin
SQL.Text := SQL.Text +
' Inner Join JGKAAA Jg On Ju.JJUBNO = Jg.JGJUBNO ' + #13#10 +
' And Jg.JGGUMKD In (''A'') ' + #13#10 +
' ';
End
Else
SQL.Text := SQL.Text + ' Left Join JGKAAA Jg On Ju.JJUBNO = Jg.JGJUBNO ';
SQL.Text := SQL.Text +
' Left Join CSAAA Csrtle On Ju.JCOMNO = Cs.CACOMNO ' + #13#10 +
' Where Ju.JFDATE BetWeen :SDATE And :EDATE ';