Ability to search on VIEWS as well

[koenvb]

We have a database with important views as well, could we search on VIEWS as well?
Currently if you ask to search for a view eg. supplier_info

Eg get_object_source from db-context (local)

{
  `object_name`: `SUPPLIER_INFO`,
  `object_type`: `VIEW`
}

Source for VIEW SUPPLIER_INFO:

Error retrieving source: ORA-31603: object "SUPPLIER_INFO" of type VIEW not found in schema "APP"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6718
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-31603/

it will in the end realize that an object exist get_pl_sql_objects from db-context

{
  `object_type`: `VIEW`,
  `name_pattern`: `SUPPLIER_INFO`
}
Found 1 VIEW objects:

VIEW: SUPPLIER_INFO
Owner: APP
Status: VALID
Created: 2021-08-16 17:55:31

But it does not want to query it or show the desc of it?
Would be nice maybe to have the same functionality for views as you have for tables.

[danielmeppiel]

Definitely this should be working, let me have a look at it.

[koenvb]

Happy to help to test where required.

[danielmeppiel]

@koenvb I have made some changes in branch https://github.com/danielmeppiel/mcp-db-context/tree/3-ability-to-search-on-views. I have added some views to the test database embedded in the project. The change is ready to merge in PR #5. If it does not work for you, let me know so that I can try to reproduce.

It is working for me, e.g. for get_pl_sql_objects:

and for get_object_source (this wasn't working at first, but should now in the branch I linked):

[koenvb]

The issue is that for example my readonly user has no access to the SELECT dbms_metadata.get_ddl() call which makes that it cannot generate the DDL.

So my user can use the following to get this information

SELECT 
    atc.column_name,
    atc.data_type,
    CASE WHEN atc.nullable = 'Y' THEN 'NULL' ELSE 'NOT NULL' END AS nullable,
    atc.data_default,
    atc.column_id,
    acc.comments
FROM 
    all_tab_columns atc
LEFT JOIN 
    all_col_comments acc ON (atc.owner = acc.owner AND atc.table_name = acc.table_name AND atc.column_name = acc.column_name)
WHERE 
    atc.table_name = 'CUSTOMER_INFO'
    AND atc.owner = 'IAPP'
ORDER BY 
    atc.column_id;

So the issue is here permission issues so ideally it could fall back on such a query if the other one does not work.
Again, could be specific to our case but would be a nice fallback option when the dbms_metadata.get_ddl() does not work.