Код ошибки 04063

The show errors SQL*Plus command defaults to reporting errors for the last PL/SQL object created. To show errors for a view you have to specify it:

SQL> create or replace force view someview as select * from nosuchtable;

Warning: View created with compilation errors.

SQL> show errors
No errors.

SQL> show errors view someview
Errors for VIEW SOMEVIEW:

LINE/COL   ERROR
---------- --------------------------------------------------------------------
0/0        ORA-00942: table or view does not exist
0/0        ORA-54039: table must have at least one column that is not invisible

Or you can just query user_errors:

SQL> select line, text from user_errors where name = 'SOMEVIEW' order by sequence;
LINE TEXT
---- --------------------------------------------------------------------------------
   0 ORA-54039: table must have at least one column that is not invisible
   0 ORA-00942: table or view does not exist

If you really named it something like "view_name" (in double quotes) then you need to refer to it exactly the same way, including case and quotes.

show errors view "someview"

select line, text from user_errors where name = 'someview' order by sequence;

Or, try creating the view without the force option:

SQL> create or replace view someview as select * from nosuchtable;
create or replace view someview as select * from nosuchtable
                                                 *
ERROR at line 1:
ORA-00942: table or view does not exist

Or, test the query on its own without the create view part:

SQL> select * from nosuchtable;
select * from nosuchtable
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Источник

fix-ORA-04063-package-body-has-errors

In Oracle database, we used to create an object even if there is a compilation error within the procedure. With the help of this great feature, a user can deploy a schema without fixing each problem with invalid objects. During these changes, there may be error take place stated as “ORA-04063: package body has errors”. 

However, if you get this error then stop worrying at all because I am going to show here how you can easily fix ora-04063 package body has errors. All the solutions mentioned here, hopefully, will be very helpful for you. You have to just follow the ways step by step and resolve ‘ora-04063 package body has errors‘ error.

But, before we proceed to the fixes for this error, let us first have a look at detailed information on ora-04063 package body has errors.

An error stated as ORA-04063 package body has errors takes place generally because two reasons; either trying to execute a stored procedure was made or trying to use a view that has errors.

Well for stored procedures, the error may be a syntax issue or references to other packages or procedures that actually do not exist.

However, for views, most probably, the problem could be a reference in the defining query of a view to the non-existent table. It can also be a table that has references to inaccessible or non-existent types. This data can be revealed when the message is expanded in the error utility.

Symptoms of ORA-04063 Package Body Has Errors

Here are some of the common signs that shows an error stated as ORA-04063 Package Body Has Errors:

The package body shows invalid in dba_objects:

oracle error1

When you try to compile the package, it shows errors.

oracle error2

How To Fix ORA-04063 Package Body Has Errors

Here are the best ways that you can try to fix ORA-04063 package body has errors. When it comes to fix ora-04063 package body has errors, you can try PL/SQL query by itself in SQL*plus command and also contact your database administrator for assistance.

Fix #1: Run PL/SQL query by itself in SQL*plus command

The very first option you can try to fix ORA-04063 package body has errors is to run PL/SQL query by itself in SQL*Plus command. This is because, after you run this query, SQL*Plus ‘show error’ command should be given to view the reason for this error and the location of the error as well.

Syntax for PL/SQL command is as follows:

SQL > show errors

Viewing enhanced show errors command feedback, then join dba_errors and  dba_errors so that the feedback within the lines of PL/SQL source code can easily be viewed.

CREATE FORCE VIEW command can be chosen which will help you create the view but it mark as invalid. However, you have to make sure that you recreate the view after you run this command.

Let’s take an example, the package body PLOG in the schema LM_EMPLOY either does not exist or has a compilation error:

ORA-04063: package body “LM_EMPLOY.PLOG” has errors

In such a case where package errors arise, then connect to the database as LM_EMPLOY user and run the following command:

 SQL > alter package body plog compile;

After this use the “show error”command to see what errors need to be corrected. 

 SQL>show errors

Accessing an invalid view or procedure has been created with FORCE option. GRANT SELECT against the invalid view will return the ORA-04063 error.

In the given below case tab1 table does not exist. Since the FORCE option is used, the view is created. Consequently, when the GRANT SELECT is issued against this invalid view the ORA-04063 error is returned.

SQL>CREATE OR REPLACE FORCE VIEW vst.myview
AS SELECT * FROM tab1;

Warning: View created with compilation errors.

SQL> GRANT SELECT ON vst.myview TO USER_A;

ORA-04063: view “VST.MYVIEW” has errors

