-
Alter Session Not working in Oracle job and procedure
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................
-
-
Originally Posted by jaieesh
execute immediate ' alter session set global_names='false' '
Syntax error.
Check your syntax...
Code:
select 'alter session set global_names='false''
from dual;
Now check this syntax...
Code:
select 'alter session set global_names='''||'false'||''''
from dual;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Still get the same error.
@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......
-
Originally Posted by jaieesh
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'||''''
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
still doesnt work
@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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|