4 Replies Latest reply: Mar 26, 2013 10:44 AM by rp0428 RSS

    Undefined Data type

    998339

      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.

      Please help.

      Thanks in advance


      Regards,
      Sundeep

      Edited by: 995336 on Mar 22, 2013 9:00 AM

        • 1. Re: Undefined Data type
          rp0428
          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.

          Post:

          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
          SELECT DBMS_METADATA.GET_DDL('VIEW', 'myViewName', 'mySchema') FROM DUAL;
          Confirm that the view is VALID.
          • 2. Re: Undefined Data type
            998339
            Hi,

            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;
            Output :
            Name Type Nullable Default Comments
            --------------------------- -------------- -------- ------- --------
            XXXXX1      VARCHAR2(360)
            XXXXX2      VARCHAR2(15)
            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;
            Output :
            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. Re: Undefined Data type
              EJP
              7. Not able to query.
              Until you are able to provide the actual table definition it is impossible for anyone to help you.
              • 4. Re: Undefined Data type
                rp0428
                >
                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.