Zero Tables Shown when Connected to Oracle using TNS Alias

[Iron-E]

Thank you for this plugin! I've been using it on MariaDB databases to much success. Recently I started using an Oracle database for work (see tpope/vim-dadbod#75), but I'm having a recurring issue with the UI.

I first configure vim-dadbod-ui to connect to my oracle database by providing a configuration which looks like this:

[
	{
		"url": "oracle://user_name:pass_word@service_name",
		"name": "Foo"
	}
]

• service_name is an alias defined in the tnsnames.ora located at $TNS_ADMIN.

Next, I connect to the database with :DBUI. When it establishes the connection, I see this:

However, when I run this query:

select count(table_name) from user_tables;

…I get this output:

None of the tables seem to be reported. It also looks like coc-db and vim-dadbod-completion are affected, as completions do not work when connected to the Oracle database. Should I create separate tickets for those?

[kristijanhusak]

Looks like vim-dadbod doesn't have an implementation to fetch the tables for oracle.
For example, here's the function to fetch tables for mysql: https://github.com/tpope/vim-dadbod/blob/master/autoload/db/adapter/mysql.vim#L50, and for oracle adapter, there is no "tables" function.
I'm relying on this function to get the tables list.
I don't have any experience with oracle, so if you know how to make this function to get tables for it, please provide it here and i'll make a PR to dadbod.

[Iron-E]

This is the Oracle equivalent of MySQL's show tables:

-- show tables owned by the current user:
select table_name 
from user_tables;

-- show all tables viewable by the current user (including system tables):
select table_name 
from all_tables;

-- get all non-system tables:
select t.table_name 
from all_tables t, all_users u
where t.owner = u.username
and u.common = 'NO';

• Note that every table in the first query will be shown in the second query and third query.

I'm not sure which of those three fits our needs here the best, but both of those will generate table names from the Oracle database (tested on Oracle database 19c).

Edit: To run a command and exit with sqlplus, you have to pipe:

echo 'select table_name from user_tables;' | sqlplus -L user_name/pass_word@service_name

I'll get to work on drafting a working prototype.

[Iron-E]

I've had some success with the following function:

