Home » Applications » Oracle Fusion Apps & E-Business Suite » PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 (Oracle 6i forms on Oracle 10g database)
 PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570040] |
Mon, 05 November 2012 01:30  |
 |
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
|
Junior Member |
|
|
Dear Forum members,
Please i need someone to assist me in solving the error posted above.Whenever i open a form that was running quite well initially it brings out the error PRE -FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502.I Opened the form to check the pre-form trigger and added the following Exception (
Exception
when others then
MESSAGE('SQLERRM: ' || SQLERRM);
to trap the area where the error is coming from and it raised the following line(a package in the database) shown in the form attached below.i have check those lines and amended it but the error keeps on coming.Please i need someone to help in assisting me solved this problem.(attached also in this form is the package where the exception is caught if that can be of any help. i have also checked and increase the size of the column lenght buts its still not working.
Package is here
CREATE OR REPLACE PACKAGE BODY pkmm_gv AS
--
/* Author : prince williams 06-05-2005
**
**
** Purpose : System Global Variables
**
** Copyright : riders Systems, 2012
**
*/
--
--
GV_USR_DB_LOGIN VARCHAR2(30);
GV_FULLNAME VARCHAR2(500);
GV_DB_SESSION NUMBER;
GV_USR_KEY NUMBER;
GV_ALRT_BTN NUMBER;
GV_CD_VAL VARCHAR2(30);
GV_CD_DESC VARCHAR2(500);
GV_AU_LOGON_KEY NUMBER;
GV_AU_UNIT_KEY NUMBER;
GV_DEPT_DM VARCHAR2(50);
GV_UNIT_ID VARCHAR2(10);
GV_CHNG_PWD_UNIT_ID VARCHAR2(10) := 'MMPWDCHG';
GV_M_YN VARCHAR2(100) := '0';
GV_CALL_MODE NUMBER := 2; -- 0 = Menu Call, 1= Form Call, 2= Call Query
GV_ROW_KEY NUMBER;
GV_USR_ACC_TIME_IN NUMBER := 0;
GV_USR_ACC_TIME_OUT NUMBER := 0;
GV_DT_FMT VARCHAR2(11) := 'DD-MON-YYYY';
GV_ERR_ID VARCHAR2(10);
GV_ERR_MSG VARCHAR2(2000);
GV_ALLOW_WKEND_YN VARCHAR2(1) := '0';
GV_ADMIN_PRIV_YN VARCHAR2(1) := '0';
GV_CHNG_PWD_YN VARCHAR2(1) := '0';
GV_CURRENT_YR NUMBER := 0;
GV_CURRENT_MONTH NUMBER := 0;
GV_MNT_MODE_YN VARCHAR2(1) := '0';
GV_USE_ACCT_YN VARCHAR2(1) := '0';
--
GV_LICENSE_KEY VARCHAR2(20) := 'yTx_87*_Dj';
GV_FAD_DEPT_DM VARCHAR2(5) := 'FAD';
GV_MMD_DEPT_DM VARCHAR2(5) := 'MMD';
GV_ADMIN_DEPT_DM VARCHAR2(5) := 'ADMIN';
GV_ETSD_DEPT_DM VARCHAR2(5) := 'ETSD';
GV_MIS_DEPT_DM VARCHAR2(5) := 'MIS';
GV_MNT_DEPT_DM VARCHAR2(5) := 'MNT';
GV_ADMIN_APPL_YN VARCHAR2(1) := '0';
--
GV_GOVT_NAME VARCHAR2(100) := 'INLAND REVENUE';
GV_GOVT_DEPT_NAME VARCHAR2(100);
--
-- Used for LOV retrievals
GV_LOV_CD VARCHAR2(60);
GV_LOV_DESC VARCHAR2(500);
--
TYPE rec_gv IS RECORD (gv_name VARCHAR2(100),
gv_value VARCHAR2(500),
gv_type VARCHAR2(1),
gv_upd_yn VARCHAR2(1));
--
TYPE tab_gv IS TABLE OF rec_gv INDEX BY BINARY_INTEGER;
--
tbl_gv tab_gv;
tbl_gv_count PLS_INTEGER;
--
FUNCTION fn_gv_posn (pi_gv_name IN VARCHAR2) RETURN NUMBER IS
v_name VARCHAR2(100) := UPPER(pi_gv_name);
v_posn tbl_gv_count%TYPE;
BEGIN
--
v_posn := 0;
--
FOR i in 1..tbl_gv_count LOOP
IF tbl_gv(i).gv_name = v_name THEN
v_posn := i;
EXIT;
END IF;
END LOOP;
--
RETURN v_posn;
--
END fn_gv_posn;
--
PROCEDURE pr_chk_gv_upd (pi_posn IN NUMBER, pi_gv_name IN VARCHAR2) IS
v_posn tbl_gv_count%TYPE := pi_posn;
v_name VARCHAR2(500) := SUBSTR(pi_gv_name,4);
BEGIN
--
IF NVL(v_posn,0) <= 0 THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000005', -- global does not exist
pi_errp1 => 'System variable '||v_name);
END IF;
IF tbl_gv (v_posn).gv_upd_yn = '1' THEN
NULL;
ELSE
pkmm_err.pr_raise_err(pi_errid => 'MM000006', --'Cannot Set Global Variable'
pi_errp1 => 'Modification',
pi_errp2 => 'of system variable',
pi_errp3 => v_name);
END IF;
--
END pr_chk_gv_upd;
--
FUNCTION fn_get_gv (pi_gv_name IN VARCHAR2) RETURN VARCHAR2 IS
--
v_name VARCHAR2(100) := UPPER(pi_gv_name);
v_value VARCHAR2(500);
v_posn tbl_gv_count%TYPE;
--
BEGIN
--
IF v_name IS NULL THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000007', pi_errp1 => 'Global variable name');
END IF;
--
v_value := NULL;
v_posn := fn_gv_posn (v_name);
--
IF v_posn > 0 THEN
v_value := tbl_gv (v_posn).gv_value;
END IF;
--
RETURN v_value;
--
END fn_get_gv;
--
--
PROCEDURE pr_reset_gv (pi_gv_name IN VARCHAR2) IS
BEGIN
pr_set_gv (pi_gv_name => pi_gv_name, pi_gv_value => NULL);
END pr_reset_gv;
--
PROCEDURE pr_set_gv (pi_gv_name IN VARCHAR2, pi_gv_value IN VARCHAR2) IS
v_name VARCHAR2(100) := UPPER(pi_gv_name);
v_value VARCHAR2(500) := SUBSTR(pi_gv_value,1,500);
v_posn tbl_gv_count%TYPE;
BEGIN
--
v_posn := fn_gv_posn (v_name);
pr_chk_gv_upd (pi_posn => v_posn, pi_gv_name => v_name);
tbl_gv(v_posn).gv_value := v_value;
--
END pr_set_gv;
--
--
PROCEDURE pr_init_gv (pi_param0 IN VARCHAR2 DEFAULT NULL,
pi_param1 IN VARCHAR2 DEFAULT NULL,
pi_param2 IN VARCHAR2 DEFAULT NULL,
pi_param3 IN VARCHAR2 DEFAULT NULL,
pi_param4 IN VARCHAR2 DEFAULT NULL,
pi_param5 IN VARCHAR2 DEFAULT NULL, -- Password
pi_param6 IN VARCHAR2 DEFAULT NULL, -- SGADMIN if applicable
pi_param7 IN VARCHAR2 DEFAULT NULL, -- Not in use
pi_param8 IN VARCHAR2 DEFAULT NULL, -- Not in use
pi_param9 IN VARCHAR2 DEFAULT NULL) IS -- Not in use
--
v_db_login VARCHAR2(100) := UPPER(pi_param0);
v_pwd VARCHAR2(100) := UPPER(pi_param5);
--
v_init_param1 VARCHAR2(500) := pi_param1;
v_init_param2 VARCHAR2(500) := pi_param2;
v_init_param3 VARCHAR2(500) := pi_param3;
--
v_init_param4 VARCHAR2(500) := pi_param4; -- Operating Department
--
your mm_x_usr%rowtype;
ur_null mm_x_usr%rowtype;
--
gr mm_x_grp%rowtype;
gr_tmp mm_x_grp%rowtype;
gr_null mm_x_grp%rowtype;
--
pr mm_s_param%rowtype;
--
v_no_grp BOOLEAN;
v_no_dept BOOLEAN;
v_out_time BOOLEAN;
v_dt DATE;
v_num NUMBER;
v_tmp_in NUMBER;
v_tmp_out NUMBER;
v_crnt_time NUMBER;
v_tmp_char VARCHAR2(30);
--
PROCEDURE pr_select_gv IS
--
CURSOR c_grp IS
SELECT *
FROM mm_x_usr_grp_x
WHERE usr_key = ur.row_key;
--
BEGIN
--
your := ur_null;
gr := gr_null;
gr_tmp := gr_null;
--
-- Parameters
--
BEGIN
SELECT *
INTO pr
FROM mm_s_param
WHERE dept_dm = v_init_param4;
EXCEPTION
WHEN no_data_found THEN
IF (v_init_param4 IN (GV_MIS_DEPT_DM, GV_ADMIN_DEPT_DM)) OR v_db_login = 'MM_ADMIN' THEN
NULL;
ELSE
pkmm_err.pr_raise_err (pi_errid => 'MM000045',
pi_errp1 => 'Parameter',
pi_errp2 => 'Please inform the System Administrator.');
END IF;
WHEN too_many_rows THEN
pkmm_err.pr_raise_err(pi_errid => 'MM000046',
pi_errp1 => 'Parameter',
pi_errp2 => 'Please inform the System Administrator.');
END;
--
IF pr.use_yn = '0' THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000029',
pi_errp1 => 'Application',
pi_errp2 => 'Please inform the System Administrator.');
END IF;
--
-- User details
BEGIN
SELECT *
INTO your
FROM mm_x_usr
WHERE UPPER(usr_db_login) = v_db_login;
--
IF v_db_login = 'MM_OWNER' THEN
RAISE no_data_found;
ELSIF v_db_login <> 'MM_ADMIN' AND ur.usr_stat_dm <> 'A' THEN
RAISE no_data_found;
END IF;
--
EXCEPTION
WHEN no_data_found THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000020');
WHEN too_many_rows THEN
pkmm_err.pr_raise_err(pi_errid => 'MM000035',
pi_errp1 => 'Your login',
pi_errp2 => 'name',
pi_errp3 => 'Please inform the System Administrator.');
END;
--
/* Ammended by prince 'williams
If MIS is independent on any department, then there is no need to have this
IF statement
*/
/*
IF v_init_param4 = GV_MIS_DEPT_DM THEN
-- MIS only runs if MAIN ACCOUNTS or CASH CONTROL is running and in use
BEGIN
SELECT '1'
INTO v_tmp_char
FROM mm_s_param
WHERE dept_dm IN (GV_MA_DEPT_DM, GV_MT_DEPT_DM)
AND use_yn = '1'
AND last_upd_yr <= crnt_yr
AND last_upd_mth IS NOT NULL;
EXCEPTION
WHEN no_data_found THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000098',
pi_errp1 => 'FINAL ACCOUNTS Or',
pi_errp2 => ' CASH CONTROL ',
pi_errp3 => 'installed or updated',
pi_errp4 => 'System not available for use.');
WHEN too_many_rows THEN
NULL;
END;
--
END IF; */
--
GV_USR_KEY := ur.row_key;
GV_DEPT_DM := v_init_param4;
--
IF v_init_param4 = GV_ADMIN_DEPT_DM THEN
GV_ADMIN_APPL_YN := '1';
ELSE
GV_ADMIN_APPL_YN := '0';
END IF;
--
IF v_db_login = 'MM_ADMIN' THEN
--
GV_ADMIN_APPL_YN := '1';
GV_ADMIN_PRIV_YN := '1';
GV_M_YN := '1';
GV_FULLNAME := 'SYSTEM ADMINISTRATOR';
GV_CHNG_PWD_YN := '0';
GV_MNT_MODE_YN := '1';
GV_USE_ACCT_YN := '0';
-- 24x7 access
GV_ALLOW_WKEND_YN := '1';
GV_USR_ACC_TIME_IN := 0;
GV_USR_ACC_TIME_OUT := 86400;
--
ELSE
--
IF ur.usr_stat_dm = 'D' THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000048',
pi_errp1 => 'Your');
ELSIF ur.usr_stat_dm = 'L' THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000031',
pi_errp1 => 'Your',
pi_errp2 => 'Locked');
ELSIF ur.usr_stat_dm <> 'A' THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000004',
pi_errp1 => 'Your Login',
pi_errp2 => 'Account Status',
pi_errp3 => 'Please inform the System Administrator.');
END IF;
--
GV_ALLOW_WKEND_YN := '0';
GV_ADMIN_PRIV_YN := '0';
GV_M_YN := '0';
GV_USR_ACC_TIME_IN := 0;
GV_USR_ACC_TIME_OUT := 0;
GV_CHNG_PWD_YN := '0';
--
GV_FULLNAME := ur.first_name||' '||ur.surname;
IF ur.title IS NOT NULL THEN
GV_FULLNAME := ur.title||' '||GV_FULLNAME;
END IF;
--
--
v_no_grp := TRUE;
v_no_dept := TRUE;
v_out_time := TRUE;
--
v_crnt_time := TO_NUMBER(TO_CHAR(SYSDATE,'SSSSS')); -- Current Time
--
FOR cr IN c_grp LOOP
--
gr_tmp := gr_null;
--
BEGIN
SELECT *
INTO gr_tmp
FROM mm_x_grp
WHERE row_key = cr.grp_key;
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN too_many_rows THEN
pkmm_err.pr_raise_err(pi_errid => 'MM000035',
pi_errp1 => 'Your Access',
pi_errp2 => 'Group Key',
pi_errp3 => 'Please inform the System Administrator.');
END;
--
IF gr_tmp.row_key = cr.grp_key THEN
--
v_no_grp := FALSE;
--
IF gr_tmp.dept_dm = v_init_param4 THEN
--
v_no_dept := FALSE;
--
IF gr_tmp.admin_yn = '1' THEN
gr.admin_yn := '1';
END IF;
IF gr_tmp.allow_wkend_yn = '1' THEN
gr.allow_wkend_yn := '1';
END IF;
IF gr_tmp.chng_days > 0 THEN
IF gr_tmp.chng_days > NVL(gr.chng_days,0) THEN
NULL;
ELSE
gr.chng_days := gr_tmp.chng_days;
END IF;
END IF;
--
-- Login and Logout time
BEGIN
v_dt := TO_DATE(gr_tmp.time_in,'hh24:mi');
v_tmp_in := TO_NUMBER(TO_CHAR(v_dt,'SSSSS'));
EXCEPTION
WHEN others THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000004',
pi_errp1 => 'User Group '||gr_tmp.grp_id,
pi_errp2 => 'Login Time',
pi_errp3 => 'Please inform the System Administrator.');
END;
--
BEGIN
v_dt := TO_DATE(gr_tmp.time_out,'hh24:mi');
v_tmp_out := TO_NUMBER(TO_CHAR(v_dt,'SSSSS'));
EXCEPTION
WHEN others THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000004',
pi_errp1 => 'User Group '||gr_tmp.grp_id,
pi_errp2 => 'Logout Time',
pi_errp3 => 'Please inform the System Administrator.');
END;
--
IF v_tmp_out < v_tmp_in THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000004',
pi_errp1 => 'User Group '||gr_tmp.grp_id,
pi_errp2 => 'Login Period',
pi_errp3 => 'Please inform the System Administrator.');
END IF;
--
IF v_crnt_time BETWEEN v_tmp_in AND v_tmp_out THEN
--
v_out_time := FALSE;
--
IF GV_USR_ACC_TIME_IN = 0 OR v_tmp_in < GV_USR_ACC_TIME_IN THEN
GV_USR_ACC_TIME_IN := v_tmp_in;
END IF;
--
IF GV_USR_ACC_TIME_OUT = 0 OR v_tmp_out > GV_USR_ACC_TIME_OUT THEN
GV_USR_ACC_TIME_OUT := v_tmp_out;
END IF;
--
END IF;
--
END IF;
--
END IF;
--
END LOOP;
--
IF v_no_grp THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000057');
ELSIF v_no_dept THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000029');
ELSIF v_out_time THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000058');
ELSIF pr.mnt_mode_yn = '1' AND NVL(gr.admin_yn,'0') <> '1' THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000036', pi_errp1 => 'Access');
END IF;
--
v_dt := pkmm_gen.fn_bus_dt;
IF (TO_CHAR(v_dt,'DAY') IN ('SUNDAY','SATURDAY')) AND
(NVL(gr.allow_wkend_yn,'0') <> '1') THEN
pkmm_err.pr_raise_err (pi_errid => 'MM000059');
END IF;
--
IF ur.last_chng_dt IS NULL THEN
GV_CHNG_PWD_YN := '1';
ELSIF gr.chng_days > 0 THEN
IF gr.chng_days <= (v_dt - ur.last_chng_dt) THEN
GV_CHNG_PWD_YN := '1';
END IF;
END IF;
--
IF gr.admin_yn = '1' THEN
GV_ADMIN_PRIV_YN := '1';
END IF;
IF gr.allow_wkend_yn = '1' THEN
GV_ALLOW_WKEND_YN := '1';
END IF;
--
GV_MNT_MODE_YN := pr.mnt_mode_yn;
--GV_USE_ACCT_YN := pr.use_acct_yn;
--
END IF;
--
--GV_CURRENT_YR := pr.crnt_yr;
--GV_CURRENT_MONTH := pr.crnt_mth;
GV_USR_DB_LOGIN := v_db_login;
--
END pr_select_gv;
--
--
PROCEDURE pr_assign (pi_gv_name IN VARCHAR2,
pi_gv_value IN VARCHAR2,
pi_gv_type IN VARCHAR2,
pi_gv_upd_yn IN VARCHAR2)IS
BEGIN
tbl_gv_count := tbl_gv_count + 1;
tbl_gv (tbl_gv_count).gv_name := pi_gv_name;
tbl_gv (tbl_gv_count).gv_value := pi_gv_value;
tbl_gv (tbl_gv_count).gv_type := pi_gv_type;
tbl_gv (tbl_gv_count).gv_upd_yn := pi_gv_upd_yn;
END pr_assign;
--
BEGIN
IF (NVL(v_db_login,'MM_OWNER') IN ('MM_OWNER','SYSTEM','SYS')) OR (v_pwd IS NULL) OR
(NVL(v_init_param1,'x') != 'x3') OR (NVL(v_init_param2,'x') != 'y6') OR
(NVL(v_init_param3,'x') != 'z9') THEN
RAISE_APPLICATION_ERROR (-20999,'You are not licensed to use this Application.');
ELSIF NVL(v_init_param4,'X') NOT IN
(GV_FAD_DEPT_DM, GV_MMD_DEPT_DM, GV_ADMIN_DEPT_DM, GV_ETSD_DEPT_DM, GV_MIS_DEPT_DM, GV_MNT_DEPT_DM) THEN
RAISE_APPLICATION_ERROR (-20999,'Unknown or Invalid Application.');
--null;
END IF;
--
BEGIN
SELECT UPPER(dept_desc)
INTO gv_govt_dept_name
FROM mm_int
WHERE dept_dm = v_init_param4
AND dept_desc IS NOT NULL;
EXCEPTION
WHEN others THEN
RAISE_APPLICATION_ERROR (-20999,'Unregistered or Invalid Application');
END;
--
tbl_gv.DELETE;
tbl_gv_count := 0;
--
pr_assign (pi_gv_name => 'GV_ERR_ID',
pi_gv_value => NULL,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_ERR_MSG',
pi_gv_value => NULL,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_ALRT_BTN',
pi_gv_value => GV_ALRT_BTN,
pi_gv_type => 'N',
pi_gv_upd_yn => '1');
--
pr_select_gv;
--
pr_assign (pi_gv_name => 'GV_ADMIN_APPL_YN',
pi_gv_value => GV_ADMIN_APPL_YN,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_DEPT_DM',
pi_gv_value => GV_DEPT_DM,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_LICENSE_KEY',
pi_gv_value => GV_LICENSE_KEY,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_GOVT_NAME',
pi_gv_value => GV_GOVT_NAME,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_GOVT_DEPT_NAME',
pi_gv_value => GV_GOVT_DEPT_NAME,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_FULLNAME',
pi_gv_value => GV_FULLNAME,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_USR_DB_LOGIN',
pi_gv_value => GV_USR_DB_LOGIN,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_USR_PWD',
pi_gv_value => v_pwd,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
GV_DB_SESSION := USERENV('sessionid');
pr_assign (pi_gv_name => 'GV_DB_SESSION',
pi_gv_value => GV_DB_SESSION,
pi_gv_type => 'N',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_USR_KEY',
pi_gv_value => GV_USR_KEY,
pi_gv_type => 'N',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_ADMIN_PRIV_YN',
pi_gv_value => GV_ADMIN_PRIV_YN,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_ALLOW_WKEND_YN',
pi_gv_value => GV_ALLOW_WKEND_YN,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_CHNG_PWD_YN',
pi_gv_value => GV_CHNG_PWD_YN,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_CD_VAL',
pi_gv_value => GV_CD_VAL,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_CD_DESC',
pi_gv_value => GV_CD_DESC,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_UNIT_ID',
pi_gv_value => GV_UNIT_ID,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
GV_AU_LOGON_KEY := pkmm_gen.fn_nxt_rkey;
pr_assign (pi_gv_name => 'GV_AU_LOGON_KEY',
pi_gv_value => GV_AU_LOGON_KEY,
pi_gv_type => 'N',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_AU_UNIT_KEY',
pi_gv_value => GV_AU_UNIT_KEY,
pi_gv_type => 'N',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_M_YN',
pi_gv_value => GV_M_YN,
pi_gv_type => 'N',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_CALL_MODE',
pi_gv_value => GV_CALL_MODE,
pi_gv_type => 'N',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_ROW_KEY',
pi_gv_value => GV_ROW_KEY,
pi_gv_type => 'N',
pi_gv_upd_yn => '1');
--
-- Earliest Allowed Time in 'SSSSS' i.e. Seconds past midnight
pr_assign (pi_gv_name => 'GV_USR_ACC_TIME_IN',
pi_gv_value => GV_USR_ACC_TIME_IN,
pi_gv_type => 'N',
pi_gv_upd_yn => '0');
--
-- Latest Allowed Time in 'SSSSS' i.e. Seconds past midnight
pr_assign (pi_gv_name => 'GV_USR_ACC_TIME_OUT',
pi_gv_value => GV_USR_ACC_TIME_OUT,
pi_gv_type => 'N',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_DT_FMT',
pi_gv_value => GV_DT_FMT,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_CHNG_PWD_UNIT_ID',
pi_gv_value => GV_CHNG_PWD_UNIT_ID,
pi_gv_type => 'V',
pi_gv_upd_yn => '0');
--
pr_assign (pi_gv_name => 'GV_LOV_CD',
pi_gv_value => GV_LOV_CD,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pr_assign (pi_gv_name => 'GV_LOV_DESC',
pi_gv_value => GV_LOV_DESC,
pi_gv_type => 'V',
pi_gv_upd_yn => '1');
--
pkmm_au.pr_au_usr_logon (pi_al_key => GV_AU_LOGON_KEY,
pi_usr_key => GV_USR_KEY,
pi_dept_dm => GV_DEPT_DM,
pi_sess_no => GV_DB_SESSION);
--
END pr_init_gv;
--
-- Finance and Account Department Code
FUNCTION fn_fad_dept RETURN VARCHAR2 IS
BEGIN
pkmm_acc.pr_logon_check;
RETURN GV_FAD_DEPT_DM;
END fn_fad_dept;
--
-- Material Management Department Code
FUNCTION fn_mmd_dept RETURN VARCHAR2 IS
BEGIN
pkmm_acc.pr_logon_check;
RETURN GV_MMD_DEPT_DM;
END fn_mmd_dept;
--
-- System Admin Dept Code
FUNCTION fn_admin_dept RETURN VARCHAR2 IS
BEGIN
pkmm_acc.pr_logon_check;
RETURN GV_ADMIN_DEPT_DM;
END fn_admin_dept;
--
-- Engineering and Technical Services Department Code
FUNCTION fn_etsd_dept RETURN VARCHAR2 IS
BEGIN
pkmm_acc.pr_logon_check;
RETURN GV_ETSD_DEPT_DM;
END fn_etsd_dept;
--
-- MIS Code
FUNCTION fn_mis_dept RETURN VARCHAR2 IS
BEGIN
pkmm_acc.pr_logon_check;
RETURN GV_MIS_DEPT_DM;
END fn_mis_dept;
--
-- Maintenance Dept Code
FUNCTION fn_mnt_dept RETURN VARCHAR2 IS
BEGIN
pkmm_acc.pr_logon_check;
RETURN GV_MNT_DEPT_DM;
END fn_mnt_dept;
--
END pkmm_gv;
/
[EDITED by LF: removed image from the message body; it is too wide and forces me to scroll the screen left/right. Applied [spoiler] tags to cut a long message shorter]
[Updated on: Wed, 07 November 2012 05:05] by Moderator Report message to a moderator |
|
|
|
| Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570046 is a reply to message #570040] |
Mon, 05 November 2012 02:19  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's one idea (possibly not the best one).
Create a table:create table test (pos number);
Create a stored procedure which will insert a record into that table. Use pragma autonomous transaction so that you could COMMIT that insert, without affecting the caller (i.e. a package you posted above), something like this:create or replace procedure p_pai_insert (par_pos in number) is
pragma autonomous_transaction;
begin
insert into test (pos) values (par_pos);
commit;
end;
Finally, edit package code and, in front of every section you find appropriate, insert call to the procedure you just created: p_pai_insert(1);
<some code here>
p_pai_insert(2);
<more code here>
p_pai_insert(3);
...
Execute the form; when the error appears, go to SQL*Plus and select from the TEST table - the highest number will point to the last code executed correctly. Remove unnecessary P_PAI_INSERT calls and, possibly, add some more; truncate the TEST table; repeat form execution until you narrow the error cause and, hopefully, fix it.
Sorry, I can't think of any smarter option right now.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570365 is a reply to message #570363] |
Thu, 08 November 2012 05:16 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
First you need to fix the package compilation error. How did you create it (which tool?). Was it SQL*Plus? If so, after you run the CREATE PACKAGE script, type SHOW ERR to see what went wrong. Here's how:
SQL> create package pkg_delme is
2 begin
3 blabla;
4 end;
5 /
Warning: Package created with compilation errors.
SQL> show err package pkg_delme
Errors for PACKAGE PKG_DELME:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
the following:
end function package pragma private procedure subtype type
use <an identifier> <a double-quoted delimited-identifier>
form current cursor
4/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
end not pragma final instantiable order overriding static
member constructor map
SQL> |
|
|
|
Goto Forum:
Current Time: Fri Jan 10 17:19:56 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
