Type Conversion inside procedure in PLSQL-CodePudding

I have two tables in Oracle. One of them is GPU_INV and the second one is GPU_FILE_HIST. I am using GPU_INV's records inside GPU_FILE_HIST and that is a final table for results.

My questions is that I have defined 2 columns in GPU_INV which are BILL_DATE (date) and DUE_DATE(date). These are date type as you see but I want to make them BILL_DATE NUM ( 6 NUM) format: YYYYMM and DUE_DATE NUM(8) format: YYYYMMDD while writing into GPU_FILE_HIST.

So how can I make this changes in my procedure?

I am adding my scripts below and I am open to any advices thank you from now.

Note that: I am storing GPU_INV's records inside of FILE_CONTENT column inside GPU_FILE_HIST.

    create table GPU_FILE_HIST
(
  file_name    VARCHAR2(50),
  file_content CLOB,
  cdate        DATE,
  cuser        VARCHAR2(100)
);

GPU_FILE_EXTRACTOR procedure scripts

CREATE OR REPLACE PROCEDURE GPU_FILE_EXTRACTOR is



vs_FILE_NAME varchar2(50);
  vb_output_is_UNIX_FILE boolean := true;
  vs_header1 varchar2(20) := 'FCBS';
  vs_header2 varchar2(250) := 'BILLNO|INVOICEID|BILLDATE|BILLACCTID|PARENTBILLACCTID|DUEDATE|CMPGID|AMOUNT|GRANDAMOUNT|PRODNUM|DISTID';
  vs_footer varchar2(100);
  vs_rowdata varchar2(1000);
  vn_RECORD_COUNT number;
  vl_FILE_CLOB CLOB;
  vs_newline varchar2(2);
BEGIN
  if vb_output_is_UNIX_FILE then
    vs_newline := CHR(10);
  else
    vs_newline := CHR(13)||CHR(10);
  end if;

 SELECT 'FCBS_INVOICE_'||to_char(sysdate,'YYYYMMDD_HHMISS') ||'.txt' into vs_FILE_NAME FROM dual;

 vl_FILE_CLOB := empty_clob();
 dbms_lob.createtemporary(lob_loc => vl_FILE_CLOB, cache => true, dur => dbms_lob.session);


  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_header1),vs_header1);
   dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);

  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_header2),vs_header2);
    dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);

 select count(1) into vn_RECORD_COUNT from FCBSADM.GPU_INV;
 for rec in (select * from FCBSADM.GPU_INV)
   loop
    vs_rowdata := rec.BILL_NO||'|'||rec.INV_ID||'|'||to_char(rec.BILL_DATE,'YYYYMM')||'|'||rec.BILL_ACCT_ID||'|'||rec.PARENT_BILL_ACCT_ID||'|'||to_char(rec.DUE_DATE,'YYYYMMDD')||'|'||rec.CMPG_ID||'|'||rec.net_AMT||'|'||rec.DUE_AMT||'|'||rec.PROD_NUM||'|'||rec.DST_ID;
     dbms_lob.writeappend(vl_FILE_CLOB, length(vs_rowdata), vs_rowdata);
        dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);
   end loop;
   vs_footer := vn_RECORD_COUNT||'|'||vs_FILE_NAME;

  dbms_lob.writeappend(vl_FILE_CLOB,  length(vs_footer),vs_footer );
     dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);

insert into FCBSADM.GPU_FILE_HIST(Cdate, File_Name, File_Content,CUSER) values (sysdate,vs_file_name, vl_FILE_CLOB, USER);
commit;
END;

CodePudding user response：

You only have to change this

vs_rowdata := rec.BILL_NO||'|'||rec.INV_ID||'|'||to_number(to_char(rec.BILL_DATE,'YYYYMM'))||'|'||rec.BILL_ACCT_ID||'|'||rec.PARENT_BILL_ACCT_ID||'|'||to_number(to_char(rec.DUE_DATE,'YYYYMMDD')|)|'|'||rec.CMPG_ID||'|'||rec.net_AMT||'|'||rec.DUE_AMT||'|'||rec.PROD_NUM||'|'||rec.DST_ID;