When the view is created on an existing table the GRANT SELECT does not return the ORA-04063 error message.

SQL>CREATE OR REPLACE FORCE VIEW vst.myview
AS SELECT * FROM dba_tables;

View created

SQL> GRANT SELECT ON vst.myview TO USER_A;
                                        

NOTE: It is always a good practice to double-check the syntax of all your command because it is difficult to proactively prevent seeing ORA-04063. Make sure that all the mentioned dependencies are correct and all variables being referenced exist.

Fix #2: Contact Your Database Administrator For Assistance

After trying the above method to fix ora-04063 package body has errors, if you find yourself unsuccessful then another way you can try is to contact the database administrator for help. Also, always check the Oracle consultants credentials and certification to ensure that they have relevant experience and expertise to help you out of that problem.

Fix #3: Try Oracle Database File Repair Tool To Fix ORA-04063 Package Body Has Errors

Even after trying the above fixes, if you are still unable to fix ora-04063 package body has errors then you can try one Oracle File Repair Tool. This tool is able to fix all kinds of errors you get related to the Oracle database. Using this tool will help you searches all Oracle databases which are actually present in the system and it also shows the preview of the recoverable database objects.

With the help of this tool, you can restore data from both databases as well as backup files. Also, this tool saves the recovered data as Transact-SQL script. This tool is very easy to use and there is no need to have any technical knowledge to use this tool.

Steps To Repair ORA-04063 Package Body Has Errors

You can try these below easy steps to fix ORA-04063 Package Body Has Errors using this fully featured camera:

Step 1: Search the Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases on your computer.

1

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in the left-side pane.

2

Step 3: Click an object to see its preview.

3

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

4

Step 5: Give the user name, password, and path of the blank database where you want to save the repaired database objects.

5

Step 6: Repairing and restoring various database objects after establishing a connection with a blank oracle database.

6

Final Verdict

While creating any object in the Oracle database, if you get an error stated as ‘ORA-04063 Package Body Has Errors’ then stop worrying now. I am saying so because, in this blog, I have tried my level best to help you out. You can easily fix ORA-04063 Package Body Has Errors using the above ways. Or, you can also try Oracle File Repair Tool to resolve the ora-04063 error.

This tool can help you fix ORA-04063 Package Body Has Errors easily in just a few steps. So, just try the ways and fix this issue in no time.

All the very best to you….

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.

Содержание

  1. Sql error ora 04063
  2. ORA-04063 during Data Pump Export / Queries against RESOURCE_VIEW (Doc ID 1526443.1)
  3. Applies to:
  4. Symptoms
  5. Cause
  6. To view full details, sign in with your My Oracle Support account.
  7. Don’t have a My Oracle Support account? Click to get started!
  8. How to Resolve ORA-04063: view has errors
  9. ORA-04063
  10. Solutions
  11. 1. Missing Column
  12. 2. Missing Table
  13. 3. Inaccessible DB Link
  14. More Consideration
  15. ORA-04063 View has errors
  16. Answers
  17. ORA-04063: table
  18. Best Answer
  19. Answers
  20. TABLE: AZ.AZ_DIFF_RESULTS
  21. Dependencies

Sql error ora 04063

In Oracle, you can generally create an object even if there is a compilation error within the procedure or package. With such a feature, a user can deploy a schema without fixing each problem with invalid objects. Unfortunately, this is not the case for views. If there is a compilation error in a procedure, function or package within the view, it will not be created, and you will run into ORA-04063.

Error ORA-04063 occurs because either an attempt to execute a stored procedure was made or an attempt to use a view that has errors was made.

For stored procedures, the problem may be a syntax issue or references to other procedures that do not exist.

For views, a possible problem could be a reference in the view’s defining query to a non-existent table. This can also be a table that has references to non-existent or inaccessible types. This information is revealed when the message is expanded in the oerr utility:

ORA-04063: table/view 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.

To resolve ORA-04063, execute the PL/SQL query by itself in SQL*Plus. After running the query, use the SQL*Plus “show error” command to view the reason for the error and the location (by line) of the error. The syntax for this command is:

SQL > show errors

To view enhanced show errors command feedback, join dba_errors and dba_service to view feedback within the lines of PL/SQL source code.

You may choose to use the CREATE FORCE VIEW command, which will create the view but mark it invalid. Be sure to recreate the view after you run this command.

In the following example, the package body PLOG in the schema LM_EMPLOY either does not exist or has a compilation error:

ORA-04063: package body “LM_EMPLOY.PLOG” has errors

