Wednesday, January 23, 2013

Interactive report Bug in Oracle APEX Development Tool

I had a  requirement to change the particular column name , In my interactive report i had just used the query

Select * from my_view

My_id,
MY_name,
My_status

now i changed the table and view columns of "My_status"  to "Active_status" .

Select * from my_view ; is not giving me any kind of problem from the SQL Workshop .

but the same query which is there in the Interactive report is given me problem .
 
This was very .

I looked intot he Report source of the Intearctive Report and then typed the same query again .

"Select * from My_view "

when clicked on apply changes the screen shows me a message

My_status got changed to "Active_status" .

I am unable to find , what is the reason that , IR didnot recognize the change in the column unless i touched the source again , as "Select * form my_view" is working perfectly fine in SQL workshop hence i expect the same in IR too .

So strange ..what can be the reason behind it ???? any idea ... is that a bug in ORACLE APEX DEVELOPMENT TOOL ??????

7 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. While this may not be what you expected, it is actually accurate behavior. When you enter a SQL Statement into an Interactive Report, the builder parses the SQL statement and stores the column information in the metadata. The metadata is then used at runtime to allow users to decide which columns they want to see, which to use for column breaks, etc.

    If something changes in the underlying table (or view) you need to go back and at least "touch" the statement so that the metadata can be rewritten and any new columns can be added, and any deleted columns will be removed.

    ReplyDelete
    Replies
    1. But why so ? in that case , Standard reports are better than interactive report . as they need not require to touch the report again when the db level changes are made

      Delete
  3. I think what you are expecting the new column "Active_status" to appear automatically in the IR after the above change, but the bad news is it will not happen.

    You need to run the IR and click on "Reset" option from the dropdown and will see the newly added changed column "Active_status" and don't forget to click on "Save" option and also selecting "Primary Report". Hope this helps you.

    ReplyDelete
    Replies
    1. forget about appearing in the report , the IR report is giving oracle error as column does not exists , soon i land on my page .

      The same query
      select * from my_view doesnot give me any error .

      Delete
  4. Please do not write a select as 'select *'

    The best way to write is
    select mv.my_id my_id
    ,mv.my_name my_name,
    ,mv.my_status my_status
    from my view mv

    This will bomb the report as the column name changed.
    You can then change the query to

    select mv.my_id my_id
    ,mv.my_name my_name,
    ,mv.active_status my_status
    from my view mv

    ReplyDelete
  5. Hi good_practice_dude ,

    My question was actually not to check the coding standard , but i want to check that
    when
    select * from my_view , column change doesn't give problem in Standard report , why does it give Oracle error in interactive report .


    as both should probably execute the query when the page runs , so why one is not giving error and another is giving error .

    ReplyDelete