Besides, it would be easier to read your code if you read best practices and name parameters differently (such as "p_schema_name" or "par_schema_name" or "schema_name_in" or whatever those best practices suggest) because - when it is "schema_name", which is equal to ALL_OBJECTS' column name ("schema_name"), you might get confused. Actually, you got confused.

As the requirement is such that the tables will be created and the same will be dropped day end
So I required to give the privileges for the table based on the user(schema) hence seemingly guidance on this if you could really assist then it's a big treat for me thanks

This is a data warehousing project and there are many permanent tables with huge volume of data's ( 90 Million+ records)

and as a tactical solution(on performance aspect) in spite of hitting the big tables every day Around 5 to 10 table are created on daily (and the structure of the table changes as per the rules that are set in front end and these tables are created and dropped from front end only select privileges are given in oracle )

and from these tables some analytical operations are carried and shared to other schema