分类目录归档:EBS PLSQL开发

Oracle 在线重定义(普通表变更为分区表)

–背景:cux_gl_interfacebak数据量过大(cux_gl_interfacebak有主键),需按accouting_date按年分区,以下命令直接在command窗口执行即可。
–1、检查需要在线冲定义的表是否
begin
dbms_redefinition.can_redef_table(‘apps’,’cux_gl_interfacebak’,dbms_redefinition.cons_use_pk);
end;
/

或验证是否可以通过rowid方式定义

begin
–dbms_redefinition.can_redef_table(‘scott’,’tb_cablecheck_equipment_bak’,2);
dbms_redefinition.can_redef_table(‘apps’,’cux_gl_interfacebak’,dbms_redefinition.cons_use_rowid);
end;
/

–2、创建中间表
create table CUX_GL_INTERFACEBAK_1
(
source_batch_id VARCHAR2(50) not null,
source_line_id NUMBER(10),
je_group_id VARCHAR2(50) not null,
ledger_id VARCHAR2(50) not null,
accounting_date DATE not null,
process_date DATE not null,
je_category_name VARCHAR2(25) not null,
je_source_name VARCHAR2(25) not null,
currency_code VARCHAR2(15) not null,
currency_conversion_date DATE,
currency_conversion_rate NUMBER(38,2),
currency_conversion_type VARCHAR2(30),
entered_dr NUMBER(38,2),
entered_cr NUMBER(38,2),
accounted_dr NUMBER(38,2),
accounted_cr NUMBER(38,2),
actual_flag VARCHAR2(25) not null,
import_flag VARCHAR2(1) not null,
import_date VARCHAR2(25),
gl_request_id NUMBER(30),
error_message VARCHAR2(255),
doc_seq_num VARCHAR2(100),
segment1 VARCHAR2(25),
segment2 VARCHAR2(25),
segment3 VARCHAR2(25),
segment4 VARCHAR2(25),
segment5 VARCHAR2(25),
segment6 VARCHAR2(25),
segment7 VARCHAR2(25),
segment8 VARCHAR2(25),
segment9 VARCHAR2(25),
segment10 VARCHAR2(25),
segment11 VARCHAR2(25),
segment12 VARCHAR2(25),
segment13 VARCHAR2(25),
line_description VARCHAR2(240),
attribute1 VARCHAR2(25),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
source_key_id NUMBER(10) not null
)

partition by range(accounting_date)(
PARTITION tb_cablecheck_equipment_p1 VALUES LESS THAN (TO_DATE(‘2017-01-01′,’YYYY-MM-DD’)),
PARTITION tb_cablecheck_equipment_p2 VALUES LESS THAN(TO_DATE(‘2018-01-01’, ‘YYYY-MM-DD’)),
PARTITION tb_cablecheck_equipment_p3 VALUES LESS THAN(TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’)),
PARTITION tb_cablecheck_equipment_p4 VALUES LESS THAN(TO_DATE(‘2020-01-01’, ‘YYYY-MM-DD’)),
PARTITION tb_cablecheck_equipment_p5 VALUES LESS THAN(TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD’)),
PARTITION tb_cablecheck_equipment_p6 VALUES LESS THAN(MAXVALUE)
);

–3、进行冲定义命令
begin
dbms_redefinition.start_redef_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′,null,2);
end;
/

–4、复制依赖对象
declare
num_errors pls_integer;
begin
dbms_redefinition.copy_table_dependents(‘apps’, ‘CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′,
dbms_redefinition.cons_orig_params, true, true, true, true, num_errors);
end;
/

–5、同步中间表,保证数据的一致性
begin
dbms_redefinition.sync_interim_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′);
end;
/

–6、完成重定义命令
begin
dbms_redefinition.finish_redef_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′);
end;
/
–7、验证冲定义是否正常
select * from CUX_GL_INTERFACEBAK partition(tb_cablecheck_equipment_p4);

select *
from cux_gl_interfacebak partition(tb_cablecheck_equipment_p3)
where 1 = 1
and segment3 = ‘6031010101’

–8、删除表
drop table apps.CUX_GL_INTERFACEBAK_1;

Oracle PLSQL Json类接口例子

说明:此段例子程序来源公司闫同事,需要json安装文件的,可留言索取。

例子程序:

–物料LIST结果解析
DECLARE
  l_item_info json;
  l_item_list json_list;
  l_item_line json;

  l_ret_code       VARCHAR2(100);
  l_ret_msg        VARCHAR2(100);
  l_json_list_data json_list;
  l_temp_line      json;
  l_erpcode        VARCHAR2(100);
  l_itemno         VARCHAR2(100);
  l_type           VARCHAR2(100);

