Forum Discussion

RKDY91_1's avatar
RKDY91_1
New Contributor
13 years ago

ORA-00911: invalid character when trying to execute an insert statement on an oracle db

I have a script which returns when running a select statement but when trying to do an insert i get an "ORA-00911: invalid character" error .



when I try to execute the same on sql developer the statement executes just fine. 



I tried everything on all the forums.



below is what I am using 



/below is for select statment it works great 




set qry = ado.CreateADOQuery


qry.ConnectionString = "Provider=OraOLEDB.Oracle.1" 


 


qry.SQL = "SELECT * FROM ppd where NBR = "XYR"


qry.Open


qry.First



but when trying to run an insert statement  like below 

query = "insert into mguser.psa (activity_date,activity_time)values('xyx','xyx');commit;)"

tried using 

qry.open

qry.execsql

qry.Execute(query)



I keep  getting ORA-00911



any help would be greatly appreciated!



Thanks,

Tony



 

2 Replies

  • RKDY91_1's avatar
    RKDY91_1
    New Contributor
    13 years ago
    I found the solution by trying various options.



    here is the solution



    execute the insert in parts



    first execute the insert without semi colon 



    then execute the commit statement



    qry.execute(insert into mguser.psa (activity_date,activity_time)values('xyx','xyx'))



    qry.execute("commit")



    this is strange but does the work for me.



    Thanks,

    Tony











  • AlexKaras's avatar
    AlexKaras
    Community Hero
    13 years ago
    Hi Tony,



    Sorry for not replying earlier... but this is the expected behaviour. The case is that the native ADO (and thus the ADO wrapper object in TestComplete) does not support batch SQL statements. ADO requires such statements to be executed line-by-line, exactly like you've found out.