Solved

nls_env parameters

Posted on 2003-11-27
2
2,766 Views
Last Modified: 2008-01-09
How to alter nls_env parameter associated with a job
0
Comment
Question by:adekunbi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 500 total points
ID: 9832325
In the view dba_jobs you can find NLS_ENV column. Of course you have to know the job number.


I could not find where to set nls_env column. seems it is recordee by the current session.
But I found an example how to alter this column in the view.

The following is an automated script to correct USER/DBA_JOBS.NLS_ENV  
inconsistencies using an individual job owner approach. Run this script in  
SQL*Plus while logged in as the job owner:
 
 
set serveroutput on;
declare
  -- vars for fetching
  v_job number;
  v_nls varchar2(4000);
  v_what varchar2(4000);
  cursor c1 is
    select job, nls_env, what from user_jobs  
    where  
    ((lower(nls_env) like '%nls_lang=%' or lower(nls_env) like '%nls_territory=%')  
      and not (lower(nls_env) like '%nls_date_forma=t%'))  
    or  
    (lower(nls_env) like '%nls_date_format=%yy%'
      and not lower(nls_env) like '%nls_date_format=%yyyy%');
  -- vars for mask parsing
  v_start number;
  v_end   number;
  v_mask  varchar(256);
  v_date  varchar(256);
  -- vars for dbms_sql
  v_cur   integer;
  v_rc    integer;
begin
  v_cur := dbms_sql.open_cursor;
  dbms_output.put_line('Non Y2K Compliant Jobs');
  dbms_output.put_line('======================');
  open c1;
  loop
    fetch c1 into v_job, v_nls, v_what;
     exit when c1%notfound;
       dbms_output.put_line('Job: '||v_job);
       -- extract the mask
       v_start := instr(lower(v_nls),'nls_date_format=');
       v_end  := instr(lower(v_nls),' nls',v_start);
       v_mask := substr(v_nls,v_start, v_end-v_start);
       dbms_output.put_line(v_mask);
       -- modify the mask (4 possible case combinations)
       v_date := substr(v_mask,instr(v_mask,'=') + 1);
       v_date := replace(v_date,'YY','RR');
       v_date := replace(v_date,'yy','rr');
       v_date := replace(v_date,'Yy','Yy');
       v_date := replace(v_date,'yY','rR');
       dbms_output.put_line('New setting: '||v_date);
       -- change the job
       dbms_sql.parse(v_cur, 'alter session set nls_date_format='||v_date, dbms_sql.v7);
       v_rc := dbms_sql.execute(v_cur);
       dbms_sql.parse(v_cur, 'begin dbms_job.what(:job,:what); end;', dbms_sql.v7);
       dbms_sql.bind_variable(v_cur,':job',v_job);
       dbms_sql.bind_variable(v_cur,':what',v_what);
       v_rc := dbms_sql.execute(v_cur);
       dbms_output.put_line('Success ...');
       dbms_output.put_line('');
  end loop;
  close c1;
  dbms_sql.close_cursor(v_cur);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
/
 

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question