All_tab_partitions in PL/SQL - ORA-01403: no data found

Hi Experts,
         Im writing a procedure for partition maintenance. I have an issue, the procedure executes(collects the table partition names from all_tab_partitions) fine in my own test schema  X(which has DBA rights) but when i use the procedure to execute on the tables on another schema Y.

It returns no data found error, even if the table exists.
The SQL is below.

    select partition_name
into v_max_partition_name
from dba_tab_partitions
where TABLE_NAME='TEST_TABLE_AUD'
and TABLE_OWNER='Y'
and partition_name like '%MAX%';

This above SQL works fine when ran as a SQL but within  a PL/SQL procedure provides below error
ORA-01403: no data found
shivakarthikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrjoltcolaCommented:
Perhaps you should just use USER_TAB_PARTITIONS instead.

Or ALL_TAB_PARTITIONS if you still want to use OWNER column.
0
shivakarthikAuthor Commented:
Thanks,Yes I'm using All_tab_partitions,that wat is giving me the issue. I cannot use user_tab_columns cause the owner(DBA) of the package should be able to execute in all schemas
0
mrjoltcolaCommented:
The example above uses DBA_TAB_PARTITIONS.

Anyway, the only thing I can think is that schema Y has no table named TEST_TABLE_AUD with partition names of %MAX%

Can you verify this and/or show the results?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

shivakarthikAuthor Commented:
Apologize,Ive been using all_tab_partitions.I do have a table called "TEST_TABLE_AUD"  in the schema, I do get results when i run as a SQL alone above but when i code it in the procedure it says no data found.

The procedure works fine if the owner(X) is specified as the one who owns the procedure but doesn't work for another schema(Y). Eventhough both the schema have the same tables named "TEST_TABLE_AUD" .

0
mrjoltcolaCommented:
By default, Oracle procedures execute with the permissions of the owner.

So try creating it as a DBA?
0
shivakarthikAuthor Commented:
I Did create as a DBA. The owner X is a DBA.
0
mrjoltcolaCommented:
Ah, ok, I think I understand your problem.

By default Oracle procedures execute with owner rights, not invoker rights.
Additionally, ROLES are disabled during the PL/SQL execution, so the only thing in effect are the object privileges. So you would need to do one of 2 things:

1) Define the procedure with INVOKER's rights (using AUTHID CURRENT_USER)
or
2) grant object privileges on the table itself to the user calling the proc

0
mrjoltcolaCommented:
>>I Did create as a DBA. The owner X is a DBA.

But owner of the proc (X) is not the owner of the tables (Y) and DBA is a role, so it is not in effect in the procedure. See my previous post.

To see what I mean try this. Create the below function as user X, then test it by running it AS the owner of the schema tables(Y?).

select x.partitions from dual;

If you then comment out the line AUTHID CURRENT_USER and re-create and test again, you will see that owner Y cannot see the objects because the PL/SQL is running with X's permissions. So using INVOKER's RIGHTS (IR) will let you do what you are trying to do.


CREATE OR REPLACE FUNCTION partitions RETURN SYS_REFCURSOR
  AUTHID CURRENT_USER  -- comment this line and try again the 2nd time
IS
  result SYS_REFCURSOR;
BEGIN
  open result for select table_owner, table_name, partition_name from all_tab_partitions;
  return result;
END;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shivakarthikAuthor Commented:
Awesome ... Thanks for the great help .. It works
0
shivakarthikAuthor Commented:
Clear Example ... Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.