Alter Session Not working in Oracle job and procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Alter Session Not working in Oracle job and procedure

  1. #1
    Join Date
    Jul 2010
    Posts
    3

    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................

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and what happens ....

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jaieesh View Post
    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.

  4. #4
    Join Date
    Jul 2010
    Posts
    3

    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......

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jaieesh View Post
    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.

  6. #6
    Join Date
    Jul 2010
    Posts
    3

    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
  •  


Click Here to Expand Forum to Full Width