Hi, I am trying to create or replace a package from SqlDeverloper client in Dev env.
But getting error: IO Error: An existing connection was forcibly closed by the remote host
However, it works for another env, eg. Prod.
And it also works in Dev App Server via SqlPlus.
And more confuse, it's not having issue for all packages, only some.
See following, it works in first statement, but not second one with just a simple loop statement added:
SQLcl: Release 19.4 Production on Sun Nov 08 15:18:42 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Username? (''?) bslowner/bslowner@stlwolora16.bluescopesteel.net:1521/MLOD
Last Successful login time: Sun Nov 08 2020 15:19:01 +11:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create or replace PACKAGE BODY TEST_AUTOMATED_REPORTING AS
2 PROCEDURE PK_CDL_REPORT AS
3 v_current_day VARCHAR2(100);
4 BEGIN
5 SELECT SUBSTR(UPPER(to_char(sysdate,'Day')), 0, 2) INTO v_current_day FROM dual;
6 END PK_CDL_REPORT;
7 END TEST_AUTOMATED_REPORTING;
8 /
Package Body TEST_AUTOMATED_REPORTING compiled
SQL> create or replace PACKAGE BODY TEST_AUTOMATED_REPORTING AS
2 PROCEDURE PK_CDL_REPORT AS
3 v_current_day VARCHAR2(100);
4 BEGIN
5 SELECT SUBSTR(UPPER(to_char(sysdate,'Day')), 0, 2) INTO v_current_day FROM dual;
6 for i in 1..10 loop
7 v_param_str := 'P_SOURCE_LOCATION=SAME AS~''' || sources(i) || '''';
8 end loop;
9 END PK_CDL_REPORT;
10 END TEST_AUTOMATED_REPORTING;
11 /
Error starting at line : 1 in command -
create or replace PACKAGE BODY TEST_AUTOMATED_REPORTING AS
PROCEDURE PK_CDL_REPORT AS
v_current_day VARCHAR2(100);
BEGIN
SELECT SUBSTR(UPPER(to_char(sysdate,'Day')), 0, 2) INTO v_current_day FROM dual;
for i in 1..10 loop
v_param_str := 'P_SOURCE_LOCATION=SAME AS~''' || sources(i) || '''';
end loop;
END PK_CDL_REPORT;
END TEST_AUTOMATED_REPORTING;
Error report -
IO Error: An existing connection was forcibly closed by the remote host
SQL> show error
Nov 08, 2020 3:13:48 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: oracle.dbtools.raptor.newscriptrunner.commands.ShowErrors.handleEvent(ShowErrors.java:81)
java.lang.NullPointerException
at oracle.dbtools.raptor.newscriptrunner.commands.ShowErrors.handleEvent(ShowErrors.java:81)
at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:407)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:418)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1252)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:494)
SQL>
Please help!
Many thanks in advance!
Hong
