[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-07-07
10
Medium Priority
?
2,195 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

650 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