Adapt the get_training_plan_generic method for Oracle Database

[rmendesl]

The get_training_plan_generic method does not work for Oracle Database. Because in the Oracle Database there is no INFORMATION_SCHEMA like MySQL or SQL Server.

It uses other views like ALL_TAB_COLUMNS or USER_TAB_COLUMNS that contain the same information like TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, etc.

I would like to suggest adapting this method to the Oracle database.

Error:

df_information_schema = vn.run_sql("SELECT * FROM USER_TAB_COLUMNS")

Traceback (most recent call last):
  File "c:\Projects\Python\sonora.ai3\app.py", line 40, in <module>
    training()
  File "c:\Projects\Python\sonora.ai3\app.py", line 30, in training
    plan = vn.get_training_plan_generic(df_information_schema)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Projects\Python\sonora.ai3\sonora_env\Lib\site-packages\vanna\base\base.py", line 1794, in get_training_plan_generic
    database_column = df.columns[
                      ^^^^^^^^^^^
IndexError: list index out of range

[PucaVaz]

Can you use something like

SELECT VIEW_NAME, TEXT
FROM ALL_VIEWS
WHERE OWNER = 'owner' AND VIEW_NAME = 'name';? 

If you run with vn.runsql() will return a pd data frame, so you can adjust a little bitter to work

[gregholliday]

I was able to work around it with the following SQL:
SELECT 'Name of database' AS DATABASE, OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE FROM ALL_TAB_COLUMNS WHERE OWNER = 'Owner Name'

I guess DATABASE could be anything you want since it's hard coded, but I used the actual name of my PDB. This seemed to work. I've been able to run through the example in the documentation using my Oracle DB.

[rmendesl]

@gregholliday I did the process you mentioned and it apparently worked.