Need help in Data Elements display in the report for documentation purpose

Ramya Madhyastha-Oracle

Hi ,

I have a query whether we can display the table or a dataset columns metadata as a report for the end users to show the data type for the columns ?

I mean something like this in OAC Report : DESCRIBE TABLE_NAME

Sumanth V -Oracle

@Ramya Madhyastha-Oracle -

Yes, you can display table metadata in an Oracle Analytics Cloud (OAC) workbook, though it requires creating a dataset or SQL queries to expose and visualize metadata details.

Example:

Hope this helps!

Gianni Ceresa

The column "table_name" in user_tab_columnsis just the table name, not the schema . table name. And user_tab_columns, as the name suggest, only works for objects owned by the current user (https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/USER_TAB_COLUMNS.html).

If the owner of the table is another user, you have to switch to all_tab_columns (could require different permissions in your DB ), and in there you have a column "owner" for the schema owning the table and again "table_name" that is just for the table name.

@Ramya Madhyastha-Oracle , do you really want to describe the table as it is in the database? Or do you want to describe the columns and data types of the columns in a DV dataset? Because they could be different if you transform them when creating the dataset based on your table.

RajeshPolavarapu-Oracle

Hi @Ramya Madhyastha-Oracle

You try checking in other tables (or views) called ALL_TAB_COLUMNS or DBA_TAB_COLUMNS

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_TAB_COLUMNS.html#GUID-91F945AE-5778-45A6-A07E-775A006A9AA1

Hope it helps.

Ramya Madhyastha-Oracle

HI @Gianni Ceresa @RajeshPolavarapu-Oracle

Thank you !! all_tab_column worked

@Gianni Ceresa , Basically I need to connect to the database and show all the tables metadata and add business need of each column and as and when new column is added I need not have to manually add it to the report it will be picked up from the dataflow or dataset

Ramya Madhyastha-Oracle

HI @Sumanth V,

Thank you !!

Sorry to be more precise I am calling the table from the database schema directly .

Sumanth V -Oracle

@Ramya Madhyastha-Oracle -

We cannot run DESC table or DESCRIBE table commands directly in Oracle Analytics Cloud (OAC). These are SQL*Plus commands used to retrieve metadata about a table, such as column names, data types, and constraints, in a database. OAC does not have the functionality to directly execute such commands as it primarily interacts with logical SQL or queries that operate on subject areas or datasets.

Other suggestions are welcome. Thanks!

Ramya Madhyastha-Oracle

Thank you @Sumanth V -Oracle !!

Any workaround for this ?

Sumanth V -Oracle

@Ramya Madhyastha-Oracle - We can go with the approach I suggested first and control the access of dataset. Other than that, you may have to log it as an Enhancement Request with the business justification in the Idea Labs.

Ramya Madhyastha-Oracle

I tried with your approach but it didnt work

Sumanth V -Oracle

@Ramya Madhyastha-Oracle - It would be helpful if you share an error or what didn't work. Thank you.

Ramya Madhyastha-Oracle

Tried 2 different ways

1. Directly tried calling from the schema

2. Created a manual query as select * from schema_name.tablename and saved the table name as "table_name" and then tried to run the above query by creating another manual query

Sumanth V -Oracle

@Ramya Madhyastha-Oracle - Loose the where condition and just query the table user_tab_columns and validate if the tables are getting listed first.

Ramya Madhyastha-Oracle

yes it did result but the table names are different