Home » SQL & PL/SQL » SQL & PL/SQL » View created with compilation errors & ORA-01775: looping chain of synonyms (merged) (Oracle,11.1.0.7.0,Unix)
View created with compilation errors & ORA-01775: looping chain of synonyms (merged) [message #512641] Tue, 21 June 2011 05:19 Go to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi,

I created a view in DB2 .I copied the code of view from DB3 and created in DB1. The view got created but with compilation errors.

SQL>
SQL> CREATE OR REPLACE FORCE VIEW bala.cee_efacts_pie_order_v (cenumber,
2 installationid,
3 areanumber,
4 clli,
5 servicedate,
6 forecasttype,
7 jobid,
8 shippriority,
9 installationcomment,
10 shippingnotes,
11 action,
12 ceitemnumber,
13 heci,
14 quantity,
15 frc,
16 shippingnoticenumber,
17 requestdate,
18 shipmentdate,
19 receiveddate,
20 status,
21 action_heci
22 )
23 AS
24 SELECT ord.ord_nbr, NVL (oa.efacts_ticket, 0),
25 ord.eg_gloc_lent_id || ord.eg_gloc_code, oa.clli, oa.req_srvc_date,
26 oa.forecast_type, ord.opeds_job_nbr,
27 NVL (frcoi.premium_transport, 'N'), oa.scope_of_work, NULL,
28 DECODE (oa.efacts_ticket, NULL, 'A', 'C'), frcoi.frcoi_nbr,
29 nm.mt_pid_segment3, frcoi.qty,
30 frcoi.frc_asset_type_code || frcoi.frc_wrkt_kow, 'UNKNOWN', SYSDATE,
31 oa.matl_ship_date, NULL, 'FORECAST', od.work_id_act
32 FROM bala_orders ord,
33 bala_order_appendixes oa,
34 bala_frc_order_items frcoi,
35 bala_nh_materials nm,
36 cee_ospcm_details od,
37 cee_ord_bb_items obi
38 WHERE ord.ord_nbr = oa.ord_nbr
39 AND ord.current_appndx = oa.ord_appndx
40 AND frcoi.ord_nbr = oa.ord_nbr
41 AND frcoi.ord_appndx = oa.ord_appndx
42 AND frcoi.ord_nbr = od.ord_nbr
43 AND frcoi.ord_appndx = od.ord_appndx
44 AND frcoi.frcoi_nbr = od.frcoi_nbr
45 AND nm.mt_pid = frcoi.mt_pid
46 AND frcoi.ord_nbr = obi.ord_nbr
47 AND frcoi.ord_appndx = obi.ord_appndx
48 AND frcoi.frcoi_nbr = obi.frcoi_nbr
49 AND obi.efacts_sent IS NULL
50 AND UPPER (nm.material_type) = 'PLUG-IN';

Warning: View created with compilation errors.

SQL>
SQL>
SQL>
SQL> show errors
No errors.
SQL> show error
No errors.
SQL>

I checked the status of view in user_objects , it was invalid.

How to make it valid ?


Thanks


Re: View created with compilation errors [message #512642 is a reply to message #512641] Tue, 21 June 2011 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
SELECT * FROM user_errors;



EDIT: fixed tag

[Updated on: Tue, 21 June 2011 05:23]

Report message to a moderator

Re: View created with compilation errors [message #512643 is a reply to message #512642] Tue, 21 June 2011 05:32 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member



There are so many errors with 161 rows selected.

can i save it in spreadsheet and upload it here?

Re: View created with compilation errors [message #512647 is a reply to message #512643] Tue, 21 June 2011 05:46 Go to previous messageGo to next message
ramoradba
Messages: 2453
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Add a where clause to cookie`s query with name=<VIEW_NAME>
Re: View created with compilation errors [message #512648 is a reply to message #512641] Tue, 21 June 2011 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.


[Updated on: Tue, 21 June 2011 05:54]

Report message to a moderator

Re: View created with compilation errors [message #512649 is a reply to message #512647] Tue, 21 June 2011 05:53 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

SQL>
SQL> SELECT * FROM user_errors where name='CEE_EFACTS_PIE_ORDER_V';

NAME                           TYPE           SEQUENCE       LINE   POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
CEE_EFACTS_PIE_ORDER_V         VIEW                  1          0          0
ORA-01775: looping chain of synonyms
ERROR                  0
Re: View created with compilation errors [message #512650 is a reply to message #512649] Tue, 21 June 2011 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
looping chain of synonyms

What do you think it means (if you are able to think).

Regards
Michel
Re: View created with compilation errors [message #512653 is a reply to message #512650] Tue, 21 June 2011 06:10 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member



This error may also comes when a synonym points to a no more existing object.

But this "cee_efacts_pie_order_v " exist which i created now.


Thanks
Re: View created with compilation errors [message #512656 is a reply to message #512653] Tue, 21 June 2011 06:14 Go to previous messageGo to next message
ramoradba
Messages: 2453
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Not only That
But also
Quote:
ORA-01775: looping chain of synonyms
Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1
Action: Change one synonym definition so that it applies to a base table or view and retry the operation.
Re: View created with compilation errors [message #512665 is a reply to message #512656] Tue, 21 June 2011 06:51 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Thanks,


I created the object that was missing .I then tried to create a view but this time different error.


SQL> CREATE OR REPLACE FORCE VIEW jam.cee_efacts_pie_order_v (cenumber,
  2    2                                                           installationid,
  3    3                                                           areanumber,
  4    4                                                           clli,
  5    5                                                           servicedate,
  6    6                                                           forecasttype,
  7    7                                                           jobid,
  8    8                                                           shippriority,
  9    9                                                           installationcomment,
 10   10                                                           shippingnotes,
 11   11                                                           action,
 12   12                                                           ceitemnumber,
 13   13                                                           heci,
 14   14                                                           quantity,
 15   15                                                           frc,
 16   16                                                           shippingnoticenumber,
 17   17                                                           requestdate,
 18   18                                                           shipmentdate,
 19   19                                                           receiveddate,
 20   20                                                           status,
 21   21                                                           action_heci
 22   22                                                          )
 23   23  AS
 24   24     SELECT ord.ord_nbr, NVL (oa.efacts_ticket, 0),
 25   25            ord.eg_gloc_lent_id || ord.eg_gloc_code, oa.clli, oa.req_srvc_date,
 26   26            oa.forecast_type, ord.opeds_job_nbr,
 27   27            NVL (frcoi.premium_transport, 'N'), oa.scope_of_work, NULL,
 28   28            DECODE (oa.efacts_ticket, NULL, 'A', 'C'), frcoi.frcoi_nbr,
 29   29            nm.mt_pid_segment3, frcoi.qty,
 30   30            frcoi.frc_asset_type_code || frcoi.frc_wrkt_kow, 'UNKNOWN', SYSDATE,
 31   31            oa.matl_ship_date, NULL, 'FORECAST', od.work_id_act
 32   32       FROM jam_orders ord,
 33   33            jam_order_appendixes oa,
 34   34            jam_frc_order_items frcoi,
 35   35            jam_nh_materials nm,
 36   36            cee_ospcm_details od,
 37   37            cee_ord_bb_items obi
 38   38      WHERE ord.ord_nbr = oa.ord_nbr
 39   39        AND ord.current_appndx = oa.ord_appndx
 40   40        AND frcoi.ord_nbr = oa.ord_nbr
 41   41        AND frcoi.ord_appndx = oa.ord_appndx
 42   42        AND frcoi.ord_nbr = od.ord_nbr
 43   43        AND frcoi.ord_appndx = od.ord_appndx
 44   44        AND frcoi.frcoi_nbr = od.frcoi_nbr
 45   45        AND nm.mt_pid = frcoi.mt_pid
 46   46        AND frcoi.ord_nbr = obi.ord_nbr
 47   47        AND frcoi.ord_appndx = obi.ord_appndx
 48   48        AND frcoi.frcoi_nbr = obi.frcoi_nbr
 49   49        AND obi.efacts_sent IS NULL
 50   50        AND UPPER (nm.material_type) = 'PLUG-IN';
  2                                                           installationid,
  *
ERROR at line 2:
ORA-00904: : invalid identifier


This column installationid already exist.So i think i need recompile to make the view valid. using

alter view viewname compile;


Thanks

[Updated on: Tue, 21 June 2011 06:59]

Report message to a moderator

Re: View created with compilation errors [message #512667 is a reply to message #512665] Tue, 21 June 2011 07:02 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member



This works..


Thanks all
ORA-01775: looping chain of synonyms [message #512696 is a reply to message #512641] Tue, 21 June 2011 09:31 Go to previous message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

I want to share with you the issue that i faced.

I created a view with compilation errors.here the view status was invalid.

then i checked the error using select * from user_errors where name='VIEWNAME'

It showed a error saying

ORA-01775: looping chain of synonyms

This error may also comes when a synonym points to a no more existing object.

Then after double check i found from the code that one table was missing.
I then created the table and compiled the view which turns the view to valid state.

alter view viewname compile;



Previous Topic: how to choose first row value and second row value
Next Topic: SQL-Developer and string more than 4000 char
Goto Forum:
  


Current Time: Fri Oct 31 15:16:42 CDT 2014

Total time taken to generate the page: 0.16826 seconds