Solved

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

Posted on 2009-07-07
10
2,038 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Remove Hyphens in Oracle SQL 5 46
Salary Amount Format 13 56
Pl/SQL Query 31 62
Encryption Decryption in Oracle 12 65
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now