In this case, to resolve the error, connect to the database as LM_EMPLOY user and run the following command:

SQL > alter package body plog compile;

Then use the “show error” command (SQL > show errors) to see what errors need to be corrected.

While it is difficult to proactively prevent seeing ORA-04063, it is always good practice to double check the syntax of all your commands. Make sure dependencies are correct and all variables being referenced exist.

If you continue to face problems with ORA-04063, you may consider contacting your database administrator for assistance if you are not the system DBA. Another solution may be to contact an Oracle professional to resolve the issue. Remember to always check the Oracle consultants’ credentials and certification to ensure that they have the relevant experience and expertise to help you meet your database needs.

Источник

ORA-04063 during Data Pump Export / Queries against RESOURCE_VIEW (Doc ID 1526443.1)

Last updated on JANUARY 30, 2022

Applies to:

Symptoms

A full Data Pump Export (expdp) fails with the following errors:

.
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
ORA-39127: unexpected error from call to export_string :=XDB.DBMS_XDBUTIL_INT.SYSTEM_INFO_EXP(0,dynconnect,’11.02.00.00.00′,newblock)
ORA-04063: package body «XDB.DBMS_XDBUTIL_INT» has errors
ORA-06508: PL/SQL: could not find program unit being called: «XDB.DBMS_XDBUTIL_INT»
ORA-06512: at line 1
ORA-06512: at «SYS.DBMS_METADATA», line 9281
.
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
ORA-39127: unexpected error from call to export_string :=XDB.DBMS_XDBUTIL_INT.SYSTEM_INFO_EXP(1,dynconnect,’11.02.00.00.00′,newblock)
ORA-04063: package body «XDB.DBMS_XDBUTIL_INT» has errors
ORA-06508: PL/SQL: could not find program unit being called: «XDB.DBMS_XDBUTIL_INT»
ORA-06512: at line 1
ORA-06512: at «SYS.DBMS_METADATA», line 9281
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TABLE:»OLAPSYS».»CWM2$OLAPEXPORTOBJECTTABLE»]
ORA-04063: package body «XDB.DBMS_XDBZ0» has errors
ORA-06508: PL/SQL: could not find program unit being called: «XDB.DBMS_XDBZ0»
ORA-06512: at «SYS.DBMS_SYS_ERROR», line 95
ORA-06512: at «SYS.KUPW$WORKER», line 9001
—— PL/SQL Call Stack ——
object line object
handle number name
0x1187ec280 20462 package body SYS.KUPW$WORKER
0x1187ec280 9028 package body SYS.KUPW$WORKER
0x1187ec280 10935 package body SYS.KUPW$WORKER
0x1187ec280 2728 package body SYS.KUPW$WORKER
0x1187ec280 9697 package body SYS.KUPW$WORKER
0x1187ec280 1775 package body SYS.KUPW$WORKER
0xff2701b0 2 anonymous block
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TABLE:»OLAPSYS».»CWM2$OLAPEXPORTOBJECTTABLE»]
ORA-04063: package body «XDB.DBMS_XDBZ0» has errors
ORA-06508: PL/SQL: could not find program unit being called: «XDB.DBMS_XDBZ0»
ORA-06512: at «SYS.DBMS_SYS_ERROR», line 95
ORA-06512: at «SYS.KUPW$WORKER», line 9001
—— PL/SQL Call Stack ——
object line object
handle number name
0x1187ec280 20462 package body SYS.KUPW$WORKER
0x1187ec280 9028 package body SYS.KUPW$WORKER
0x1187ec280 10935 package body SYS.KUPW$WORKER
0x1187ec280 2728 package body SYS.KUPW$WORKER
0x1187ec280 9697 package body SYS.KUPW$WORKER
0x1187ec280 1775 package body SYS.KUPW$WORKER
0xff2701b0 2 anonymous block
Job «SYSTEM».»SYS_EXPORT_FULL_01″ stopped due to fatal error at 16:42:12

Also, querying RESOURCE_VIEW fails:

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

How to Resolve ORA-04063: view has errors

ORA-04063 means the view that you want to grant to someone else has compile error, so the database cannot grant it to other users. Let’s see an example of ORA-04063:

SQL> conn hr/hr
Connected.
SQL> grant select on employees_v to sh;
grant select on employees_v to sh
*
ERROR at line 1:
ORA-04063: view «HR.EMPLOYEES_V» has errors

The short answer to the solution of ORA-04063 is to make the view compile successfully. You can compile the view again like this:

