Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2745
  • Last Modified:

VB/Oracle ADO Error: -2147217900 ORA-00900 Invalid SQL statement

Hello

We have a VB project that was created by someone who is no longer with the company. His original code called an Oracle stored procedure that deleted data from Table A.

With our project expanding, a trigger has been added on the oracle side to that table. When the stored procedure deleted a record from Table A, a record from Table B also has to be deleted. The trigger utilizes the database link object. So, in the trigger, we have something like this:

Delete from Table B@dblink select * from Table A......

Now, when we run the VB project, we are receiving the below error message:
Error: -2147217900 ORA-00900 Invalid SQL statement

Somehow the dblink code in the trigger is causing this error and I am not sure why because ADO executes the stored procedure. The trigger is hanging off of the table being referenced in the stored procedure. The VB code is not passing the dblink code.

Anyone seen this before? Any thoughts would be appreciated. Thanks

Michele

0
mmemon
Asked:
mmemon
1 Solution
 
may_f_24Commented:
can you write the statement you call from VB?
from my experience, once you issue the original statement from VB, what else oracle does has no effect from where it was called
0
 
DrTechCommented:
The delete statement you have written, appears to be invalid.

It should be something like

delete from B@dblink where id in (select id from Table A)
0
 
mmemonAuthor Commented:
Sorry. My fault

The delete statement in the trigger is:

Delete from WWU.TEST@THOMAS_LK.WORLD
        Where pers_id = :OLD.pers_id;

I typed it wrong.

Michele
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
retronautCommented:
Have you isolated the problem to be with the trigger?

can you execute the oracle procedure using sql*plus?

Do the datatypes match pers_id and :OLD.pers_id?

Has the account defined database link been granted delete priviledges on the WWU.TEST table?

0
 
mmemonAuthor Commented:
Yes,

I have isolated the problem. Thank you for following up. It turned out that I had to Disable Microsoft Transaction Server on the dsn in the ODBC administrator tool. Once that was done, then my VB code executed without any problems.

I am still don't fully understand why this option caused the problem. But for now, I am happy that our app is running up to par.

Thank you.
Michele
0
 
YensidModCommented:
Question is PAQ'd and points refunded.

YensidMod
EE Moderator
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now