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
@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!
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).
user_tab_columns
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.
all_tab_columns
@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.
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.
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