SQL> alter view employees_v compile;

Warning: View altered with compilation errors.

OK, we have errors, but how do we check the error?

We can query the dictionary view ALL_ERRORS to know the underlying error.

SQL> select text from all_errors where owner = ‘HR’ and type = ‘VIEW’ and name = ‘EMPLOYEES_V’;

Solutions

Usually, there’re several possible causes of ORA-04063:

1. Missing Column

One or more columns defined in view have been dropped in the base table. This error (ORA-00904) is very common, when the base table changed its definition, the view becomes invalid because of mismatch between view columns and table columns.

SQL> select text from all_errors where owner = ‘HR’ and type = ‘VIEW’ and name = ‘EMPLOYEES_V’;

As you can see, the error specifically complains about missing EMAIL column of the base table.

2. Missing Table

The base table does not exist. Apparently, it’s due to missing base table. It could be dropped or renamed. To prevent such error (ORA-00942), Users should consider the dependencies before dropping or renaming tables.

SQL> select text from all_errors where owner = ‘HR’ and type = ‘VIEW’ and name = ‘EMPLOYEES_V’;

3. Inaccessible DB Link

The db link is not accessible. It could be one of the following causes:

  • The database link does not exist. You have to create it.
  • The connect identifier defined in the database link cannot be found in tnsnames.ora .
  • The database link cannot be looked up. The solution depends on what you saw in the error message.

More Consideration

Generally speaking, ORA-04063 related to database links are not easily solved, because they involve the network condition, remote listeners and remote databases.

If there’s any TNS error, you may refer to TNSPING, How and Why. Moreover, I think you might like to know how to create and use database links and their restrictions.

Источник

ORA-04063 View has errors

I’m trying to create a view but gets the following error/warning: ORA-04063 View XXXXX has errors.

I have googled a bit and it seams that this error ocurrs when something that is inaccessible or non existans is referenced in the view query. However, the query executes fine when i run it in a query windows in Oracle SQL Developer.

Any ideas? Can it be that I need a perticular grant in order to run a query in a view?

Answers

Are you sure the query executes correctly with the same user? You only need SELECT privilege on the table, and the GRANT CREATE VIEW.

Maybe you have an invalid column name in the create view statement.

>
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.
>

There is a colum/table to which your user doen’t have access to. Execute the query as user you are trying to create the view. Make sure your user has been granted create view

When I execute the query (with the same user) i do get a nice result.

What would be an invalid columnname? and why would i be diffrent when executed in a view?

Источник

ORA-04063: table

On 11.2.04 on UNIX

I have the following invalide object:

select object_name,object_type, status, owner from dba_objects where OBJECT_NAME=’AZ_COMP_REPORTER’;

OBJECT_NAME OBJECT_TYPE STATUS OWNER

AZ_COMP_REPORTER PACKAGE BODY INVALID APPS

Then I try to compile:

SQL> alter package apps.AZ_COMP_REPORTER compile body;

Warning: Package Body altered with compilation errors.

Errors for PACKAGE BODY APPS.AZ_COMP_REPORTER:

126/10 PL/SQL: ORA-04063: view «AZ.AZ_DIFF_RESULTS#» has errors

I see in sql devloper:

select count(*) from AZ.AZ_DIFF_RESULTS;

Erreur SQL : ORA-04063: table «AZ.AZ_DIFF_RESULTS» has errors

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

*Action: Fix the errors and/or create referenced objects as necessary.

How to find the errors on AZ.AZ_DIFF_RESULTS table?

Best Answer

You need to use the adadmin utility to compile everything. You’ll will never succeed in trying to do it manually one object at a time.

Answers

AZ.AZ_DIFF_RESULTS does have that XMLTYPE column ATTR_DIFF, does your code work if you exclude it?

Can you the DDL for the AZ.AZ_DIFF_RESULTS table?

TABLE: AZ.AZ_DIFF_RESULTS

