This discussion is archived
6 Replies Latest reply: Dec 12, 2012 6:26 AM by MaryM RSS

Views in Apex 4.1.1 is crashing the Apex engine

MaryM Newbie
Currently Being Moderated
Good Morning,

I have over 27 views in apex and made a column name change in the views and after I compile about 4 or 5 of them, the apex engine crashes. I was compiling the data views first, before moving on to report views.
When I upload the views and ran them in the as a script, it said all of the views were created.
When I went into object browser most of the views compiled. I recompiled the ones that were marked red. I compiled them in the code window, incase it threw an error, I can fix it there.

I had problems with views, before causing core dumps when I compiled them, but figured I had to compile the views in a certain order.

Any help on this is appreciated.

Using Oracle 11g
Apex 4.1.1

Thanks,
Mary
  • 1. Re: Views in Apex 4.1.1 is crashing the Apex engine
    Joel_C Pro
    Currently Being Moderated
    I know it's almost a cliche'd answer but... can you provide a bit more detail?

    Specifically with regard to "the apex engine crash[ing]" - can you describe this event more thoroughly?

    Secondly, do you have access to the DB outside of the APEX SQL Workshop? It would be useful to know if you encounter the same behaviour when running said script in either SQL*Plus or SQL Developer/TOAD/your-favourite-IDE.
    I had problems with views, before causing core dumps when I compiled them
    Concerns me...
  • 2. Re: Views in Apex 4.1.1 is crashing the Apex engine
    MaryM Newbie
    Currently Being Moderated
    I know it's almost a cliche'd answer but... can you provide a bit more detail?
    When I compile about 3 or 4 view in the code section of the view using the object browser it runs fine. It's when I go and compile another view in the code section, it will take longer to compile or freeze up and then throw an ORA-20876 error. I tell the dba whats going on and she checks and sees that the Apex engine has crashed and has to restart it.

    I do not have access to SQL Plus or anything else. I will do this on the Oracle Hosted site to see if there is any problems.

    The problem with the views before, was I was just compiling them and alot of my views use other views. So I just figured that I needed to compile the main data views, before the rest of the views, thinking that I needed to do compile them in order.

    Update:
    I uploaded 23 views that I had to change a field name in them to the Oracle Hosted site. They loaded and compiled fine. The views that didn't have any changes, there was a red mark next to them, so I recompiled them. There were no errors. Why do I have to recompile views that I didn't change?

    My Oracle Hosted Site info:
    workspace: maryc_demo
    user name: mary.carr2@usmc.mil
    p/w: camaro81


    Thanks,
    Mary

    Edited by: MaryM on Dec 11, 2012 2:48 PM

    Edited by: MaryM on Dec 11, 2012 3:53 PM
  • 3. Re: Views in Apex 4.1.1 is crashing the Apex engine
    Joel_C Pro
    Currently Being Moderated
    MaryM wrote:
    I know it's almost a cliche'd answer but... can you provide a bit more detail?
    When I compile about 3 or 4 view in the code section of the view using the object browser it runs fine. It's when I go and compile another view in the code section, it will take longer to compile or freeze up and then throw an ORA-20876 error. I tell the dba whats going on and she checks and sees that the Apex engine has crashed and has to restart it.

    I do not have access to SQL Plus or anything else. I will do this on the Oracle Hosted site to see if there is any problems.

    The problem with the views before, was I was just compiling them and alot of my views use other views. So I just figured that I needed to compile the main data views, before the rest of the views, thinking that I needed to do compile them in order.

    Update:
    I uploaded 23 views that I had to change a field name in them to the Oracle Hosted site. They loaded and compiled fine. The views that didn't have any changes, there was a red mark next to them, so I recompiled them. There were no errors. Why do I have to recompile views that I didn't change?
    As you say, if there are interlocking dependencies between the objects in the view(s), on the initial run of the script they may well be invalidated. If you run the following:
    create or replace view VW_FOO as
    select sysdate tdate from dual;
    
    
    create or replace view VW_BAR as 
    select (sysdate + 1) tdate
      from dual
     union
    select vf.tdate
      from vw_foo vf;
    
    CREATE OR REPLACE VIEW VW_FOO
    (TDATE, TDATE_2)
    AS 
    select sysdate tdate, sysdate tdate_2 from dual;
    
    View created.
    View created.
    View created.
    the View VW_BAR is invalidated, despite not being materially affected by the change to VW_FOO, on which it has a dependency - there are no errors displayed. Re-compiling VW_BAR seperately thereafter validates it.
    My Oracle Hosted Site info:
    Hello,

    I tried logging into apex.oracle.com with the details you've provided but was unable to gain access?
  • 4. Re: Views in Apex 4.1.1 is crashing the Apex engine
    MaryM Newbie
    Currently Being Moderated
    Joel,

    Here are my credentials for the Oracle hosted site.

    workspace --> maryc_demo
    u/n --> MARY.CARR2@USMC.MIL
    p/w --> camaro81
  • 5. Re: Views in Apex 4.1.1 is crashing the Apex engine
    Joel_C Pro
    Currently Being Moderated
    MaryM wrote:
    Joel,

    Here are my credentials for the Oracle hosted site.
    Hello,

    Sorry to be a pain but I'd consider removing the application and replacing it with a stripped down redacted demo version - it looks like it might contain sensitive material which might not be appropriate for a public forum such as this.

    I'd also suggest changing the username and password to something more generic.

    ...

    That aside, it's difficult to exactly judge what the main problem is (regarding the APEX engine falling over). You said before that it falls over after 4 or 5 views are created - are you basically running the same script as the one uploaded to apex.oracle.com or are you running each statement individually? I guess the question would be: is it any view that causes it to fail, or one specific view? I would consider stepping through each one individually until it fails again, then have a go at debugging that one, make the relevant amendments, then re-run the script.

    With regards to the version on apex.oracle.com, I notice that in your script, you're using the [url:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm]FORCE clause when creating the view(s). In addition, the very first view in the script references another (as yet non-existent) view. This explains the compilation errors you are encountering - the FORCE clause will create the view whether or not the underlying objects exist but will render it invalid.
    create or replace FORCE view VW_BAR as 
    select (sysdate + 1) tdate
      from dual
     union
    select vf.tdate
      from vw_foo vf;
    
    create or replace view VW_FOO as
    select sysdate tdate from dual;
    
    ...
    
    Warning: compiled but with compilation errors
    View created.
    In and of itself, I don't think this is particularly worrying, as the solution is to re-compile the invalid objects.
  • 6. Re: Views in Apex 4.1.1 is crashing the Apex engine
    MaryM Newbie
    Currently Being Moderated
    When I run the script all of the views are created. It's when I go in and some of the views have to be recompiled and is when I get the errors. I downloaded the script from apex using their ddl utility and make the changes and reload the script and run it. It adds the Force to the view, when I download it.

    The data is this application is not sensitive and is just junk data. But I will go in and change the password on it.

    I guess I should put the views in order to run and hopefully they won't crash the apex engine. Is it possible that engine is crashing because the network is very slow?

    Everything on the Oracle site ran and compiled without errors.

    Mary

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points