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

Posted on 2009-07-07
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
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
Question by:shivakarthik
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 40

Expert Comment

ID: 24799678
Perhaps you should just use USER_TAB_PARTITIONS instead.

Or ALL_TAB_PARTITIONS if you still want to use OWNER column.

Author Comment

ID: 24799978
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
LVL 40

Expert Comment

ID: 24800093
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 24800182
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" .

LVL 40

Expert Comment

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

So try creating it as a DBA?

Author Comment

ID: 24800238
I Did create as a DBA. The owner X is a DBA.
LVL 40

Expert Comment

ID: 24800461
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)
2) grant object privileges on the table itself to the user calling the proc

LVL 40

Accepted Solution

mrjoltcola earned 500 total points
ID: 24800487
>>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.

  AUTHID CURRENT_USER  -- comment this line and try again the 2nd time
  open result for select table_owner, table_name, partition_name from all_tab_partitions;
  return result;

Open in new window


Author Comment

ID: 24800525
Awesome ... Thanks for the great help .. It works

Author Closing Comment

ID: 31600899
Clear Example ... Thanks

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question