Object Details
Object Name: AZ_DIFF_RESULTS
Object Type: TABLE
Owner: AZ
FND Design Data: AZ.AZ_DIFF_RESULTS
Subobject Name:
Status: VALID
Storage Details
Tablespace: APPS_TS_TX_DATA
PCT Free: 10
PCT Used:
Indexes
Index Type Uniqueness Tablespace Column
SYS_IL0000394514C00019$$ LOB UNIQUE APPS_TS_TX_DATA
AZ_DIFF_RESULTS_HCD_NU2 NORMAL NONUNIQUE APPS_TS_TX_IDX HASHCODE_DETAILS
AZ_DIFF_RESULTS_NU1 NORMAL NONUNIQUE APPS_TS_TX_IDX REQUEST_ID
SOURCE
PARENT_ID
ID
Columns
Name Datatype Length Mandatory Comments
NAME VARCHAR2 (240) Entity Name
DISPLAY_NAME VARCHAR2 (240) Entity Display Name
REQUEST_ID NUMBER (15) Yes Concurrent Request ID
SOURCE VARCHAR2 (240) Yes Entity Code or Split code
TYPE NUMBER (2) Type Indicator for the record
ID NUMBER Yes Unique Sequence for the record
PARENT_ID NUMBER Parent Sequence ID
HASHCODE_DETAILS VARCHAR2 (2000) Hash of the key attributes
DEPTH NUMBER Level of Hierarchy
IS_DIFFERENT VARCHAR2 (1) Indicates if the row is different while being compared
IS_TRANSFORMED VARCHAR2 (1) Indicates if the row is transformed
SHOW_ONLY_DIFF VARCHAR2 (1) Flag to indicate that this record would be a part of only differences in the downloadable report
PARAM1 VARCHAR2 (240) For future use
PARAM2 VARCHAR2 (240) For future use
PARAM3 VARCHAR2 (240) For future use
PARAM4 VARCHAR2 (240) For future use
PARAM5 VARCHAR2 (240) For future use
ATTR_DIFF XMLTYPE (2000)
DETAIL_LOG_STATUS VARCHAR2 (255) Status of a load for a given row
DETAIL_LOG_MSG VARCHAR2 (2000) Log message of load for a given row
Query Text

Cut, paste (and edit) the following text to query this object:

Dependencies

AZ.AZ_DIFF_RESULTS references the following:

SYS STANDARD — show dependent code XMLTYPE XDB XDz4zvFeduIfngQ5MVuYuPFA==

AZ.AZ_DIFF_RESULTS is referenced by following:

AZ AZ_DIFF_RESULTS#

Источник

May 4, 2021

I got ” ORA-04063: table/view has errors ”  error in Oracle database.

ORA-04063: table/view has errors

Details of error are as follows.

ORA-04063: table/view 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.


DBMS STATS package is failing with the errors below:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4285/5 PL/SQL: Statement ignored
4285/27 PLS-00302: component 'IS_STATS_FROM_UPGRADE' must be declared
SQL> @?/rdbms/admin/utlrp.sql
SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06512: at "SYS.UTL_RECOMP", line 899
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at "SYS.UTL_RECOMP", line 260
ORA-06512: at "SYS.UTL_RECOMP", line 803
ORA-06512: at line 4




table/view has errors


This ORA-04063 errors are related with the 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.





  



                       
  






Fix the errors and/or create referenced objects as necessary.


In this case Package body for SYS.DBMS_STATS is invalid.


Compile the package body as follows.


SQL > alter package body PACKAGE_NAME compile;


Packages and Types can be validated by running catalog and catproc scripts:


$sqlplus "/as sysdba"
SQL> startup restrict
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;


Shutdown your database in the normal mode and startup with the normal mode.


If you got this error for procedure and function, you can compile it as follows.


ALTER PROCEDURE | FUNCTION|  PACKAGE [<schema>.] <name> COMPILE  [BODY]


You can compile all invalid objects running the utlrp.sql as follows.


SQL > @?/rdbms/admin/utlrp.sql


Do you want to learn Oracle Database for Beginners, then read the following articles.




Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )




  1,334 views last month,  1 views today










About Mehmet Salih Deveci







  



                       
  











I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.










Источник






fix-ORA-04063-package-body-has-errors


In Oracle database, we used to create an object even if there is a compilation error within the procedure. With the help of this great feature, a user can deploy a schema without fixing each problem with invalid objects. During these changes, there may be error take place stated as “ORA-04063: package body has errors”. 


However, if you get this error then stop worrying at all because I am going to show here how you can easily fix ora-04063 package body has errors. All the solutions mentioned here, hopefully, will be very helpful for you. You have to just follow the ways step by step and resolve ‘ora-04063 package body has errors‘ error.


But, before we proceed to the fixes for this error, let us first have a look at detailed information on ora-04063 package body has errors.


An error stated as ORA-04063 package body has errors takes place generally because two reasons; either trying to execute a stored procedure was made or trying to use a view that has errors.


Well for stored procedures, the error may be a syntax issue or references to other packages or procedures that actually do not exist.


