CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-apps
Shruti sharma
Shruti sharma

Reputation: 211

table not found issue in oracle database

I am running below query from sqldeveloper.

select  apps.inv_project.get_pjm_locsegs('concatenated_segments') from dual;

then I am getting below error.

enter image description here

I searched in the package and table indeed exist.

 SELECT concatenated_segment_delimiter
        FROM apps.fnd_id_flex_structures
       WHERE id_flex_code = 'MTLL'
         AND ROWNUM = 1;

am i doing something wrong?

Upvotes: 0

Views: 1090

Answers (2)

Andy Haack
Andy Haack

Reputation: 484

This package is defined as authid current_user. It either needs to be executed as the APPS database user, or have the current_schema set to apps, presumed you have granted all required privileges to the non-apps DB user you are currently logged in with.

I saw you prefixed the call to inv_project and the query on fnd_id_flex_structures with 'apps.'. Instead of prefixing every table, better ask your DBAs to create a logon trigger to change the schema for your database account, as explained here: how to set schema in oracle sql developer for oracle e business suite tables

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

I presume that SELECT statement you posted points to line #1978 (where the error was raised).

If you can select from the table on SQL level, it means that you have required privileges.

But, if you aren't the owner of that table and acquired privileges on it via role, then such privileges won't work in named stored procedures (which is your inv_project.get_pjm_locsegs function, part of the package; right?).

If that's so, you should grant those privileges directly to user which is using the table (and that's apps).

Upvotes: 2

Related Questions