BEGIN
  l_item_info := json();

  l_item_info.put('code',
                  '1');
  l_item_info.put('message',
                  '调用成功');

  l_item_list := json_list();

  –物料1
  l_item_line := json();
  l_item_line.put('erpCode',
                  '10000001');
  l_item_line.put('itemNo',
                  '22222222222');
  l_item_line.put('type',
                  '4');
  dbms_output.put_line(json_ext.get_string(l_item_line,
                                           'erpCode') || '是:');
  l_item_line.print;
  dbms_output.put_line('—————————–');
  l_item_list.append(l_item_line.to_json_value);

  –物料2
  l_item_line := json();
  l_item_line.put('erpCode',
                  '10000003');
  l_item_line.put('itemNo',
                  '333333333333');
  l_item_line.put('type',
                  '2');
  dbms_output.put_line(json_ext.get_string(l_item_line,
                                           'erpCode') || '是:');
  l_item_line.print;
  dbms_output.put_line('—————————–');
  l_item_list.append(l_item_line.to_json_value);

  –物料LIST构成
  dbms_output.put_line('l_item_list是:');
  l_item_list.print;
  dbms_output.put_line('—————————–');

  –返回结果整体构成
  l_item_info.put('data',
                  l_item_list.to_json_value);
  dbms_output.put_line('l_item_info是:');
  l_item_info.print;
  dbms_output.put_line('—————————–');

  –返回结果解析
  l_ret_code := json_ext.get_string(l_item_info,
                                    'code');
  l_ret_msg  := json_ext.get_string(l_item_info,
                                    'message');

  dbms_output.put_line('解析结果:');
  dbms_output.put_line('l_ret_code: ' || l_ret_code);
  dbms_output.put_line('l_ret_msg: ' || l_ret_msg);

  IF l_ret_code = '1' THEN
    l_json_list_data := json_list(l_item_info.get('data'));
 
    FOR i IN 1 .. l_json_list_data.count()
    LOOP
      l_temp_line := json(l_json_list_data.get(i));
   
      l_erpcode := json_ext.get_string(l_temp_line,
                                       'erpCode');
      l_itemno  := json_ext.get_string(l_temp_line,
                                       'itemNo');
      l_type    := json_ext.get_string(l_temp_line,
                                       'type');
   
      dbms_output.put_line('l_erpcode: ' || l_erpcode);
      dbms_output.put_line('l_itemNo: ' || l_itemno);
      dbms_output.put_line('l_type: ' || l_type);
   
    END LOOP;
 
  END IF;

END;

——————————————————————————

以下是输出结果:

10000001是:
{
  "erpCode" : "10000001",
  "itemNo" : "22222222222",
  "type" : "4"
}
—————————–
10000003是:
{
  "erpCode" : "10000003",
  "itemNo" : "333333333333",
  "type" : "2"
}
—————————–
l_item_list是:
[{
  "erpCode" : "10000001",
  "itemNo" : "22222222222",
  "type" : "4"
}, {
  "erpCode" : "10000003",
  "itemNo" : "333333333333",
  "type" : "2"
}]
—————————–
l_item_info是:
{
  "code" : "1",
  "message" : "\u8C03\u7528\u6210\u529F",
  "data" : [{
    "erpCode" : "10000001",
    "itemNo" : "22222222222",
    "type" : "4"
  }, {
    "erpCode" : "10000003",
    "itemNo" : "333333333333",
    "type" : "2"
  }]
}
—————————–
解析结果:
l_ret_code: 1
l_ret_msg: 调用成功
l_erpcode: 10000001
l_itemNo: 22222222222
l_type: 4
l_erpcode: 10000003
l_itemNo: 333333333333
l_type: 2

Oracle创建Job命令

–获取时间和序列
create sequence numbincrease_s;
create table machineTime(seq number,mtime date);

–存储过程
create or replace procedure getMachineTime as
begin
  insert into machineTime values (numbincrease_s.nextval,sysdate);
  commit;
end;

–定义jobs调用上述存储过程,为了看出效果一分钟执行一次
declare
  jobs number;
begin
  sys.dbms_job.submit(jobs, 'getMachineTime;', sysdate, 'sysdate+1/1440');
  commit;
end;

–删除Job
/*begin
  –select job from user_jobs;
  sys.dbms_job.remove(108); –job_id
  commit;
end;*/

–select * from dba_jobs;
–select * from all_jobs;
–select * from user_jobs;

–下面是带参数的存储过程的调用
/*begin
  sys.dbms_job.submit(job => :job,
                      what => 'declare s1 varchar2(200); s2 varchar2(200); begin xxx.xxxxxx(s1,s2); end;',
                      next_date => sysdate,
                      interval => 'sysdate+1/1440');
  commit;
end;*/
–调用系统定义的存储过程类似上述方式。