However, for views, most probably, the problem could be a reference in the defining query of a view to the non-existent table. It can also be a table that has references to inaccessible or non-existent types. This data can be revealed when the message is expanded in the error utility.


Symptoms of ORA-04063 Package Body Has Errors


Here are some of the common signs that shows an error stated as ORA-04063 Package Body Has Errors:





  



                       
  






The package body shows invalid in dba_objects:


 oracle error1


When you try to compile the package, it shows errors.


oracle error2


How To Fix ORA-04063 Package Body Has Errors


Here are the best ways that you can try to fix ORA-04063 package body has errors. When it comes to fix ora-04063 package body has errors, you can try PL/SQL query by itself in SQL*plus command and also contact your database administrator for assistance.


Fix #1: Run PL/SQL query by itself in SQL*plus command


The very first option you can try to fix ORA-04063 package body has errors is to run PL/SQL query by itself in SQL*Plus command. This is because, after you run this query, SQL*Plus ‘show error’ command should be given to view the reason for this error and the location of the error as well.


Syntax for PL/SQL command is as follows:


SQL > show errors


Viewing enhanced show errors command feedback, then join dba_errors and  dba_errors  so that the feedback within the lines of PL/SQL source code can easily be viewed.


CREATE FORCE VIEW command can be chosen which will help you create the view but it mark as invalid. However, you have to make sure that you recreate the view after you run this command.





  



                       
  






Let’s take an example, the package body PLOG in the schema LM_EMPLOY either does not exist or has a compilation error:


ORA-04063: package body “LM_EMPLOY.PLOG” has errors


In such a case where package errors arise, then connect to the database as LM_EMPLOY user and run the following command:


 SQL > alter package body plog compile;


After this use the “show error”command to see what errors need to be corrected. 


 SQL>show errors


Accessing an invalid view or procedure has been created with FORCE option. GRANT SELECT against the invalid view will return the ORA-04063 error.


In the given below case tab1 table does not exist. Since the FORCE option is used, the view is created. Consequently, when the GRANT SELECT is issued against this invalid view the ORA-04063 error is returned.


SQL>CREATE OR REPLACE FORCE VIEW vst.myview

AS SELECT * FROM tab1;


Warning: View created with compilation errors.





  



                       
  






SQL> GRANT SELECT ON vst.myview TO USER_A;


ORA-04063: view “VST.MYVIEW” has errors


When the view is created on an existing table the GRANT SELECT does not return the ORA-04063 error message.


SQL>CREATE OR REPLACE FORCE VIEW vst.myview

AS SELECT * FROM dba_tables;


View created


SQL> GRANT SELECT ON vst.myview TO USER_A;

                                         


NOTE: It is always a good practice to double-check the syntax of all your command because it is difficult to proactively prevent seeing ORA-04063. Make sure that all the mentioned dependencies are correct and all variables being referenced exist.


Fix #2: Contact Your Database Administrator For Assistance


After trying the above method to fix ora-04063 package body has errors, if you find yourself unsuccessful then another way you can try is to contact the database administrator for help. Also, always check the Oracle consultants credentials and certification to ensure that they have relevant experience and expertise to help you out of that problem.


Fix #3: Try Oracle Database File Repair Tool To Fix ORA-04063 Package Body Has Errors


Even after trying the above fixes, if you are still unable to fix ora-04063 package body has errors then you can try one Oracle File Repair Tool. This tool is able to fix all kinds of errors you get related to the Oracle database. Using this tool will help you searches all Oracle databases which are actually present in the system and it also shows the preview of the recoverable database objects.


With the help of this tool, you can restore data from both databases as well as backup files. Also, this tool saves the recovered data as Transact-SQL script. This tool is very easy to use and there is no need to have any technical knowledge to use this tool.





  



                       
  






 


Steps To Repair ORA-04063 Package Body Has Errors


You can try these below easy steps to fix ORA-04063 Package Body Has Errors using this fully featured camera:


Step 1: Search the Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases on your computer.


1


Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in the left-side pane.


2


Step 3: Click an object to see its preview.


3


Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.


4





  



                       
  






Step 5: Give the user name, password, and path of the blank database where you want to save the repaired database objects.


5




Step 6: Repairing and restoring various database objects after establishing a connection with a blank oracle database.




6


Final Verdict


While creating any object in the Oracle database, if you get an error stated as ‘ORA-04063 Package Body Has Errors’ then stop worrying now. I am saying so because, in this blog, I have tried my level best to help you out. You can easily fix ORA-04063 Package Body Has Errors using the above ways. Or, you can also try Oracle File Repair Tool to resolve the ora-04063 error.


 


