Alter Session Not working in Oracle job and procedure

[jaieesh]

Hi ,
I need to run a monthly job in oracle which takes data from a remote database and fills it into my database.
To Connect to the remote database I have a db link created. Even after
the creation of dblink the table could not be accessed because of name issues in dblink and database name.

Hence I added the statement " alter session set global_names='false' " before my insert queries . When I run it as a script it works fine and fetches the data.

The following works as a script:

alter session set global_names='false';
select * from abc.domainname.com

My problem is when I put these statements in a job and a procedure alter session doesn't seem to work.

The only modication I made for it to run in a job is

execute immediate ' alter session set global_names='false' '
select * from abc.domainname.com


This gives me the same previous error which came before adding the alter session in the script.


Kindly suggest how to tackle this................

[davey23uk]

and what happens ....

[PAVB]

execute immediate ' alter session set global_names='false' '

Syntax error.

Check your syntax...

select 'alter session set global_names='false''
from    dual;

Now check this syntax...

select 'alter session set global_names='''||'false'||''''
from    dual;

[jaieesh]

@davey : I get the following error

ORA-04052: error occurred when looking up remote object CHGMGT.ALL_AOTS_CRS@IC3.STC.COM
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 CHGMGT.ALL_AOTS_CRS@ic3.stc.com 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 CHGMGT.ALL_AOTS_CRS@ic3.stc.com 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 CHGMGT.ALL_AOTS_CRS@ic3.stc.com 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 CHGMGT.ALL_AOTS_CRS@ic3.stc.com 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 CHGMGT.ALL_AOTS_CRS@ic3.stc.com 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......

[PAVB]

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 CHGMGT.ALL_AOTS_CRS@ic3.stc.com 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;

Jaieesh --

What I gave you was a way to test if syntax was correct or not.

What you have to include in your code is:
execute immediate 'alter session set global_names='''||'false'||''''

[jaieesh]

@PAVB

Syntax of alter session was ok. Changed it to as suggested by you.

CREATE OR REPLACE PROCEDURE CHM_KPI_AUTO
IS
v_cnt number;
BEGIN
v_cnt:=0;
execute immediate 'alter session set global_names='''||'false'||'''' ;
select count(*) as KPI_1_APR_Numerator into v_cnt from CHGMGT.ALL_AOTS_CRS@ic4.sbc.com 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;


Still get the same db link error which comes when global_names is set to true.


Regards