ORA-02019 on String Concatenation SQL from C# using Oracle.DataAccess.Client

Question

I have some code that generates an SQL query based on several values. The end result requires calling two databases to create a single DataTable. The second database is connected by a database link we use all the time. The C# itself is solid and the end result query string is this:

SELECT distinct HOME.MISC.PROCEDURE(H.WIDGET, H.FIDGET),
  H.WIDGET ,
  (SELECT WIDGETYPE
  FROM VISITOR.SOMETABLE@DEVSERV   --here is where it all goes bad, bad bad. 
  WHERE UPPER(WIDGET_SN) = UPPER(H.WIDGET)
  ) ,                                     
  H.FIDGET,                           
  HOME.STUFF.ANOTHERPROCEDURE(H.WIDGET)
FROM HOME.WIDGET_HEAP H
INNER JOIN HOME.WIDGET_LIST L
ON H.WIDGET         = L.WIDGET_ID
WHERE UPPER(L.WIDGET_ID ) = UPPER('1337-H4X')

Obviously these aren't real field names or values, but you get the point. When I lift the query from the variable itself at a breakpoint, I can paste the SQL into SQL Developer and it works just fine. When I run the query and try to populate my DataTable in my program, I get:

ORA-02019: connection description for remote database not found;

The username/TNS/etc etc. is all set properly, as we have many programs that use the same information to make their connections. Am I doing something wrong here on using the link? I've searched Google and here and found many references to the error, but nothing quite like this situation.

unleashed · Accepted Answer · 2017-03-03 20:07:36Z

2

Since you can run it in SQLDeveloper just fine, it sounds like an issue with the visibility of the db_link. Is that link a public database link? If so, all users would have access. If not, then only the user you logged into SQLDeveloper would have access to it. If this is the case, is the app user the same user? If it is not, then it does not have access to it. The key is in the error description, connection description not found. Make sure your app has visibility to this link.

answered Mar 3, 2017 at 20:07

unleashed

7713 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CDove Over a year ago

This was the case. The other applications consuming the same data apparently had the correct login hard-coded(!) and used it when they needed elevated permissions, keeping it out of the config files entirely. I changed this in those apps to use an imported string, encrypted it, and gave the applications access to an "Elevated" login when necessary.

Collectives™ on Stack Overflow

ORA-02019 on String Concatenation SQL from C# using Oracle.DataAccess.Client

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related