Hi all,
I have upgraded from 11g to 18c Oracle DB and tried to run few programs. The error occured when trying to call whoami function and the name of the package and function is not called like in 11g. How to fix this ,should I change the PL/SQL code, DBMS_UTILITY.FORMAT_CALL_STACK, 6.Who_Called_Me or something else. I do not know. Thank you for all your help.
###############################################
PROCEDURE check_input_file (
lp_file_name_i IN VARCHAR2,
lp_file_nr_o OUT VARCHAR2)
IS
lv_check_input_file_status PLS_INTEGER;
BEGIN
lv_check_input_file_status :=
data.6.input_file_exists (whoami, lp_file_name_i);
IF lv_check_input_file_status = -1
THEN
RAISE ge_file_already_processed;
ELSIF lv_check_input_file_status > 0
THEN
lp_file_nr_o := lv_check_input_file_status;
END IF;
p.l (to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') || ' data.6.write_to_parameters: ' ||
whoami || ',INPUT_FILE,' || lp_file_name_i || ',' ||
lv_check_input_file_status ||',NULL,(check_input_file),Program Version: '||gc_version);
data.6.write_to_parameters (whoami,
'INPUT_FILE',
lp_file_name_i,
lv_check_input_file_status,
NULL);
END check_input_file;
###############################################
###############################################
CREATE OR REPLACE PROCEDURE 6.Who_Called_Me (
owner OUT VARCHAR2,
NAME OUT VARCHAR2,
lineno OUT NUMBER,
caller_t OUT VARCHAR2)
AS
/* Found at: http://govt.oracle.com/%7Etkyte/who_called_me/index.html */
call_stack VARCHAR2 (4096) DEFAULT DBMS_UTILITY.FORMAT_CALL_STACK;
n NUMBER;
found_stack BOOLEAN DEFAULT FALSE;
line VARCHAR2 (255);
cnt NUMBER := 0;
BEGIN
LOOP
n := INSTR (call_stack, CHR (10) );
EXIT WHEN (cnt = 3
OR n IS NULL
OR n = 0);
line := SUBSTR (call_stack, 1, n - 1);
call_stack := SUBSTR (call_stack, n + 1);
IF (NOT found_stack)
THEN
IF (line LIKE '%handle%number%name%')
THEN
found_stack := TRUE;
END IF;
ELSE
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
IF (cnt = 3)
THEN
-- lineno := TO_NUMBER (SUBSTR (line, 13, 6) );
line := SUBSTR (line, 21);
IF (line LIKE 'pr%')
THEN
n := LENGTH ('procedure ');
ELSIF (line LIKE 'fun%')
THEN
n := LENGTH ('function ');
ELSIF (line LIKE 'package body%')
THEN
n := LENGTH ('package body ');
ELSIF (line LIKE 'pack%')
THEN
n := LENGTH ('package ');
ELSIF (line LIKE 'anonymous%')
THEN
n := LENGTH ('anonymous block ');
ELSE
n := NULL;
END IF;
IF (n IS NOT NULL)
THEN
caller_t := LTRIM (RTRIM (UPPER (SUBSTR (line, 1, n - 1) ) ) );
ELSE
caller_t := 'TRIGGER';
END IF;
line := SUBSTR (line, NVL (n, 1) );
n := INSTR (line, '.');
owner := LTRIM (RTRIM (SUBSTR (line, 1, n - 1) ) );
lineno := TO_NUMBER (SUBSTR (owner, 1, INSTR (owner, ' ') ) );
owner := LTRIM (RTRIM (SUBSTR (owner, INSTR (owner, ' ', 1, 3) ) ) );
NAME := LTRIM (RTRIM (SUBSTR (line, n + 1) ) );
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
P.l ('ERROR: ' || SQLCODE);
P.l (SUBSTR (SQLERRM, 1, 32676) );
END Who_Called_Me;
/
###############################################