Forum Stats

  • 3,814,139 Users
  • 2,258,824 Discussions
  • 7,892,587 Comments

Discussions

When scheduled report runs, receive error "table or view does not exist"

user23011
user23011 Member Posts: 8
edited Nov 20, 2008 10:28AM in Discoverer
This is the first time we are playing around with scheduling reports. We can schedule a basic report without issue BUT when the report starts calling database packages it starts erroring out. We've figured out the package is being called fine but when it does an SQL query it doesn't know the scope of the table. For example if the code says 'select * from per_all_people_f' it doesn't know what per_all_people_f is. BUT if I change the code to specify the table owner 'select * from hr.per_all_people_f' then it will execute successfully.

In Discoverer we have it setup so that a generic db user (DISCO_SCHEDULE) is used when scheduled reports run. I've searched metalink and we've run Oracles scripts on setting up this user. At this point we are thinking about creating a bunch of synonyms for the tables that error out, but thought there had to be a better way.

Does anyone have any thoughts on how to solve this problem? Was their a setup step that we missed when setting up the db user DISCO_SCHEDULE?

Thanks in advance for any help received.

Marty
Tagged:

Best Answer

  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Answer ✓
    Hi,

    Your problem is that you are calling the function hr_general.decode_person_name from the DISCO_SCHEDULE user and like most of the HR packages, the hr_general package is defined with current_user rights. Therefore the package will use the privileges of the current user when the package is run.

    You can try granting privileges to the tables to the DISCO_SCHEDULE user and creating the necessary synonyms, but you will struggle to get this to work. The HR packages reference many other packages and tables, and ensuring that you have all the privileges set up will be virtually impossible.

    An alternative approach is to create a view owned by APPS which makes the calls to the hr_general package. You can also create a wrapper function that is owned by APPS and created with definer rights that calls the hr_general package. You can then call this package instead of using hr_general.

    Rod West

Answers

  • Russ Proudman
    Russ Proudman Member Posts: 2,079
    edited Nov 14, 2008 2:40PM
    You're obviously in an Oracle Apps environment so I thought you'd want to write your queries referring to the applsys or apps views instead of the underlying tables (ie: apps view might be something like: apps.per_all_people_v).

    Besides the concept of the org_id or the underlying name / structure of the oracle table changing with new releases, I thought that most all of the apps / applsys views had pre-defined synonyms in Oracle Apps so all that work would be done for you as well. For example using the previous example they could well be a synonym called: per_all_people_v as it's an Apps view (in my example, maybe not in fact) and what you're describing already exists.

    So you might want to look into that first.

    However, to answer your direct question - I cannot see a way of referring to tables without either the schema name prefix it or a database synonym already being set up no matter what the tools (ie: whether Discoverer, Forms or Reports).

    Just my take.

    Russ
    -----------------------------

    Actually one more thing after re-reading. You refer to a database user you set up for scheduling so I take it your not referring to the Discoverer scheduler? but instead you're running reports from the .bat file option calling Discoverer Desktop?

    So the first things to find out are:
    1. what version and flavor of Discoverer are you running (ie: desktop 10.1.2, plus 10.1.3?, etc.)
    2. are you scheduling via the Discoverer built-in scheduler or a Desktop bat file option
    3. are you referring to Oracle Apps or not (I originally thought so because of the table name you mentioned).

    Thx.

    Russ

    Edited by: Russ Proudman on Nov 14, 2008 2:36 PM
    Russ Proudman
  • user23011
    user23011 Member Posts: 8
    To begin, thank you for your response to this...

    I am using Discoverer Plus version 10.1.2.54.25 and I am using the built in scheduler. We are using Discoverer to report on Oracles E-Business Suite so you guessed correctly.

    We setup the generic db account (DISCO_SCHEDULE) so that users could schedule reports and NOT have database accounts themselves. We specified which db account it gets scheduled under in the Discoverer Administrator tool.

    To be more specific on the issue, we have a lot of reports that use the package/function hr_general.decode_person_name which is an Oracle registered Discoverer function (DECODE_PERSON_NAME). In this code it is selecting from per_all_people_f & fnd_sessions.

    Does that help explain my problem better? Any further thoughts?
  • Russ Proudman
    Russ Proudman Member Posts: 2,079
    edited Nov 14, 2008 3:20PM
    Got it.

    Now knowing your situation, it sure sounds like you'll have to explicitly define synonyms to those tables.

    As a PL/SQL guy as well I have written many functions that called Oracle packages, tables, etc. and in turn my function was called from Discoverer, Apps, etc.

    That meant that I also had to grant access to select from them and create synonyms to the user who owned the function.

    So from the setup you describe it sure sounds like you have to do the same thing.


    Russ
    Russ Proudman
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Answer ✓
    Hi,

    Your problem is that you are calling the function hr_general.decode_person_name from the DISCO_SCHEDULE user and like most of the HR packages, the hr_general package is defined with current_user rights. Therefore the package will use the privileges of the current user when the package is run.

    You can try granting privileges to the tables to the DISCO_SCHEDULE user and creating the necessary synonyms, but you will struggle to get this to work. The HR packages reference many other packages and tables, and ensuring that you have all the privileges set up will be virtually impossible.

    An alternative approach is to create a view owned by APPS which makes the calls to the hr_general package. You can also create a wrapper function that is owned by APPS and created with definer rights that calls the hr_general package. You can then call this package instead of using hr_general.

    Rod West
  • Rod... Thank you very much because that did it!!! I changed my package to be defined with DEFINER rights and then added a function that was a wrapper to Oracles function in the hr_general package. Lastly changed every place in Discoverer that referred to Oracles function to now point to my wrapper function. Scheduled once again and it completed successfully!!!
  • Hi Marty
    Would you mind posting the code you created for the view, package and function? I'm working on an HR site right now and, while the issue has not come up, I know it will. If the solution is too big for a posting would you please send a copy to my email address?

    [email protected]

    Best wishes
    Michael
  • About a year ago we started moving away from Oracles views so there was very little Oracle code that we had to do this with. Frankly only 2 common packages that I've found so far. Below is one of them. The key line that Rod helped me understand is the very first one where the package is defined as DEFINER. The rest is basic code calling.

    CREATE OR REPLACE PACKAGE xxx_fnd_disco_util AUTHID DEFINER AS

    FUNCTION get_code_meaning(
    p_lookup_type VARCHAR2,
    p_lookup_code VARCHAR2)
    RETURN fnd_lookup_values.meaning%TYPE;

    END xxx_fnd_disco_util;

    CREATE OR REPLACE PACKAGE BODY xxx_fnd_disco_util AS
    FUNCTION get_code_meaning(
    p_lookup_type VARCHAR2,
    p_lookup_code VARCHAR2)
    RETURN fnd_lookup_values.meaning%TYPE IS

    BEGIN
    RETURN(hr_bis.bis_decode_lookup(p_lookup_type, p_lookup_code));
    END get_code_meaning;

    END xxx_fnd_disco_util;
This discussion has been closed.