Create view in different schema with errors issue.

[MarkF]

It appears that if you create or compile a view with an error in a different schema than the current one, that ODAC will raise the exception with "no errors" as the message. This appears to be because ODAC checks the user_errors view and not the all_errors view (so only current schema errors will be shown.)

Code: Select all

create force view ddl.badview as select fish from emp;
alter view ddl.badview compile;

Both statements above show "no errors". If you run the same script and remove the "ddl" schema part you should see the real error.

This is not a major issue for me by the way, but I noticed it recently.

-Mark

[bork]

Hello

I made the following view:

Code: Select all

CREATE OR REPLACE VIEW TEST_VIEW (
  ID,
  NAME,
  VALUE
)
AS
  SELECT ID,NAME,VALUE FROM TEST_TABLE WHERE ID = (SELECT ID FROM TEST_TABLE );

When I open this view from any other schema by the query:

Code: Select all

select * from TEST_SCHEMA.TEST_VIEW 

Then I get the following error message: "ORA-01427: single-row subquery returns more than one row"

Try to open your view by the SYSTEM user. Maybe your user does not have enough permissions to get the error message from the other schema.

If it doesn't help then please provide us a DDL script of your query. Probably your query generates some specific error.

Also please specify the connection mode: Direct or OCI. Also we need to know the Oracle sever and client versions.

[MarkF]

Thanks for looking into this but I think you may have misread my post. The problem is related to compile errors in a view (errors that go into the user_errors or all_errors system views.) You don't need to select from the view to show the issue as the problem doesn't relate to that. I did include a sample script that shows the problem. The main point is the "compile" part (or creating it in another schema with errors).

-Mark

[bork]

Hello

Thank you for the information. We have reproduced your issue. We will notify you as soon as we have any result.

[bork]

We have fixed problem. This fix will be included in the next ODAC build.