This tool can help you fix ORA-04063 Package Body Has Errors easily in just a few steps. So, just try the ways and fix this issue in no time.


All the very best to you….












Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.





  



                       
  












Источник






Jdev version 11.1.1.7.1


I am using Oracle Database 10g XE & I am able to connect to the DB and retrieve all tables in HR schema. But when I am trying to create «Business components from Table» I am getting the above mentioned error. I have uninstalled Jdeveloper and once again reinstalled it. Still I am getting the same error.


Here is the description of the error :-


The following SQL statement failed :

SELECT /*OracleDictionaryQueries.ALL_ORACLE_OBJECT_QUERY(3)*/
   O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID
FROM   ALL_OBJECTS O
WHERE  O.OWNER = ?
AND    O.OBJECT_NAME LIKE ?
AND    O.OBJECT_TYPE IN (?, ?, ?)
AND    O.SUBOBJECT_NAME IS NULL
AND    O.SECONDARY = 'N'
AND    ( O.OBJECT_TYPE <> 'INDEX' OR
     (
      EXISTS (SELECT 1
              FROM   ALL_INDEXES I
              WHERE  I.OWNER = O.OWNER
              AND    I.INDEX_NAME = O.OBJECT_NAME
         AND    I.DROPPED = 'NO'
             )
     )
   )
AND    ( O.OBJECT_TYPE <> 'TRIGGER' OR
     (
      EXISTS (SELECT 1
              FROM   ALL_TRIGGERS TR
              WHERE  TR.OWNER = O.OWNER
              AND    TR.TRIGGER_NAME = O.OBJECT_NAME
              AND   (   TR.BASE_OBJECT_TYPE <> 'TABLE'
                     OR EXISTS ( SELECT 1 FROM ALL_TABLES T2
                                 WHERE  T2.OWNER = TR.TABLE_OWNER
                                 AND    T2.TABLE_NAME = TR.TABLE_NAME
                            AND    T2.DROPPED = 'NO'
                                )
                    )
             )
     )
   )
AND    ( O.OBJECT_TYPE <> 'TYPE' OR
     EXISTS (SELECT 1
              FROM   ALL_TYPES T
              WHERE  T.OWNER = O.OWNER
              AND    T.TYPE_NAME = O.OBJECT_NAME)
   )
AND    ( O.OBJECT_NAME NOT LIKE 'AQ$%' OR
     NOT EXISTS
     ( SELECT 1
       FROM   ALL_QUEUE_TABLES QT
       WHERE  O.OWNER = QT.OWNER
       AND    (   O.OBJECT_NAME = 'AQ$'||QT.queue_table
               OR O.OBJECT_NAME like 'AQ$'||QT.queue_table||'^_%' escape '^'
               OR O.OBJECT_NAME like 'AQ$^_'||QT.queue_table||'^_%' escape      '^')
      )
   )
UNION ALL
SELECT /*OracleDictionaryQueries.ALL_TABLE_ORACLE_QUERY*/
   X.TABLE_NAME
,     'TABLE' OBJECT_TYPE
,     (SELECT O.OBJECT_ID
   FROM   ALL_OBJECTS O
   WHERE  O.OWNER = X.OWNER
   AND O.OBJECT_TYPE = 'TABLE'
   AND O.OBJECT_NAME = X.TABLE_NAME) OBJECT_ID
FROM (
SELECT T.TABLE_NAME, T.OWNER
FROM   ALL_TABLES T
WHERE  T.OWNER = ?
AND    T.NESTED = 'NO'
AND    T.TABLE_NAME LIKE ?
AND    T.IOT_NAME IS NULL
AND    T.SECONDARY = 'N'
AND    T.DROPPED = 'NO'
MINUS
SELECT QT.QUEUE_TABLE, QT.OWNER
FROM   ALL_QUEUE_TABLES QT
WHERE  QT.OWNER = ?
AND    QT.QUEUE_TABLE LIKE ?
MINUS
SELECT MV.MVIEW_NAME, MV.OWNER
FROM   ALL_MVIEWS MV
WHERE  MV.OWNER = ?
AND    MV.MVIEW_NAME LIKE ?
MINUS
SELECT MVL.LOG_TABLE, MVL.LOG_OWNER
FROM   ALL_SNAPSHOT_LOGS MVL
WHERE  MVL.LOG_OWNER = ?
AND    MVL.LOG_TABLE LIKE ?
MINUS
SELECT RU.TABLE_NAME, RU.OWNER
FROM   ALL_TABLES RU
WHERE  RU.TABLE_NAME LIKE 'RUPD%'
AND    RU.TEMPORARY = 'Y'
AND    RU.OWNER = ?
AND    EXISTS (SELECT 1
           FROM   ALL_TAB_COLUMNS ATC
           WHERE  ATC.OWNER = RU.OWNER
           AND    ATC.TABLE_NAME = RU.TABLE_NAME
           AND    ATC.COLUMN_NAME = 'CHANGE_VECTOR$$')
) X
UNION ALL
SELECT T.TABLE_NAME
,     'TABLE' OBJECT_TYPE
,     (SELECT O.OBJECT_ID
   FROM   ALL_OBJECTS O
   WHERE  O.OWNER = T.OWNER
   AND O.OBJECT_TYPE = 'TABLE'
   AND O.OBJECT_NAME = T.TABLE_NAME) OBJECT_ID
