dbcodeio / public

The Power of Databases, The Convenience of VS Code: All in One Place
15 stars 0 forks source link

Oracle Introspection Error #58

Open sgpeter1 opened 2 weeks ago

sgpeter1 commented 2 weeks ago

Getting an error during introspection. My guess is I don't have access to the tables/views that are being queried.

Error: ORA-00923: FROM keyword not found where expected

VSCode Version: 1.94.2 Extension Version: 1.3.1 OS: win32 Arch: x64 Database Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0 - 19.19.0.0.0

Not sure if this is helpful, but when looking for columns, views, tables, etc., I generally use all_objects and all_tab_columns in Oracle.

mikeburgh commented 2 weeks ago

So multiple problems.. I tested everything against 23, but used docs from older versions for functions that I thought might not be there (JSON_OBJECT for example) but turns out.. true/false datatype was not in older versions, or the ability to do selects without tables...

Sorted that out, should be good now if you want to give the version I just pushed a test (1.3.2)

The code at the moment uses the userx set.. I started with all but it was massive (145k standard system columns) which made it very slow.. so for now it uses the user_ ones and only in the schema you connect to (user) I might need to change that pending feedback if the other schemas are important ?

sgpeter1 commented 2 weeks ago

Hello!

Getting this error now:

Error getting databases for connection EDW, error: ORA-00942: table or view does not exist

It's very common to need access to non-user schemas. Things like IT, HR, MARKET, whatever.... schemas manifest as such. Individual users at big enterprises probably have very little in their personal schemas.

Thanks!

mikeburgh commented 2 weeks ago

I think I can guess what that is.. I assume this gives you the same error:

select ora_database_name , ( select sum(bytes) from dba_data_files) as bytes from dual;

I was trying to get the size of the DB, but realize you wont have permissions most likely to dba_x

Ahh, okay.. so schemas are sort of used liked databases since it's single DB model.. Okay I think I recall somewhere about being able to identify System/Oracle created objects, if I filter those out by default I might be able to get all the schemas without the performance hit, and without needing to re work the driver model at this stage (it's planned, but not yet)

mikeburgh commented 2 weeks ago

Okay 1.3.3 should be out now.. should give you all the schemas that you have access to, excluding the oracle managed ones..

Hopefully this is the one!

sgpeter1 commented 1 week ago

Now I'm seeing this error:

Error: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 7 ORA-06512: at line 19

Oracle is your favorite, I'm certain. :) I love it for certain features and functions, but I feel like troubleshooting and debugging is harder than other SQL platforms.

mikeburgh commented 1 week ago

Yeah.. same.. has some really great features, but it's a beast.. took me ages to get it running and to be able to even connect to it in a docker container!

I do like how the driver gives back errors with URLS in a lot of cases to the docs.. I am working on getting those to be hot linked automatically.

Hopefully the release I just pushed 1.3.6 gets this issue solved.