Print Thread
TOracleQuery giving ORA-00911 Invalid Character
#49046 05/22/14 04:04 PM
Joined: Sep 2010
Posts: 2
Colorado, USA
M
m&m Offline OP
Member
OP Offline
Member
M
Joined: Sep 2010
Posts: 2
Colorado, USA
Hello

I have a TOracleQuery Component in Delphi defined as below

object qryMakeInVisible: TOracleQuery
SQL.Strings = (

'Insert into LostApplicants ( Cycle_Year, Applicant_ID, Applicant_SSN )'
'values ( :CYCLEYEAR, :APPLICANTID, :APPLICANTSSN );'
''
'Commit;'
''
'Update Temp_d'
'Set Visible = 0'
'Where Temp_d.Applicant_ID = :APPLICANTID;'
''
'Commit;')
Session = OracleSession1
Debug = True
Variables.Data = {
03000000030000000A0000003A4359434C455945415205000000000000000000
00000C0000003A4150504C4943414E5449440300000000000000000000000D00
00003A4150504C4943414E5453534E050000000000000000000000}
Left = 600
Top = 292
end



The Code that I am using to execute the Query is as follows



begin

{ Make InVisible }

Application.ProcessMessages;

with dmLost_Applicants.dmLostApplicants.qryMakeInVisible do
begin

SetVariable( 'CYCLEYEAR', dbgLostApplicationList.DataSource.DataSet.Fields[5].Value );
SetVariable( 'APPLICANTID', dbgLostApplicationList.DataSource.DataSet.Fields[0].Value );
SetVariable( 'APPLICANTSSN', dbgLostApplicationList.DataSource.DataSet.Fields[1].Value );

sql.SaveToFile( 'MakeInvisibleQuery' );

Execute;

end;

end;


The SaveToFile gives the following code


'Insert into LostApplicants ( Cycle_Year, Applicant_ID, Applicant_SSN )'#$D#$A'values ( :CYCLEYEAR, :APPLICANTID, :APPLICANTSSN );'#$D#$A#$D#$A'Commit;'#$D#$A#$D#$A'Update Temp_d'#$D#$A'Set Visible = 0'#$D#$A'Where Temp_d.Applicant_ID = :APPLICANTID;'#$D#$A#$D#$A'Commit;'#$D#$A


When I run the program in the debugger I can see the correct values of the data in the SetVariable lines of code
and when I turn on qryMakeInVisible.Debug it produces the follow code in the popup.


Insert into LostApplicants ( Cycle_Year, Applicant_ID, Applicant_SSN )
values ( :CYCLEYEAR, :APPLICANTID, :APPLICANTSSN );

Commit;

Update Temp_d
Set Visible = 0
Where Temp_d.Applicant_ID = :APPLICANTID;

Commit;

:CYCLEYEAR = 2013
:APPLICANTID = 111222333
:APPLICANTSSN = 444556666


Everthing looks good to me, but it is giving an "ORA-00911 invalid character" error message --- What Gives


Any help would be appreciated.

Mark Moss



Re: TOracleQuery giving ORA-00911 Invalid Character
m&m #49051 05/23/14 09:45 AM
Joined: Aug 1999
Posts: 22,173
Member
Offline
Member
Joined: Aug 1999
Posts: 22,173
If you want to execute multiple SQL statements at once you have to pass it as PL/SQL in an anonymous PL/SQL Block by surrounding the statements with a begin / end pair:

Code
begin

  Insert into LostApplicants ( Cycle_Year, Applicant_ID, Applicant_SSN )'
  values ( :CYCLEYEAR, :APPLICANTID, :APPLICANTSSN );

  Commit;

  Update Temp_d
  Set Visible = 0
  Where Temp_d.Applicant_ID = :APPLICANTID;

  Commit;

end;
The invalid character that Oracle complains about is the semi-colon at the end of the first statement.

Last edited by Marco Kalter; 05/23/14 09:45 AM.

Marco Kalter
Allround Automations
Re: TOracleQuery giving ORA-00911 Invalid Character
Marco Kalter #49053 05/23/14 03:21 PM
Joined: Sep 2010
Posts: 2
Colorado, USA
M
m&m Offline OP
Member
OP Offline
Member
M
Joined: Sep 2010
Posts: 2
Colorado, USA
Marco

First I want to thank you for your reply, and second what about the other semi-colon's?

Are they in error also?

Mark Moss

Re: TOracleQuery giving ORA-00911 Invalid Character
m&m #49055 05/26/14 08:54 AM
Joined: Aug 1999
Posts: 22,173
Member
Offline
Member
Joined: Aug 1999
Posts: 22,173
For a single SQL statement you cannot use a semi-colon to terminate it.

For a PL/SQL Block with multiple SQL statements you need to terminate each SQL statement in the block with a semi-colon, as well as the "end;" of the PL/SQL Block itself.


Marco Kalter
Allround Automations

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.026s Queries: 15 (0.006s) Memory: 2.5118 MB (Peak: 3.0397 MB) Data Comp: Off Server Time: 2024-03-29 05:35:57 UTC
Valid HTML 5 and Valid CSS