6 Replies Latest reply: Jul 11, 2012 2:04 PM by 947261 RSS

    ORA-04063: view error

    947261
      Hello Guys,

      When I try to open view in sql developer. It gives me ORA-04063: view "POSTRACK_TR.WIZARD_SURVEY" error. The SQL code of view is below, I don't understand what is the problem. I have just encountered this kind of error. Do you have any idea? what should I do?
      CREATE OR REPLACE FORCE VIEW "POSTRACK_TR"."WIZARD_SURVEY" ("SURVEY_TYPE", "GROUPNAME", "MAIN_ORDER", "SUBGROUPNAME", "SUB_ORDER", "QUESTION", "QUESTION_ORDER", "QUESTION_TYPE_NAME", "QUESTION_REQUIRED", "ANSWERS_TYPE", "CATEGORY", "SUB_CATEGORY", "BRAND", "SKU", "BARCODE", "FLAVOR", "PRODUCT_FAMILY", "PACKAGETYPE", "SUPPLIER", "FACTOR", "QUESTION_UID")
      AS
        SELECT DISTINCT sut.survey_type,
          mg.groupname,
          mg.order_number AS main_order,
          sg.subgrouptext AS subgroupname,
          --sgd.sub_order,
          q.question,
          qd.order_number AS question_order,
          qt.question_type_name,
          qd.question_required,
          AT.answers_type,
          c.category ,
          sc.sub_category,
          b.brand,
          sk.sku,
          sk.barcode,
          f.flavor,
          pf.product_family,
          pt.packagetype,
          sp.supplier,
          cs.factor,
          qd.question_uid
        FROM survey_types sut,
          question_def qd,
          main_groups_def mg,
          subgroups sg,
          --sub_groups_def sgd,
          questions q,
          question_type qt,
          answers_types AT,
          categories_suppliers cs,
          categories c,
          sub_categories sc,
          brands b,
          skus sk,
          flavors f,
          product_families pf,
          package_types pt,
          suppliers sp
        WHERE mg.group_id = qd.main_group_id
        AND sg.subgroupid = qd.sub_group_id
          --AND sgd.sub_group_id        = qd.sub_group_id
          --AND sgd.main_group_id       = qd.main_group_id
        AND q.questionid            = qd.question_id
        AND qt.question_type_id     =qd.question_type_id
        AND AT.answers_type_id      = qd.answer_type_id
        AND cs.question_uid(+)      = qd.question_uid
        AND c.category_id(+)        = cs.category_id
        AND sc.sub_category_id(+)   = cs.sub_category_id
        AND b.brand_id(+)           = cs.brand_id
        AND sk.barcode(+)           = cs.barcode
        AND f.flavor_id(+)          = cs.flavor_id
        AND pf.product_family_id(+) = cs.product_family_id
        AND pt.packagetype_id(+)    = cs.packagetype_id
        AND sp.supplier_id(+)       = cs.supplier_id
        AND ((mg.belongto1          =1
        AND qd.belongto1            = 1
        AND sut.survey_type_id      = 1)
        OR ( mg.belongto2           =1
        AND qd.belongto2            = 1
        AND sut.survey_type_id      = 2)
        OR ( mg.belongto3           =1
        AND qd.belongto3            = 1
        AND sut.survey_type_id      = 3)
        OR ( mg.belongto4           =1
        AND qd.belongto4            = 1
        AND sut.survey_type_id      = 4)
        OR ( mg.belongto5           =1
        AND qd.belongto5            = 1
        AND sut.survey_type_id      = 5)
        OR ( mg.belongto6           =1
        AND qd.belongto6            = 1
        AND sut.survey_type_id      = 6)
        OR ( mg.belongto7           =1
        AND qd.belongto7            = 1
        AND sut.survey_type_id      = 7)
        OR ( mg.belongto8           =1
        AND qd.belongto8            = 1
        AND sut.survey_type_id      = 8)
        OR ( mg.belongto9           =1
        AND qd.belongto9            = 1
        AND sut.survey_type_id      = 9)
        OR ( mg.belongto10          =1
        AND qd.belongto10           = 1
        AND sut.survey_type_id      = 10)
        OR (mg.belongto11           =1
        AND qd.belongto11           = 1
        AND sut.survey_type_id      = 11)
        OR ( mg.belongto12          =1
        AND qd.belongto12           = 1
        AND sut.survey_type_id      = 12)
        OR ( mg.belongto13          =1
        AND qd.belongto13           = 1
        AND sut.survey_type_id      = 13)
        OR ( mg.belongto14          =1
        AND qd.belongto14           = 1
        AND sut.survey_type_id      =14)
        OR ( mg.belongto15          =1
        AND qd.belongto15           = 1
        AND sut.survey_type_id      = 15) )
        ORDER BY sut.survey_type,
          mg.order_number,
          --sgd.sub_order,
          qd.order_number;
      In addition, when I type desc wizard_survey, it gives very interesting output???
      desc wizard_survey
      Name               Null Type        
      ------------------ ---- ----------- 
      SURVEY_TYPE             UNDEFINED() 
      GROUPNAME               UNDEFINED() 
      MAIN_ORDER              UNDEFINED() 
      SUBGROUPNAME            UNDEFINED() 
      SUB_ORDER               UNDEFINED() 
      QUESTION                UNDEFINED() 
      QUESTION_ORDER          UNDEFINED() 
      QUESTION_TYPE_NAME      UNDEFINED() 
      QUESTION_REQUIRED       UNDEFINED() 
      ANSWERS_TYPE            UNDEFINED() 
      CATEGORY                UNDEFINED() 
      SUB_CATEGORY            UNDEFINED() 
      BRAND                   UNDEFINED() 
      SKU                     UNDEFINED() 
      BARCODE                 UNDEFINED() 
      FLAVOR                  UNDEFINED() 
      PRODUCT_FAMILY          UNDEFINED() 
      PACKAGETYPE             UNDEFINED() 
      SUPPLIER                UNDEFINED() 
      FACTOR                  UNDEFINED() 
      QUESTION_UID            UNDEFINED() 
      Thanks for your help.

      Edited by: 944258 on 11.Tem.2012 11:43
        • 1. Re: ORA-04063: view error
          sb92075
          04063, 00000, "%s has errors"
          // *Cause:  Attempt to execute a stored procedure or use a view that has
          //          errors.  For stored procedures, the problem could be syntax errors
          //          or references to other, non-existent procedures.  For views,
          //          the problem could be a reference in the view's defining query to
          //          a non-existent table.
          //          Can also be a table which has references to non-existent or
          //          inaccessible types.
          // *Action: Fix the errors and/or create referenced objects as necessary.
          • 2. Re: ORA-04063: view error
            Hoek
            ORA-04063: string has errors
            Cause: Attempt to execute a stored procedure or use a view that has errors. For stored procedures, the problem could be syntax errors or references to other, non-existent procedures. For views, the problem could be a reference in the view's defining query to a non-existent table. Can also be a table which has references to non-existent or inaccessible types.
            Action: Fix the errors and/or create referenced objects as necessary.
            http://docs.oracle.com/cd/E11882_01/server.112/e17766/e2100.htm#sthref1898
            • 3. Re: ORA-04063: view error
              Solomon Yakobson
              944258 wrote:
              CREATE OR REPLACE <font color=red size=3>FORCE</font> VIEW "POSTRACK_TR"."WIZARD_SURVEY"
              FORCE means create view even if it has errors. To check what the errors are either remove FORCE or, in SQL*Plus issue:
              SHOW ERR VIEW WIZARD_SURVEY
              Or issue :
              SELECT * FROM ALL_ERRORS
              WHERE OWNER = 'POSTRACK_TR' and NAME = 'WIZARD_SURVEY' AND TYPE = 'VIEW';
              SY.
              • 4. Re: ORA-04063: view error
                947261
                SHOW ERR VIEW WIZARD_SURVEY;

                ORA-01730: invalid number of column names specified



                It gives this error.

                Ok guys I found it thanks a million.

                Edited by: 944258 on 11.Tem.2012 11:52
                • 5. Re: ORA-04063: view error
                  omaha66
                  Check that the number of columns in your column view specification is the same number as the number you have listed in your select statement -

                  create view my_view (col1 ...)

                  has same number of columns as your select statement

                  select col1, ...
                  • 6. Re: ORA-04063: view error
                    947261
                    I hjave already checked it, they were not match. I fixed it. You are right.

                    Thanks a lot everybody.