Home » SQL & PL/SQL » SQL & PL/SQL » View Created with Compilation Errors (sql)
View Created with Compilation Errors [message #422040] Mon, 14 September 2009 01:28 Go to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Hello All,

Could any one please help me, what is the issue in this view. When i create it is giving error view created with compilation errors. I have checked my query and its running fine and getting data also. Please help me ASAP.

Please find the attachment.


Thanks and Regards
M.A.Aiyaz
  • Attachment: view2.txt
    (Size: 3.74KB, Downloaded 201 times)
Re: View Created with Compilation Errors [message #422041 is a reply to message #422040] Mon, 14 September 2009 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you specify what error(s) you got? Without that information, it is nearly impossible to help.
Re: View Created with Compilation Errors [message #422044 is a reply to message #422040] Mon, 14 September 2009 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63820
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please use SQL*Plus and copy and paste your session.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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.

Regards
Michel
Re: View Created with Compilation Errors [message #422051 is a reply to message #422040] Mon, 14 September 2009 02:07 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
are you getting this error
ORA-01730: invalid number of column names specified
Re: View Created with Compilation Errors [message #422055 is a reply to message #422051] Mon, 14 September 2009 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63820
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If so count the column names.

Regards
Michel

[Updated on: Mon, 14 September 2009 02:17]

Report message to a moderator

Re: View Created with Compilation Errors [message #422057 is a reply to message #422051] Mon, 14 September 2009 02:31 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, guessing whether it is ORA-01730 or any of other, numerous ORA- errors is pointless. It may last FOREVER, until the OP answers the question.
Re: View Created with Compilation Errors [message #422061 is a reply to message #422051] Mon, 14 September 2009 03:12 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Hi anand,

Thanks for the quick response. The number of columns are perfect. Even if i removed the column names after create view. It gives the same error.I have found out that the tables which i am using is not acessable to this schema. So, may be that was the error. Could you please let me know how to grant permision a table access to different Schema.

Thanks
Aiyaz
Re: View Created with Compilation Errors [message #422062 is a reply to message #422061] Mon, 14 September 2009 03:15 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Sorry i din't mentioned what was the error code and information


Its
ORA-04063: view "REGAL.RHC_ODRS_BY_ITEM_VL" has errors

thanks
Aiyaz
Re: View Created with Compilation Errors [message #422063 is a reply to message #422061] Mon, 14 September 2009 03:17 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
aiyaz_ma wrote on Mon, 14 September 2009 09:12
Hi anand,

Thanks for the quick response. The number of columns are perfect. Even if i removed the column names after create view. It gives the same error.I have found out that the tables which i am using is not acessable to this schema. So, may be that was the error. Could you please let me know how to grant permision a table access to different Schema.

Thanks
Aiyaz

Please have the common courtesy to answer the questions asked of you first.
Re: View Created with Compilation Errors [message #422065 is a reply to message #422063] Mon, 14 September 2009 03:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
On the off chance that the OP doesn't know how to find the error:

SELECT * FROM ALL_ERRORS WHERE NAME = 'RHC_ORDERS_BY_ITEM_VL'
Re: View Created with Compilation Errors [message #422072 is a reply to message #422061] Mon, 14 September 2009 03:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
I have found out that the tables which i am using is not acessable to this schema

that cant be the case else the
ORA-00942: table or view does not exist
error shoots up instantly

Quote:
Could you please let me know how to grant permision a table access to different Schema.

by GRANT offcourse
grant select on [table name ] to [User];


can you please copy paste your whole sql session in which you are getting the error with error as well or as suggested by Jrowbottom

[Updated on: Mon, 14 September 2009 03:54]

Report message to a moderator

Re: View Created with Compilation Errors [message #422073 is a reply to message #422065] Mon, 14 September 2009 03:54 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Thanks for the information for know the errors.

It shows
SQL> SELECT * FROM ALL_ERRORS WHERE NAME = 'RHC_ORDERS_BY_ITEM_VL';

OWNER                          NAME                           TYPE          SEQUENCE      LINE
------------------------------ ------------------------------ ------------ --------- ---------
 POSITION
---------
TEXT
----------------------------------------------------------------------------------------------------
REGAL                          RHC_ORDERS_BY_ITEM_VL          VIEW                 1         0
        0
ORA-00942: table or view does not exist

I need to give acess permission to other schema

i have used this

grant select on REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL

but its giving insufficient privellege.

Which schema should i loggin ?
I tried with system/manager

[EDITED by LF: applied [code] tags]

[Updated on: Mon, 14 September 2009 04:44] by Moderator

Report message to a moderator

Re: View Created with Compilation Errors [message #422076 is a reply to message #422073] Mon, 14 September 2009 03:59 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

SQL> SELECT * FROM ALL_ERRORS WHERE NAME = 'RHC_ORDERS_BY_ITEM_VL';

OWNER                          NAME                           TYPE          SEQUENCE      LINE
------------------------------ ------------------------------ ------------ --------- ---------
 POSITION
---------
TEXT
----------------------------------------------------------------------------------------------------
REGAL                          RHC_ORDERS_BY_ITEM_VL          VIEW                 1         0
        0
ORA-00942: table or view does not exist


SQL> ed
Wrote file afiedt.buf

  1* SELECT * FROM ALL_ERRORS WHERE NAME = 'REGAL.RHC_ORDERS_BY_ITEM_VL'
SQL> /

no rows selected

SQL> conn system/[email]manager@regapps[/email]
Connected.
SQL> grant connect,resource REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL;
grant connect,resource REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL
              *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> ed
Wrote file afiedt.buf

  1* grant connect,resource on REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL
SQL> /
grant connect,resource on REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant select,insert,update,delete on REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL;
grant select,insert,update,delete on REGAL.REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL
                                           *
ERROR at line 1:
ORA-01031: insufficient privileges

[EDITED by LF: applied [code] tags]

[Updated on: Mon, 14 September 2009 04:41] by Moderator

Report message to a moderator

Re: View Created with Compilation Errors [message #422077 is a reply to message #422076] Mon, 14 September 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try running
 grant select,insert,update,delete on REGAL_OM_EDI850_WORKTAB_TEMP to RHC_EUL;
while connected as the REGAL user.
Re: View Created with Compilation Errors [message #422078 is a reply to message #422076] Mon, 14 September 2009 04:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
connect to REGAL and grant to RHC_EUL Wink
Re: View Created with Compilation Errors [message #422080 is a reply to message #422078] Mon, 14 September 2009 04:10 Go to previous message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Thanks you all. It Worked for me. When connected to REGAL user.
Thanks for all your support and thaks to the moderator of ORACLE FAQ's.

Thanks and regards
M.A.Aiyaz
Previous Topic: Oracle PL/SQL transferring value from a cursor to a variable
Next Topic: DBA_MVIEWS Staleness showing ERROR
Goto Forum:
  


Current Time: Sat Oct 01 16:13:17 CDT 2016

Total time taken to generate the page: 0.09189 seconds