We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

shivakarthik
shivakarthik asked
on
Medium Priority
2,562 Views
Last Modified: 2013-12-19
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
Comment
Watch Question

Top Expert 2009

Commented:
Perhaps you should just use USER_TAB_PARTITIONS instead.

Or ALL_TAB_PARTITIONS if you still want to use OWNER column.

Author

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
Top Expert 2009

Commented:
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?

Author

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" .

Top Expert 2009

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

So try creating it as a DBA?

Author

Commented:
I Did create as a DBA. The owner X is a DBA.
Top Expert 2009

Commented:
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

Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Awesome ... Thanks for the great help .. It works

Author

Commented:
Clear Example ... Thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.