?
Solved

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

Posted on 2003-03-17
6
Medium Priority
?
2,682 Views
Last Modified: 2007-12-19
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
Comment
Question by:mmemon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:may_f_24
ID: 8157297
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
 
LVL 6

Expert Comment

by:DrTech
ID: 8157665
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
 

Author Comment

by:mmemon
ID: 8159150
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Expert Comment

by:retronaut
ID: 8161104
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
 

Accepted Solution

by:
mmemon earned 0 total points
ID: 8161151
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
 

Expert Comment

by:YensidMod
ID: 8906675
Question is PAQ'd and points refunded.

YensidMod
EE Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question