function! g:TestTables(url)
	let l:names = split(system("echo 'set markup csv on;\nselect table_name from user_tables;' | " . db#adapter#oracle#interactive(a:url)), '\n')[12:-6]

	for l:i in range(len(l:names))
		let l:names[l:i] = l:names[l:i][1:-2]
	endfor

	return l:names
endfunction

Here's what it does:

1. Generates the oracle#interactive for the a:url.
2. Executes set markup csv on; on the server.
3. Queries select table_name from user_tables;
   • Replace this query with whichever you feel is most appropriate from the above comment, just don't replace the \n before it.
4. Strip the SQL*Plus banner.
5. Strip the CSV quotation marks.
6. Return the table names.

Edit: I just noticed mysql's adapter shows the table header as the first item in the returned list. I've adjusted my function to do the same. As such, the 0 index of the returned list for that function will always be "TABLE_NAME" unless you increase [12:-6] to [13:-6].

[kristijanhusak]

I added the function to my dadbod fork https://github.com/kristijanhusak/vim-dadbod. I'd appreciate it if you would replace tpope's dadbod with my fork and give it a try. I tried setting up oracle myself on linux, but I'm having some issues, thus I can't test it.
If it works you can either stay with my fork, or I can try to make a PR to tpope's repo.

[Iron-E]

I tested the fork, and it looks like we're getting really close!

All the tables are shown. Unfortunately, so is the table header ("TABLE_NAME").

There must be some extra step done for mysql, as the 0-index for db#adapter#mysql#tables() is removed. I tried to make the behavior of my function uniform with the mysql adapter, but we can either fix this by changing:

let l:names = split(system("echo 'set markup csv on;\nselect table_name from user_tables;' | " . db#adapter#oracle#interactive(a:url)), '\n')[12:-6]

…to:

let l:names = split(system("echo 'set markup csv on;\nselect table_name from user_tables;' | " . db#adapter#oracle#interactive(a:url)), '\n')[13:-6]

…or re-implementing that special behavior for oracle as well.

---

Finally, I see that tables now have completion available. However, each attribute of the table does not. I see that the mysql adapter provides additional information about each table:

Does this assist in the completion process? If so, I would be willing to write the queries to provide this information for Oracle if you can give me more information about how vim-dadbod-ui leverages it.

---

Thank you for all the help so far! I really appreciate it. You knew exactly where to look and how to solve the problem.

[kristijanhusak]

Thanks. We can just remove the first row to remove the List item. This is where it's done for mysql https://github.com/kristijanhusak/vim-dadbod-ui/blob/master/autoload/db_ui/schemas.vim#L83.

PostgreSQL, MySQL and MSSQL are better supported by vim-dadbod-ui/vim-dadbod-completion. Oracle falls back to dadbod's table function, like everything else that is not in the previously mentioned list.
If you want to add a support for oracle for dadbod-ui and the completion, follow other implementations here:

https://github.com/kristijanhusak/vim-dadbod-ui/blob/master/autoload/db_ui/schemas.vim

https://github.com/kristijanhusak/vim-dadbod-completion/blob/master/autoload/vim_dadbod_completion/schemas.vim

Note that I'm not able to test this at the moment, but I hope I'll be able to set up oracle at some point.

[Iron-E]

Wow, this is quite helpful! Thank you.

I've gotten started with this plugin over here and quite a few things are working already. I'll file a PR draft here when it's closer. After that I'll get started on the vim-dadbod-completion integration.

As for this issue, I am inclined to close it since we're moving out of the initial problem I was having and into a bigger problem scope. I can file further issues later if need-be— thoughts?

Before we close it though— there is one problem I am having: I got table helpers and schema settings configured, so now it can detect the schemas of the database:

However, each schema shows as having zero tables. Which query is used to determine what tables each schema has?

• This is the schema query
• This is the schema tables query

Edit: Solved by formatting query results. Oracle is a real stickler about this one 🙄

[kristijanhusak]

We can leave it until your PR is closed. If you do full integration for oracle like it's done for psql, mysql and mssql, you will not have to use my fork to get the tables. It will be part of dadbod ui. Does oracle support schemas like postgres?

[Iron-E]

Oracle does support schemas— although they're sort of blended together with users. Whereas Postres and MySQL allow for multiple databases which have multiple levels of access to them according to user groups, Oracle defines them together under one umbrella.

• Here's a related post.

[kristijanhusak]

Ok, you can still print them out in a similar way like it's done for others, so should be pretty straightforward. Let me know if you need explanation around something.

[Iron-E]

I'm happy to report that everything is working for the vim-dadbod-ui draft except for db_ui#dbout#jump_to_foreign_table()! This one is giving me some issues, and I'm hoping you can point me in the right direction.

Here's what I've determined:

• The field_name on this line is incorrectly formed for Oracle's output.
• When field_name is passed to the foreign_key_query, the {col_name} is replaced with a column name that does not exist and the query fails.
• The field_name comes from logic based on this function (s:get_cell_range).

I've attached a sample dbout file to show what I mean, based on the example MariaDB & Oracle databases I set up for this PR: sample.txt.

• TmLocCode and TmRegCode are foreign keys.
• When selecting the column name, it tends to grab more than one column.

[kristijanhusak]

Great!

What did you set for cell_line_number and cell_line_pattern for oracle schema? Are you able to set custom column delimiter for the query like it's done for sqlserver? What did you set for cell_line_number and cell_line_pattern for oracle?

[Iron-E]

I set them to these values:

Field	Value
cell_line_number	1
cell_line_pattern	'^-\+\( \+-\+\)*'
parse_results	'\t\+ *'

You can change the column delimiter from a space to anything if you use the SET MARKUP CSV ON; option, but I don't think there's a column line then; the first row of the CSV is the column names.

After doing a little more digging, it looks like the col('.') call on this same line isn't positioned in the right place for s:get_cell_range's logic to produce the expected result. What sets the column position?

[kristijanhusak]

Is the sample output you attached the default one, without any custom configuration? It looks like it has two tabs between columns, and for the cell line delimiter (2nd line), it is delimited only by a space. This inconsistency is causing the issue, because code tries to find the column name and value by the cell width, which is in this case bigger than any cell value. If we would have 1 space between columns like it is done for cell line delimiter, that would solve the issue.

[Iron-E]

I've adjusted the query and result. Here is what I use for the query:

-- Max length of a line.
SET linesize 4000;
-- Number of entries before next column header.
SET pagesize 4000;

-- Unfortunately, Oracle requires manually formatting columns.
-- You can use `set trim on` but that truncates the column name.
COLUMN TmCode    FORMAT a6;
COLUMN TmLocCode FORMAT a9;
COLUMN TmName    FORMAT a12;
COLUMN TmRegCode FORMAT a9;
COLUMN TmStatus  FORMAT a8;

SELECT * FROM NF_Team_BR;

The result is attached here. Based on this result, I've changed the s:results_parser delimiter to \s\s\+ (from \t\+\s*).

When I use <C-]> on the TmRegCode 'JPnw', the cell_range can be represented by:

set colorcolumn=17,28

[kristijanhusak]

Let's not do any formatting. What's the dbout output when you just do the select query, without all the other settings?

[Iron-E]

Here's the result without formatting. WIthout the COLUMN x FORMAT statements, each column is automatically adjusted to the maximum length of the data type of the column, rather than the value of the column (like MySQL).

Edit: Here's the query for reference.

SELECT * FROM NF_Team_BR;

And the columns selected when jumping from 'JPnw':

set colorcolumn=10,39

Seems like it always grabs TmName instead of TmRegCode.

[kristijanhusak]

Wow, that's really messed up. You do this formatting every time for every query?
We should make it so it works with defaults.
Looks like we cannot do the same thing like for others. Let me think about it how to parse this, and until then just create a PR with everything else so we can start reviewing it.

Do you maybe have some test database that is publicly accessible which i can use for test? I managed to install the client (sqlplus), but not the server.

[Iron-E]

sqlplus column formatting is pretty messed up. Essentially, they provide all the required settings to make it look right by yourself, but they don't do it automatically. I had to embed these COLUMN commands as a workaround (seen here) in a few places.

I'll work on getting a public environment set up, and I'll initiate the PR.

A potential workaround would be to use sqlcl instead of sqlplus for queries, but that would require a breaking change to vim-dadbod, so while it is an option, it isn't a great one.

[Iron-E]

After the testing the completion pull this week, I haven't come across any more bugs since its last commit.

Should we merge that one while continuing with the UI?

[kristijanhusak]

I left you a review there. You just need to tweak one thing and it should be good to go.

[Iron-E]

Closed by #68

[Maswor]

-- show all tables viewable by the current user (including system tables):
select table_name 
from all_tables;

Can we use this version to query table in Oracle. In production we often have table owned by a Team but queryable by the other. Say:

• the contract signing Team can edit the subscription to let a person listen to Spotify.
• The music Team can check the database then allow the user to listen without ads.
• Security issue in music Team can't enable a hacker to increase her subscription to 99 years

The current schema made the assumption that I owned tables that I want to query, which unfortunately show 0 table.

[kristijanhusak]

I don't have enough knowledge of Oracle to give a strong opinion on this. If it would give the same result as you would have with the full permissions on the db, I guess it's ok.

[Maswor]

I don't have enough knowledge of Oracle to give a strong opinion on this. If it would give the same result as you would have with the full permissions on the db, I guess it's ok.

I can raise an PR, is that ok?

[kristijanhusak]

I don't have enough knowledge of Oracle to give a strong opinion on this. If it would give the same result as you would have with the full permissions on the db, I guess it's ok.

I can raise an PR, is that ok?

Of course :)