FROM   ALL_OBJECT_TABLES T
WHERE  T.OWNER = ?
AND    T.TABLE_NAME LIKE ?
AND    T.IOT_NAME IS NULL
AND    T.NESTED = 'NO'
AND    T.SECONDARY = 'N'
AND    T.DROPPED = 'NO'



Can someone please help me out ?






Источник






ORA-04063 means the view that you want to grant to someone else has compile error, so the database cannot grant it to other users. Let’s see an example of ORA-04063:


 SQL> conn hr/hr
 Connected.
 SQL> grant select on employees_v to sh;
 grant select on employees_v to sh
                 *
 ERROR at line 1:
 ORA-04063: view "HR.EMPLOYEES_V" has errors


The short answer to the solution of ORA-04063 is to make the view compile successfully. You can compile the view again like this:


 SQL> alter view employees_v compile;


  Warning: View altered with compilation errors.







  



                       
  






OK, we have errors, but how do we check the error?


We can query the dictionary view ALL_ERRORS to know the underlying error.


 SQL> select text from all_errors where owner = 'HR' and type = 'VIEW' and name = 'EMPLOYEES_V';


  TEXT
 --------------------------------------------------------------------------------
 ORA-02019: connection description for remote database not found




Solutions


Usually, there’re several possible causes of ORA-04063:


Missing Column


One or more columns defined in view have been dropped in the base table. This error (ORA-00904) is very common, when the base table changed its definition, the view becomes invalid because of mismatch between view columns and table columns.


 SQL> select text from all_errors where owner = 'HR' and type = 'VIEW' and name = 'EMPLOYEES_V';


  TEXT
 --------------------------------------------------------------------------------
 ORA-00904: "EMAIL": invalid identifier 





  



                       
  








As you can see, the error specifically complains about missing EMAIL column of the base table.


There’re more about How to Resolve ORA-00904 invalid identifier.


Missing Table


The base table does not exist. Apparently, it’s due to missing base table. It could be dropped or renamed. To prevent such error (ORA-00942), users should consider the dependencies before dropping or renaming a table.


 SQL> select text from all_errors where owner = 'HR' and type = 'VIEW' and name = 'EMPLOYEES_V';


  TEXT
 --------------------------------------------------------------------------------
 ORA-00942: table or view does not exist 




Additionally, you may check the content of the view.


 SQL> select text from all_views where owner = 'HR' and view_name = 'EMPLOYEES_V';


  TEXT
 --------------------------------------------------------------------------------
 select employee_id from employees where salary > 10000 




The base table could be dropped or renamed.


We have talked about the topic in How to Resolve ORA-00942: table or view does not exist.


Inaccessible DB Link


The db link is not accessible. It could be one of the following causes:


    

  • The database link does not exist. You have to create it.
    • 

  • The connect identifier defined in the database link cannot be found in tnsnames.ora.
    • 

  • The database link cannot be looked up. The solution depends on what you saw in the error message.
    • 



More Consideration


Generally speaking, ORA-04063 related to database links are not easily solved, because they involve the network condition, remote listeners and remote databases.


If there’s any TNS error, you may refer to TNSPING, How and Why. Moreover, I think you might like to know how to create and use database links and their restrictions.






Источник







  



                       
  





	









    
    
    
    



    

                
Понравилась статья? Поделить с друзьями:

        
        

























    








			

  • Код ошибки 040500003
    • 

  • Код ошибки 040400008
    • 

  • Код ошибки 0404 на шевроле лачетти
    • 

  • Код ошибки 040272
    • 

  • Код ошибки 040200000

    •