This content has been marked as final. Show 4 replies
Welcome to the forum!
When describe a view, datatype of few column shown as UNDEFINED and rest of columns are ok.
Also, on querying ALL_TAB_COLUMNS, Data_type is UNDEFINED and Data_length is 0. But we are able to query that view and it is showing correct data.
Well - you haven't given us anything to look at.
1. the database name and full version.
2. the full java version
3. the jdbc jar file name and version
4. the actual command you are using to describe the view and the actual results
5. the actual query you are using on ALL_TAB_COLUMNS and the actual results (include only the relevant columns, not all of them)
6. the query you are using ('we are able to query that view')
7. the DDL for the view using
Confirm that the view is VALID.
SELECT DBMS_METADATA.GET_DDL('VIEW', 'myViewName', 'mySchema') FROM DUAL;
Thanks for replying and looking into the issue. Please find details required.
1.Database : Oracle 10g release 10.2
2.Java ver. : 1.6.0_41
3. Dont know how to check
4. Query : DESC schema.view_name;
Name Type Nullable Default Comments
--------------------------- -------------- -------- ------- --------
XXXXX3 UNDEFINED Y
XXXXX4 UNDEFINED Y
XXXXX5 UNDEFINED Y
XXXXX6 UNDEFINED Y
XXXXX7 UNDEFINED Y
XXXXX8 UNDEFINED Y
XXXXX9 UNDEFINED Y
XXXXX10 UNDEFINED Y
XXXXX11 UNDEFINED Y
XXXXX12 UNDEFINED Y
XXXXX13 UNDEFINED Y
XXXXX14 UNDEFINED Y
XXXXX15 UNDEFINED Y
XXXXX16 UNDEFINED Y
5. Query : SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = view_name;
COLUMN_NAME DATA_TYPE DATA_LENGTH
XXXX1 VARCHAR2 360
XXXX2 VARCHAR2 15
XXXX3 UNDEFINED 0
XXXX4 UNDEFINED 0
XXXX5 UNDEFINED 0
XXXX6 UNDEFINED 0
XXXX7 UNDEFINED 0
XXXX8 UNDEFINED 0
XXXX9 UNDEFINED 0
XXXX10 UNDEFINED 0
XXXX11 UNDEFINED 0
XXXX12 UNDEFINED 0
XXXX13 UNDEFINED 0
XXXX14 UNDEFINED 0
XXXX15 UNDEFINED 0
XXXX16 UNDEFINED 0
6. SELECT * FROM schema.view_name;
7. Not able to query.
Status of View is valid when query in all_objects.
Due to security reasons schema,view name and column names are replaced.
Hope these details will help.
3. Dont know how to check
You don't know how to tell what the name of the JDBC jar file is? Ask one of your coworkers. You need to make sure you are using the latest jar file.
Your problem could be a permissions issue where the user has permissions on the view but not the permissions needed to describe the underlying tables or one of the underlying tables might be on a remote server.
No way to tell since you decided not to post the view DDL showing the tables involved or post the owner of the view, the owner of each of the tables or the user that is performing the query.
Query the view using a user with DBA privileges and see if that resolves the problem.