| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Long-running PL/SQL function (long)
Our developers sent me a function which is running quite long to see if I could give them any advice. It is written in PL/SQL for version 9.2.0.1 of Oracle on Sun Solaris. It is going across a database link. It reads tables in one database and loads a new table in a datamart table on another box. It looks like it will currently run for four or five days to load a 140 million-row table, which is longer than our available window.
I am wondering if anyone can look at the big picture and see if there are any obvious places for improvement of this overall design. I am open to any suggestions that I can relay back to the developers.
My gratitude to anyone who can wade through this and recommend improvements.
Cherie Machler
Oracle DBA
Gelco Information Network
FUNCTION exp_rpt_sts_load (
in_src_proc_no NUMBER,
in_stt_dt DATE,
in_stop_dt DATE,
in_commit_interval NUMBER,
in_err_threshold VARCHAR2,
in_debugging BOOLEAN )
RETURN BOOLEAN IS
TYPE list_array IS VARRAY(200) OF VARCHAR2(2);
TYPE no_array IS VARRAY(200) OF NUMBER(10);
lv_pay_sts_array list_array :=
list_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
lv_sts_cnfr_no no_array :=
no_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); CURSOR c_exp_rpt_hdr IS
SELECT a.acct_no,
a.cnfr_no,
a.arrv_dt_tm,
b.pay_type,
b.status,
b.wh_mod_dt_tm upd_dt_tm,
b.wh_date_key,
b.wh_time_key
FROM exp_rpt_hdr a,
exp_rpt_amt_type b
WHERE b.wh_mod_dt_tm BETWEEN in_stt_dt AND in_stop_dt
AND trans_type = 'R'
AND a.cnfr_no = b.cnfr_no
order by acct_no, cnfr_no;
-- c_exp_rpt_hdr storage values
lv_cnfr_no exp_rpt_hdr.cnfr_no%TYPE;
lv_acct_no acct_pay_type.acct_no%TYPE;
lv_pay_type acct_pay_type.pay_type%TYPE;
CURSOR c_exp_rpt_dtls IS
SELECT DISTINCT a.cnfr_no,
b.line_seq_no,
NVL(b.dtl_seq_no,0) dtl_seq_no,
NVL(c.alloc_seq_no,0) alloc_seq_no,
d.descr
FROM exp_rpt_line_item_hdr a,
exp_rpt_line_item_dtl b,
exp_rpt_alloc c,
acct_pay_type d
WHERE a.cnfr_no = lv_cnfr_no
AND b.pay_type = lv_pay_type
AND a.cnfr_no = b.cnfr_no
AND b.cnfr_no = c.cnfr_no(+)
AND b.line_seq_no = c.line_seq_no(+)
AND b.dtl_seq_no = c.dtl_seq_no(+)
AND d.acct_no = lv_acct_no
AND b.pay_type = d.pay_type
ORDER BY b.line_seq_no, dtl_seq_no, alloc_seq_no;
-- c_exp_rpt_dtls storage values
lv_line_seq_no exp_rpt_line_item_dtl.line_seq_no%TYPE;
lv_dtl_seq_no exp_rpt_line_item_dtl.dtl_seq_no%TYPE;
lv_82_descr acct_pay_type.descr%TYPE;
-- Row definitions
r_exp_rpt_hdr c_exp_rpt_hdr%ROWTYPE;
r_exp_rpt_dtls c_exp_rpt_dtls%ROWTYPE;
-- Miscellaneous local variables
lv_mgr_global_user_no acct_user.global_user_no%TYPE;
lv_eff_dt_in DATE;
lv_sql_code NUMBER;
lv_sql_msg VARCHAR2(256);
lv_step_txt VARCHAR2(160);
lv_err_txt VARCHAR2(320);
lv_sysdate DATE;
lv_char_SYSDATE VARCHAR2(20);
lv_handle UTL_FILE.FILE_TYPE;
lv_status BOOLEAN := TRUE;
lv_in_cnt NUMBER :=0;
lv_row_cnt NUMBER :=0;
lv_err_cnt NUMBER :=0;
lv_run_log_no INTEGER :=0;
lv_in_loop BOOLEAN;
lv_82 BOOLEAN := FALSE;
lv_pay_meth r_exp_rpt_hdr.pay_type%TYPE;
lv_pay_sts r_exp_rpt_hdr.status%TYPE;
lv_no_alloc_rec BOOLEAN;
lv_ach_amt NUMBER := 0;
loop_ctr NUMBER;
lv_chng_dt DATE;
lv_arrv_dt DATE;
lv_loop NUMBER := 0;
lv_tran_dt DATE;
-- Constants
c_proc_nm VARCHAR2(80) := 'load_edm_exp_rpt_sts';
BEGIN
-- File Control
lv_handle := WHSE_DEBUG_PKG.open_debug_log_file(c_proc_nm);
lv_err_txt := 'Process '
|| c_proc_nm
|| ', '
|| 'Runtime '
|| SYSDATE;
lv_status := WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
--Run Log Start
lv_run_log_no := GlobalError.RunLogStart2(in_src_proc_no);
SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyymmddhh24miss')
INTO lv_sysdate, lv_char_SYSDATE
FROM dual;
-- Debug Control
IF in_debugging
THEN
lv_err_txt := 'Time '
|| SYSDATE
|| lv_step_txt;
lv_status := WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
END IF;
lv_acct_no := 0;
lv_cnfr_no := 0;
lv_line_seq_no := 0;
OPEN c_exp_rpt_hdr;
LOOP
FETCH c_exp_rpt_hdr
INTO r_exp_rpt_hdr;
EXIT WHEN c_exp_rpt_hdr%NOTFOUND;
IF lv_acct_no <> r_exp_rpt_hdr.acct_no THEN
BEGIN
SELECT descr
INTO lv_82_descr
FROM acct_pay_type
WHERE pay_type = 82
AND acct_no = lv_acct_no
AND wh_mod_dt_tm = ( SELECT max(wh_mod_dt_tm)
FROM acct_pay_type
WHERE acct_no = lv_acct_no
AND pay_type = 82)
AND wh_row_eff_dt_tm = ( SELECT max(wh_row_eff_dt_tm)
FROM acct_pay_type
WHERE acct_no = lv_acct_no
AND pay_type = 82);
EXCEPTION
WHEN OTHERS THEN
lv_82_descr := 'COMPANY PAID';
END;
END IF;
IF lv_cnfr_no <> r_exp_rpt_hdr.cnfr_no THEN
IF lv_82 THEN
loop_ctr := 0;
LOOP
loop_ctr := loop_ctr +1;
IF lv_pay_sts_array(loop_ctr) IS NULL THEN
EXIT;
END IF;
IF ((lv_pay_sts_array(loop_ctr) IS NOT NULL)
AND (lv_sts_cnfr_no(loop_ctr) = lv_cnfr_no))
THEN
BEGIN
INSERT INTO edm_exp_rpt_sts(
cnfr_no,
pay_meth,
line_seq_no,
dtl_seq_no,
alloc_seq_no,
pay_sts,
pay_meth_descr,
acct_no,
sts_chng_dt,
arrival_dt,
src_proc_no,
dm_load_dt,
ach_amt,
tran_dt)
VALUES(
lv_cnfr_no,
82,
lv_line_seq_no+1,
1,
0,
lv_pay_sts_array(loop_ctr),
lv_82_descr,
lv_acct_no,
lv_chng_dt,
lv_arrv_dt,
in_src_proc_no,
lv_sysdate,
lv_ach_amt,
lv_tran_dt );
lv_row_cnt := lv_row_cnt + 1;
IF MOD(lv_row_cnt,in_commit_interval) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
lv_pay_sts_array(loop_ctr) := NULL;
lv_sts_cnfr_no(loop_ctr) := NULL;
END LOOP;
loop_ctr := 1;
lv_82 := FALSE;
END IF;
lv_line_seq_no := 0;
END IF;
lv_cnfr_no := r_exp_rpt_hdr.cnfr_no;
lv_acct_no := r_exp_rpt_hdr.acct_no;
lv_pay_type := r_exp_rpt_hdr.pay_type;
lv_pay_sts := r_exp_rpt_hdr.status;
lv_chng_dt := r_exp_rpt_hdr.upd_dt_tm;
lv_arrv_dt := r_exp_rpt_hdr.arrv_dt_tm;
lv_pay_meth := r_exp_rpt_hdr.pay_type;
loop_ctr := 1;
LOOP
IF lv_pay_sts_array(loop_ctr) IS NULL THEN
lv_pay_sts_array(loop_ctr) := lv_pay_sts;
lv_sts_cnfr_no(loop_ctr) := lv_cnfr_no;
EXIT;
ELSIF lv_pay_sts_array(loop_ctr) = lv_pay_sts THEN
EXIT;
ELSE
loop_ctr := loop_ctr +1;
END IF;
END LOOP;
lv_step_txt := 'Working on exp_rpt_hdr Acct_no :'
|| r_exp_rpt_hdr.acct_no;
IF lv_pay_type = 82 THEN
lv_82 := TRUE;
END IF;
-- Debug Control
IF in_debugging THEN
lv_err_txt := 'Time '
|| SYSDATE
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
END IF;
OPEN c_exp_rpt_dtls;
LOOP
FETCH c_exp_rpt_dtls
INTO r_exp_rpt_dtls;
EXIT WHEN c_exp_rpt_dtls%NOTFOUND;
IF r_exp_rpt_dtls.line_seq_no > lv_line_seq_no THEN
lv_line_seq_no := r_exp_rpt_dtls.line_seq_no;
END IF;
lv_step_txt := 'Working on exp_rpt_dtls cnfr_no :'
|| r_exp_rpt_dtls.cnfr_no;
-- Debug Control
IF in_debugging THEN
lv_err_txt := 'Time '
|| SYSDATE
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
END IF;
BEGIN
SELECT distinct ach_amt,
tran_dt
INTO lv_ach_amt,
lv_tran_dt
FROM ach_tran
WHERE cnfr_no = lv_cnfr_no
AND splt_pay_type = lv_pay_meth
AND status = lv_pay_sts;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_ach_amt := NULL;
lv_tran_dt := NULL;
WHEN OTHERS THEN
lv_sql_code := SQLCODE;
lv_sql_msg := SQLERRM(lv_sql_code);
lv_step_txt := 'Exception Thrown '
|| lv_sql_msg;
-- Debug Control
IF in_debugging THEN
lv_err_txt := 'Time '
|| TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
END IF;
--Log error
GlobalError.LogSQLError(in_src_proc_no,
lv_sql_code,
lv_sql_msg,
lv_step_txt);
lv_err_cnt := lv_err_cnt + 1;
IF MOD(lv_err_cnt,in_err_threshold) = 0 THEN
lv_step_txt := c_proc_nm
|| ' failed';
lv_err_txt := 'Time '
|| TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
--Run Log Stop
GlobalError.RunLogStop2(in_src_proc_no,
lv_run_log_no);
lv_status :=
WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
RETURN FALSE;
END IF;
END;
BEGIN
INSERT INTO edm_exp_rpt_sts
(cnfr_no,
pay_meth,
line_seq_no,
dtl_seq_no,
alloc_seq_no,
pay_sts,
pay_meth_descr,
acct_no,
sts_chng_dt,
arrival_dt,
src_proc_no,
dm_load_dt,
ach_amt,
tran_dt)
VALUES( r_exp_rpt_hdr.cnfr_no,
r_exp_rpt_hdr.pay_type,
r_exp_rpt_dtls.line_seq_no,
r_exp_rpt_dtls.dtl_seq_no,
r_exp_rpt_dtls.alloc_seq_no,
r_exp_rpt_hdr.status,
r_exp_rpt_dtls.descr,
r_exp_rpt_hdr.acct_no,
r_exp_rpt_hdr.upd_dt_tm,
r_exp_rpt_hdr.arrv_dt_tm,
in_src_proc_no,
lv_sysdate,
lv_ach_amt,
lv_tran_dt);
lv_row_cnt := lv_row_cnt + 1;
IF MOD(lv_row_cnt,
in_commit_interval) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
/*
COMMIT;
UPDATE edm_exp_rpt_sts
SET ach_amt = lv_ach_amt,
src_proc_no = in_src_proc_no,
dm_load_dt = lv_sysdate,
tran_dt = lv_tran_dt
WHERE cnfr_no = lv_cnfr_no
AND pay_sts = lv_pay_sts
AND pay_meth = lv_pay_meth;
COMMIT;*/
WHEN OTHERS THEN
lv_sql_code := SQLCODE;
lv_sql_msg := SQLERRM(lv_sql_code);
lv_step_txt :=
'Exception Thrown Exception report sts '
|| lv_sql_msg;
--Log error
GlobalError.LogSQLError(in_src_proc_no,
lv_sql_code,
lv_sql_msg,
lv_step_txt);
lv_err_cnt := lv_err_cnt + 1;
IF MOD(lv_err_cnt,in_err_threshold) = 0 THEN
lv_step_txt := c_proc_nm|| ' failed';
lv_err_txt := 'Time '
|| SYSDATE
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
--Run Log Stop
GlobalError.RunLogStop2(in_src_proc_no,
lv_run_log_no);
lv_status :=
WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
RETURN FALSE;
END IF;
-- Debug Control
IF in_debugging THEN
lv_err_txt := 'Time '
|| SYSDATE
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
END IF;
END;
END LOOP;
CLOSE c_exp_rpt_dtls;
END LOOP;
close c_exp_rpt_hdr;
IF lv_82 THEN
loop_ctr := 0;
LOOP
loop_ctr := loop_ctr +1;
IF lv_pay_sts_array(loop_ctr) IS NULL THEN
EXIT;
END IF;
IF ((lv_pay_sts_array(loop_ctr) IS NOT NULL)
AND (lv_sts_cnfr_no(loop_ctr) = lv_cnfr_no))
THEN
BEGIN
INSERT INTO edm_exp_rpt_sts(
cnfr_no,
pay_meth,
line_seq_no,
dtl_seq_no,
alloc_seq_no,
pay_sts,
pay_meth_descr,
acct_no,
sts_chng_dt,
arrival_dt,
src_proc_no,
dm_load_dt,
ach_amt,
tran_dt)
VALUES( r_exp_rpt_hdr.cnfr_no,
82,
lv_line_seq_no+1,
1,
0,
lv_pay_sts_array(loop_ctr),
lv_82_descr,
r_exp_rpt_hdr.acct_no,
r_exp_rpt_hdr.upd_dt_tm,
r_exp_rpt_hdr.arrv_dt_tm,
in_src_proc_no,
lv_sysdate,
lv_ach_amt,
lv_tran_dt );
lv_row_cnt := lv_row_cnt + 1;
IF MOD(lv_row_cnt,in_commit_interval) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
END LOOP;
END IF;
lv_step_txt := c_proc_nm
|| ' completed successfully';
lv_err_txt := 'Time '
|| TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
--On success, lets log our results
GlobalError.RunLogUnits2(in_src_proc_no,
lv_run_log_no,
lv_in_cnt,
lv_row_cnt,
lv_err_cnt);
--Run Log Stop - Now pass back the run log id and indicate completion
GlobalError.RunLogStop2(in_src_proc_no,
lv_run_log_no);
lv_status :=
WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
--Success, let's tell that to who called us
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
lv_sql_code := SQLCODE;
lv_sql_msg := SQLERRM(lv_sql_code);
lv_step_txt := 'Exception Thrown '
|| lv_sql_msg;
-- Debug Control
IF in_debugging THEN
lv_err_txt := 'Time '
|| TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
END IF;
--Log error
GlobalError.LogSQLError(in_src_proc_no,
lv_sql_code,
lv_sql_msg,
lv_step_txt);
lv_err_cnt := lv_err_cnt + 1;
lv_step_txt := c_proc_nm
|| ' failed';
lv_err_txt := 'Time '
|| TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
|| lv_step_txt;
lv_status :=
WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);
--Run Log Stop
GlobalError.RunLogStop2(in_src_proc_no,
lv_run_log_no);
lv_status :=
WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
RETURN FALSE;
END exp_rpt_sts_load;
SQL> desc exp_rpt_hdr
Name Null? Type ----------------------------------------- -------- ---------------------------- CNFR_NO NOT NULL NUMBER(20) WH_DATE_KEY NOT NULL NUMBER(5) WH_MOD_DT_TM NOT NULL DATE WH_ROW_STS NOT NULL VARCHAR2(1) WH_ROW_EFF_DT_TM NOT NULL DATE MOD_GLOBAL_USER_NO NOT NULL NUMBER(15) TGT_CNFR_NO NOT NULL NUMBER(20) EXP_RPT_NO NUMBER(6) TRANS_TYPE NOT NULL VARCHAR2(1) TRANS_ID NOT NULL NUMBER(38) ACCT_NO NOT NULL NUMBER(9) GLOBAL_USER_NO NOT NULL NUMBER(15) INTL_REP_NO NOT NULL NUMBER(5) SUBMIT_DT_TM NOT NULL DATE START_DT NOT NULL DATE END_DT NOT NULL DATE EXP_RPT_STATUS NOT NULL VARCHAR2(1) ACCT_REV_NO NOT NULL NUMBER(18) UPD_DT_TM NOT NULL DATE ARRV_DT_TM NOT NULL DATE VER_NO VARCHAR2(7) EXE_VER_NO NUMBER(4,2) TITLE VARCHAR2(40) PURPOSE VARCHAR2(65) MSG_IND VARCHAR2(1) PRXY_CRTN_ID NUMBER(5) PRXY_SUBM_ID NUMBER(5) EXTL_ER_ID VARCHAR2(40) EXTL_TITLE VARCHAR2(40) EXTL_APPL_NAME VARCHAR2(40) EXTL_APPL_VER_ID VARCHAR2(40) DFLT_ORG_LVL1 VARCHAR2(20) DFLT_ORG_LVL2 VARCHAR2(20) DFLT_ORG_LVL3 VARCHAR2(20) DFLT_ORG_LVL4 VARCHAR2(20) REMARKS VARCHAR2(4000) IMAGED_IND VARCHAR2(1) AUDIT_TYPE VARCHAR2(1) SQL> desc exp_rpt_amt_type Name Null? Type ----------------------------------------- -------- ---------------------------- CNFR_NO NOT NULL NUMBER(20) PAY_TYPE NOT NULL NUMBER(3) WH_DATE_KEY NOT NULL NUMBER(5) WH_TIME_KEY NOT NULL NUMBER(5) WH_MOD_DT_TM NOT NULL DATE MOD_GLOBAL_USER_NO NOT NULL NUMBER(15) AMOUNT NOT NULL NUMBER(20,2) STATUS NOT NULL VARCHAR2(1) UPD_DT_TM NOT NULL DATE SQL> desc exp_rpt_alloc Name Null? Type ----------------------------------------- -------- ---------------------------- CNFR_NO NOT NULL NUMBER(20) LINE_SEQ_NO NOT NULL NUMBER(6) DTL_SEQ_NO NOT NULL NUMBER(6) ALLOC_SEQ_NO NOT NULL NUMBER(6) WH_DATE_KEY NOT NULL NUMBER(5) WH_MOD_DT_TM NOT NULL DATE MOD_GLOBAL_USER_NO NOT NULL NUMBER(15) UPD_DT_TM NOT NULL DATE ALLOC_AMT NOT NULL NUMBER(20,2) PROJ_NO VARCHAR2(40) ORG_LVL1 VARCHAR2(20) ORG_LVL2 VARCHAR2(20) ORG_LVL3 VARCHAR2(20) ORG_LVL4 VARCHAR2(20) ORG_LVL5 VARCHAR2(20) ORG_LVL6 VARCHAR2(20) ORG_LVL7 VARCHAR2(20) ORG_LVL8 VARCHAR2(20) SQL> desc acct_pay_type Name Null? Type ----------------------------------------- -------- ---------------------------- ACCT_NO NOT NULL NUMBER(9) REV_NO NOT NULL NUMBER(20) PAY_TYPE NOT NULL NUMBER(3) WH_DATE_KEY NOT NULL NUMBER(5) WH_TIME_KEY NOT NULL NUMBER(5) WH_MOD_DT_TM NOT NULL DATE WH_ROW_STS NOT NULL VARCHAR2(1) WH_ROW_EFF_DT_TM NOT NULL DATE MOD_GLOBAL_USER_NO NOT NULL NUMBER(15) UPD_DT_TM NOT NULL DATE DESCR NOT NULL VARCHAR2(20) STATUS NOT NULL VARCHAR2(1) GL_CODE VARCHAR2(12) REIMBURSE_IND VARCHAR2(1) SQL> desc exp_rpt_line_item_dtl Name Null? Type ----------------------------------------- -------- ---------------------------- CNFR_NO NOT NULL NUMBER(20) LINE_SEQ_NO NOT NULL NUMBER(6) DTL_SEQ_NO NOT NULL NUMBER(6) WH_DATE_KEY NOT NULL NUMBER(5) WH_MOD_DT_TM NOT NULL DATE MOD_GLOBAL_USER_NO NOT NULL NUMBER(15) EXP_CAT NOT NULL NUMBER(4) PAY_TYPE NOT NULL NUMBER(3) LINE_ITEM_DT NOT NULL DATE LINE_ITEM_AMT NOT NULL NUMBER(20,2) UPD_DT_TM NOT NULL DATE CURR_RATE NUMBER(17,8) CURR_AMT NUMBER(20,2) VENDOR VARCHAR2(30) LOCATION VARCHAR2(30) PURPOSE VARCHAR2(30) EXTL_LINE_SEQ_ID VARCHAR2(40) LINE_ITEM_REMARKS VARCHAR2(4000) SQL> desc ach_tran Name Null? Type ----------------------------------------- -------- ---------------------------- CNFR_NO NOT NULL NUMBER(20) SPLT_PAY_TYPE NOT NULL VARCHAR2(3) STATUS NOT NULL VARCHAR2(1) TRACE_NO NOT NULL NUMBER(10) WH_DATE_KEY NOT NULL NUMBER(5) WH_MOD_DT_TM NOT NULL DATE WH_ROW_STS NOT NULL VARCHAR2(1) WH_ROW_EFF_DT_TM NOT NULL DATE PROC_STATUS NOT NULL VARCHAR2(1) ACCT_NO NOT NULL NUMBER(9) INTL_REP_NO NOT NULL NUMBER(5) TRAN_DT NOT NULL DATE TRAN_TYPE NOT NULL VARCHAR2(1) STLMNT_DT NOT NULL DATE UPD_DT_TM NOT NULL DATE APP_NO NUMBER(10) ACH_AMT NUMBER(20,2) RET_REAS_CD VARCHAR2(3) BNK_ABA_ROUT_NO NUMBER(10) BNK_ACCT_NO VARCHAR2(17) BNK_TRAN_CODE VARCHAR2(2) ACH_CURR_TYPE VARCHAR2(1) PAYEE_NAME VARCHAR2(35) API_STATUS VARCHAR2(3) API_SUB_STATUS VARCHAR2(1) ENTRY_CLASS_IND VARCHAR2(1) SQL> desc edm_exp_rpt_sts Name Null? Type ----------------------------------------- -------- ---------------------------- CNFR_NO NOT NULL NUMBER(18) PAY_METH NOT NULL VARCHAR2(3) LINE_SEQ_NO NOT NULL NUMBER(6) DTL_SEQ_NO NOT NULL NUMBER(6) ALLOC_SEQ_NO NOT NULL NUMBER(6) PAY_STS NOT NULL VARCHAR2(1) PAY_METH_DESCR NOT NULL VARCHAR2(25) ACCT_NO NOT NULL NUMBER(9) STS_CHNG_DT NOT NULL DATE ARRIVAL_DT NOT NULL DATE SRC_PROC_NO NOT NULL NUMBER(10) DM_LOAD_DT NOT NULL DATE ACH_AMT NUMBER(20,2) TRAN_DT DATE
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Cherie_Machler_at_gelco.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jan 07 2003 - 11:25:31 CST
 |
 |