Oracle database not opening up properly

[dartey25]

Would be awesome to have oracle database analized properly on connection. Ex. getting all the tables, packages, procedures, etc.
Right now I'm just getting list of other users in database under the "schemas" dir

P.S. Otherwise, I love it! Would be really happy to be able to use it working with oracle though

[dbmatheus]

Hi, I fixed this behavior changing the code of vim-dadbod-ui/autoload/db_ui/schemas.vim, commenting out the following:
"if get(g:, 'dbext_default_ORA_bin') == 'sql' " let s:oracle.parse_results = {results, min_len -> s:results_parser(s:strip_quotes(results[13:-5]), ',', min_len)} " let s:oracle.parse_virtual_results = {results, min_len -> s:results_parser(s:strip_quotes(results[13:-4]), ',', min_len)} "endif

[dartey25]

Hi, I fixed this behavior changing the code of vim-dadbod-ui/autoload/db_ui/schemas.vim, commenting out the following: "if get(g:, 'dbext_default_ORA_bin') == 'sql' " let s:oracle.parse_results = {results, min_len -> s:results_parser(s:strip_quotes(results[13:-5]), ',', min_len)} " let s:oracle.parse_virtual_results = {results, min_len -> s:results_parser(s:strip_quotes(results[13:-4]), ',', min_len)} "endif

still the same issue happening when instead of packages, procedures, functions, tables etc - I get only listed db users under schemas (screenshot below)

P.S. Or maybe I'm doing something wrong here or don't understand something. I'm pretty new to this

[dbmatheus]

Hi @dartey25, I'm pretty new to this also :D Well, yesterday just figured out a better solution for this, install sqlcl from Oracle (it will need Java), put in your path and use the original code of the plugin. It works out of the box and does a much better format on the query output. Don't forget to define the new oracle binary with this variable (I use init.lua):

vim.g.dbext_default_ORA_bin = "sql"

[nlinaje]

I had the same problem and after some debugging I got to a simple solution.

This if statement does not correctly work in case the global variable is not set. No matter which word you compare with ("sql", "sqlplus", "bar", "foo"...), it will always be true and will execute the code inside the if statement.
if get(g:, 'dbext_default_ORA_bin') == 'sql'

As I don't want to install additionaly SW, I changed the statement to return empty string in case the variable is not set:
if get(g:, 'dbext_default_ORA_bin', '' ) == 'sql'

In my debugging, I saw that this global variable was not set. It is strange, because it should be set by the dadbod oracle adapter to sqlplus, and in fact, it really uses sqlplus to query the database. I guess more debugging is needed there.

After this change, it still does not work correctly for me. Following change does it. Inside the s:oracle variable, change the line:
'parse_results': {results, min_len -> s:results_parser(results[15:-5], '\s\s\+', min_len)},
the slice [15, -5] with [13:-3]:
'parse_results': {results, min_len -> s:results_parser(results[13:-3], '\s\s\+', min_len)},

This slice removes some lines from the output of sqlplus (at the beginning and at the end), so that only the schemas and tables are extracted.

I am using Oracle Instant Client 12.2, so I guess that using a newer version will work with [15:-5].

I will check if I am granted to install a newer version of the oracle instant client and check it again.

[kristijanhusak]

@nlinaje I pushed a fix for first issue to master 49dabb8, but for the 2nd option I'm not sure if I should change that.

This slice removes some lines from the output of sqlplus (at the beginning and at the end), so that only the schemas and tables are extracted.

Are these some warnings or?

[nlinaje]

@kristijanhusak
These are not warnings but information about sqlplus (Version, copyright...)
The first lines to remove are (empty lines not shown here):

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 20 05:11:57 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon May 20 2024 05:08:28 +00:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

And at the end, a disconnection message:

Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

I made some tests using VMs where I installed the last oracle instant client sqlplus package (Version 23.4). The values in the slice must be different to extract the information correctly:

• Version 12: [13:-3] (or [13:-2] difference is just an empty line which is automatically removed in plugin)
• Version 23: [14:-3] (or [14:-2])
• Currently in plugin set to: [15:-5]

The plugin should not have to deal with sqplus versions. The solutions is not in this plugin, but in dadbod plugin. The adapter is calling sqlplus as sqlplus -L. Changing it to sqlplus -L -S solves the problem because header and footer are not shown. With this change, the slice would be [3:]. This works at least for version 12 and 23 of sqlplus.

Note: I only checked parse_results and not parse_virtual_results

I will create an issue in the dadbod plugin.

Thx a lot for your plugin!! It is amazing :)

[dbmatheus]

Hi @nlinaje, I was also looking into this issue and found out in the vim-dadbod plugin there's an option already implemented but I'm not sure how this could be done from vim-dadbod-ui (I'm pretty new to nvim/vimscript).

The oracle.vim have this piece of code which uses -S option to sqlplus:
function! db#adapter#oracle#filter(url) abort let cmd = db#adapter#oracle#interactive(a:url) call insert(cmd, '-S', 1) return cmd endfunction

[nlinaje]

@dbmatheus I am pretty new to vimscript too, in fact I am learning it while debugging this great plugin :)
The piece you found is very interesting. I found out, that if you change this line in vim-dadbod-ui from
let callable = get(a:scheme, 'callable', 'interactive')
to:
let callable = get(a:scheme, 'callable', 'filter')
then there is no need to change anything vim-dadbod, as the vim-dadbod-ui uses the function you found. Nevertheless, I don't know the consequences of this change for other DBMS, so it is better let the experts check it!

[dbmatheus]

Nice @nlinaje, I see filter is implemented for the other drivers in vim-dadbod so maybe this will be a good solution, and with this we would need to remove the slicing expression from results[15:-5] for example right?

[kristijanhusak]

@nlinaje @dbmatheus I pushed a change that should use the filter for oracle. Pull latest master and let me know if it works.

[nlinaje]

@kristijanhusak This works only if you change the slice to [3:], because [15:-5] removes 3 empty lines and the first 12 databases from the top and deletes 2 or 3 schemas from the bottom (rest are empty lines).
Thx again!!

[kristijanhusak]

@nlinaje does this mean that Oracle is now broken?

[nlinaje]

Well, it was broken for me before too, in versions 12.2 and 23.4. If I put [3:0] in the slice it works with both versions.

[kristijanhusak]

@nlinaje in which slice? Can you give a diff or create a PR with the suggested changes?

[nlinaje]

This is my first PR! I hope I did not miss anything!

[kristijanhusak]

@nlinaje @dartey25 @dbmatheus I pushed a change to trim only first 3 lines, per @nlinaje suggestion. Can you pull latest master and give it a try with both sqlplus and the ORA_bin = 'sql' ?

[Esatollah]

currently have been using sqlplus 23.5 and couldn't get it to work other than schemas.

installed sqlcl and set the ora_bin to sql inside init lua the way dbmatheus described and can't get that working either

I hardcoded
return [get(g:, 'dbext_default_ORA_bin', 'sql'), '-L',
in the oracle.vim file so it would use sqlcl. Prcoedures etc. still don't show up. The schemas do but the contents of each schema are now empty.

The Oracle Database Version is 19c