@davey : I get the following error

ORA-04052: error occurred when looking up remote object [email protected]
ORA-00604: error occurred at recursive SQL level 1
ORA-02085: database link IC3.STC.COM connects to IC3

This error was resolved when I just run as a script with Alter session set global_names='false' and I was able to fetch data , but Alter session doesnt work in a job.

@PAVB: I tried the syntax you gave me.Following is my procedure in the forms I have tried to alter the session:

Tried By me:

1.)
CREATE OR REPLACE PROCEDURE CHM_KPI_AUTO IS
v_cnt number;
BEGIN

execute immediate 'Alter session set global_name=false';
select count(*) into v_cnt from [email protected] a where
a.ITEM = 'PREAPPR' and
a.PLANNED_START_MONTH = (select UPPER(to_char(add_months(sysdate,-1), 'mon')) from dual)
and a.PLANNED_START_YEAR = (select DECODE (UPPER(to_char(add_months(sysdate,-1), 'mon')),'DEC',to_char(to_char(sysdate,'yyyy')-1),to_char(sysdate,'yyyy')) from dual);

END;

2.)


CREATE OR REPLACE PROCEDURE CHM_KPI_AUTO IS
v_cnt number;
BEGIN

execute immediate 'Alter session set global_name='false'';
select count(*) into v_cnt from [email protected] a where
a.ITEM = 'PREAPPR' and
a.PLANNED_START_MONTH = (select UPPER(to_char(add_months(sysdate,-1), 'mon')) from dual)
and a.PLANNED_START_YEAR = (select DECODE (UPPER(to_char(add_months(sysdate,-1), 'mon')),'DEC',to_char(to_char(sysdate,'yyyy')-1),to_char(sysdate,'yyyy')) from dual);

END;



As Suggested by you:
1.)
CREATE OR REPLACE PROCEDURE CHM_KPI_AUTO IS
v_cnt number;
BEGIN

execute immediate 'alter session set global_names='''||'false'||''''
;
select count(*) into v_cnt from [email protected] a where
a.ITEM = 'PREAPPR' and
a.PLANNED_START_MONTH = (select UPPER(to_char(add_months(sysdate,-1), 'mon')) from dual)
and a.PLANNED_START_YEAR = (select DECODE (UPPER(to_char(add_months(sysdate,-1), 'mon')),'DEC',to_char(to_char(sysdate,'yyyy')-1),to_char(sysdate,'yyyy')) from dual);

END;


2.)


CREATE OR REPLACE PROCEDURE CHM_KPI_AUTO IS
v_cnt number;
a_cnt number;
BEGIN

select 'alter session set global_names='''||'false'||'''' into a_cnt
from dual;
select count(*) into v_cnt from [email protected] a where
a.ITEM = 'PREAPPR' and
a.PLANNED_START_MONTH = (select UPPER(to_char(add_months(sysdate,-1), 'mon')) from dual)
and a.PLANNED_START_YEAR = (select DECODE (UPPER(to_char(add_months(sysdate,-1), 'mon')),'DEC',to_char(to_char(sysdate,'yyyy')-1),to_char(sysdate,'yyyy')) from dual);

END;




So my problem is that I need to use Alter Session in a job or a procedure but it doesn't seem to work since I get the same error which I got while executing as a script and not using alter session.

The script that runs perfectly is

Alter session set global_names=false;
select count(*) into v_cnt from [email protected] a where
a.ITEM = 'PREAPPR' and
a.PLANNED_START_MONTH = (select UPPER(to_char(add_months(sysdate,-1), 'mon')) from dual)
and a.PLANNED_START_YEAR = (select DECODE (UPPER(to_char(add_months(sysdate,-1), 'mon')),'DEC',to_char(to_char(sysdate,'yyyy')-1),to_char(sysdate,'yyyy')) from dual);




Any Help is greatly appreciated......