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