?
Solved

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

Posted on 2009-07-07
10
Medium Priority
?
2,156 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
0
Comment
Question by:shivakarthik
[X]
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
10 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24799678
Perhaps you should just use USER_TAB_PARTITIONS instead.

Or ALL_TAB_PARTITIONS if you still want to use OWNER column.
0
 

Author Comment

by:shivakarthik
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
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?
0
Technology Partners: 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

by:shivakarthik
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" .

0
 
LVL 40

Expert Comment

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

So try creating it as a DBA?
0
 

Author Comment

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

Expert Comment

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

0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 2000 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.


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
 

Author Comment

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

Author Closing Comment

by:shivakarthik
ID: 31600899
Clear Example ... Thanks
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

800 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