create or replace function fnc_comm_history(p_loan_code number)  return sys_refcursor
as
c_ref  sys_refcursor;
begin
open c_ref for 

select a.name, a.event, a.date, b.price from table1 a, Schema-1.table2 b  where a.loan_code=b.loan_code and a.loan_code=p_loan_code;

return c_ref;
end;

So you want to change the schema for each environment?

If so, you can't do this with variables in static SQL. Here are some options you can try.

Create synonyms

create synonym tab for <schema_1>.tab;

Create these in each environment with the appropriate <schema_1> for each table. With these in place, you can omit the schema name in queries:

select * from tab;

This makes the code the same in every environment. This is probably the simplest solution in this scenario. You can get a similar effect by using views instead of synonyms if you want.

Use dynamic SQL

Here you can use a variable with the schema name to construct the SQL as needed, e.g.:

open cur for 'select ... from ' || schema_1_var || '.tab';

The downside of this approach is dynamic SQL statements are harder to debug than static SQL. There's also the risk of SQL injection if done incorrectly.

Use conditional compilation

This enables you to change which code is compiled into the database based on compiler flags. So you can state the schema name in SQL depending on the environment, e.g.:

$if $$is_dev $then
  select * from dev_schema.tab;
$elsif $$is_test $then
  select * from test_schema.tab;
$else
  select * from prod_schema.tab;
$end

This can get very fiddly; especially if you have many SQL statements that need this conditional logic. See more at https://blogs.oracle.com/connect/post/on-conditional-compilation