Oracle SQL Select Query Not Working in VB.NET Works Fine in SQL +

Question

When my query executes within my VB application I am receiving the following error:

ORA-00942: table or view does not exist

All table names in my query are spelt correctly, and do in fact exist.

If I dump the query from my VB.net app and then run the query manually in Oracle SQL Plus it executes just fine.

In both cases I am logged in with the exact same credentials and have selected the same database. For my connection within visual basic I am using OleDb.

From within my visual basic application I also ran a query to dump all tables which the user has access to using

select table_name from all_tables

And the table names which I am querying show up.

Any idea what would be causing this?

SELECT   
    RSS.STEP_STATUS_DATE,                                          
    RSS.VALUE_RECORDED                                    
FROM     
    ITR,                                                           
    REPORT R,                                                      
    INSTRUCTION I,                                                 
    INSTRUCTION_STEP INS,                                          
    REPORT_STEP RS,                                                
    REPORT_STEP_STATUS RSS                                
WHERE    
    ITR.ITR_NO = '1' AND                                         
    I.INSTRUCTION_ID = '12345' AND                                   
    INS.STEP_NO = '2' AND                                        
    R.INSTRUCTION_ID = I.INSTRUCTION_ID AND                        
    RS.REPORT_ID = R.REPORT_ID AND                                 
    RS.INSTRUCTION_STEP_ID = INS.INSTRUCTION_STEP_ID AND           
    RSS.REPORT_STEP_ID = RS.REPORT_STEP_ID AND                     
    RSS.MEASUREMENT_NAME = 'ESN'

My visual basic code is as follows:

strQuery =  "SELECT   RSS.STEP_STATUS_DATE,                                 " +
             "         RSS.VALUE_RECORDED                                    " +
             "FROM     ITR,                                                  " +
             "         REPORT R,                                             " +
             "         INSTRUCTION I,                                        " +
             "         INSTRUCTION_STEP INS,                                 " +
             "         REPORT_STEP RS,                                       " +
             "         REPORT_STEP_STATUS RSS                                " +
             "WHERE    ITR.ITR_NO = '%01' AND                                " +
             "         I.INSTRUCTION_ID = '%02' AND                          " +
             "         INS.STEP_NO = '%03' AND                               " +
             "         R.INSTRUCTION_ID = I.INSTRUCTION_ID AND               " +
             "         RS.REPORT_ID = R.REPORT_ID AND                        " +
             "         RS.INSTRUCTION_STEP_ID = INS.INSTRUCTION_STEP_ID AND  " +
             "         RSS.REPORT_STEP_ID = RS.REPORT_STEP_ID AND            " +
             "         RSS.MEASUREMENT_NAME = '%04'"

Just for fun, can you change the query to have the table name in uppercase? (Not necessarily quoted - I suspect the driver is adding quotes and making it invalid) — Alex Poole, Jun 20 '16 at 16:45
@AlexPoole I tried converting everything to uppercase and the issue still remains — Belgin Fish, Jun 20 '16 at 16:50
Well either you are logged in as a different user, or your query is wrong, or there is some bad synonym. — OldProgrammer, Jun 20 '16 at 16:55
@OldProgrammer I am logged in as the same user on Oracle SQL + and the exact same query executes just fine. — Belgin Fish, Jun 20 '16 at 16:56
@BelginFish - can't see anything wrong. I would use an alias on ITR, but I doubt it has anything to do with your error — vercelli, Jun 20 '16 at 16:57
@vercelli yeah I tried aliasing on ITR and it didn't make a difference unfortunately — Belgin Fish, Jun 20 '16 at 17:00
How about (a) prefixing each table with the schema name (also uppercase); or (b) quoting the table names? Clutching at straws a bit but think I've seen something vaguely similar before. — Alex Poole, Jun 20 '16 at 17:05
If I recall it right (at least in the old days of visual basic) to concatenate a string in another line you should end it with an underline or `&` don't recall something like `+ _&` or `+ _` but I'm sure it wasn't just a plus sign — Jorge Campos, Jun 20 '16 at 17:27
Found it: http://stackoverflow.com/a/706920/460557 it is `& _` — Jorge Campos, Jun 20 '16 at 17:29
@JorgeCampos would this be required as a newline isn't necessary in order for the query to execute, if it all concatenates to one single line it should still work fine no? — Belgin Fish, Jun 20 '16 at 17:31
Can you cut down the query to see if it is a particular table causing the error? (And you should be using `&` to concatenate strings as that is the string concatenation operator. Jorge's comment does not imply adding newlines to the concatenated string, although the underscore is not needed in recent versions of VS.) — Andrew Morton, Jun 20 '16 at 18:01
@JorgeCampos VB has supported + for string concatenation ever since appending .Net to the name. — Joel Coehoorn, Jun 20 '16 at 18:10
If you execute `select user from dual` from both your SQL client and from the code. Do they both return the same value? — sstan, Jun 20 '16 at 18:20
@JoelCoehoorn Thanks for the update. Last time I programmed in visual basic it was rule. It came up to mind the moment he updated the code. :) — Jorge Campos, Jun 20 '16 at 19:57
Make a `Debug.Print(strQuery)` right before you execute and verify the printed query. And you really should use parametrized queries. — Wernfried Domscheit, Jun 21 '16 at 05:58

score 0 · Answer 1 · answered Jun 20 '16 at 18:05

0

I'm looking at this portion (and others like it):

RSS.MEASUREMENT_NAME = '%04'

I expect you meant to do this:

RSS.MEASUREMENT_NAME LIKE '%04'

While I'm here, almost no one uses the "A,B" join syntax any more. It's extremely out-dated, and can lead to errors where join conditions are applied to the wrong tables, such that the query doesn't return any results... in other words, it might even be causing the problem you have right now.

answered Jun 20 '16 at 18:05

Joel Coehoorn

399,467
113
570
794

I have a function to replace %01, %02, etc... with an array of supplied values, that's why this is there. If this was the cause of the issue wouldn't it also trigger the error when running the query from oracle sql + ? – Belgin Fish Jun 20 '16 at 18:26
That's even worse. This will leave you horribly vulnerable to sql injection attacks. – Joel Coehoorn Jun 20 '16 at 18:28
You cant say it's horribly worse without seeing my replacement function first – Belgin Fish Jun 20 '16 at 18:59
If you're not using parameterized queries, you're not doing it right. It's the difference between attempting to sanitize an input and quarantining the input. Only one of those is proper practice. – Joel Coehoorn Jun 20 '16 at 19:01
This could be the point of fail. What would happen with your query if one of the parameter has a `'` (quote) on it? Is your replace function checking that? – Jorge Campos Jun 20 '16 at 19:59
It's only integers being passed in, there is no chance of a quote – Belgin Fish Jun 20 '16 at 20:11
Why do you put them into quotes in this case? – Wernfried Domscheit Jun 20 '16 at 20:16
Integer values should not have quotes around them. **Use real parameterized queries**, and you'll fix this problem. – Joel Coehoorn Jun 20 '16 at 20:54

Oracle SQL Select Query Not Working in VB.NET Works Fine in SQL +

1 Answers1