Greetings!
Oracle SQL Developer Version 4.2.0.17.089
PL/SQL Release 10.1.0.5.0 - Production
Windows 7.0
I wrote a short SQL script (copied below) that does the following:
-
Drops a table
-
Creates and then populates that table
-
Does a select
-
Does a different group-by select
If I run (I select the logic), 1-3 it runs OK
But when I run 1-4 I receive a “Closed Connection” message which has the effect too of dropping the temp table.
with no other explanation
My workaround is to run 1-3 (select that logic and execute) and then run 4 (select that logic and execute)
The 2nd query (step 4) is a group by so I am unsure if this is memory related.
I have never received the "closed connection" before?
Does anyone have any ideas how to fix this issue so I can run the entire script?
Thank you for your time and help!
Annie
Logic:
DROP TABLE TMP_MAJ_MNR;
CREATE TABLE TMP_MAJ_MNR
(MAJOR_SCHL_CD NUMBER(2,0),
MAJOR_DEPT_CD NUMBER(2,0),
MAJOR_MAJR_CD NUMBER(3,0),
MAJOR_EMPHASIS_MNR_CD NUMBER(3,0),
DEGREE_CD VARCHAR2(2 BYTE) ,
DEGREE_DESCR VARCHAR2(100),
REC_TYPE VARCHAR2(40) );
INSERT INTO TMP_MAJ_MNR
SELECT DISTINCT
STD.MAJOR_SCHL_CD,
STD.MAJOR_DEPT_CD,
STD.MAJOR_MAJR_CD,
STD.MAJOR_EMPHASIS_MNR_CD,
STD.DEGREE_CD,
MAJ_MNR.DESCR100,
CASE WHEN
MAJOR_MAJR_CD <> 0 AND (DEGREE_CD is NULL OR DEGREE_CD < '30' OR DEGREE_CD >= '40')
THEN 'Major'
WHEN STD.MAJOR_MAJR_CD = 0 AND FOR_MNR.DEG_CD in ('00','90')
THEN 'Minor'
ELSE 'Neither major nor minor'
END
FROM STUDENT.STSEMMJT STD
LEFT JOIN STUDENT.STMJRMNT MAJ_MNR
ON STD.MAJOR_SCHL_CD = MAJ_MNR.SCHL_CD and
STD.MAJOR_DEPT_CD = MAJ_MNR.DEPT_CD and
STD.MAJOR_MAJR_CD = MAJ_MNR.MAJR_CD and
STD.MAJOR_EMPHASIS_MNR_CD = MAJ_MNR.EMPHASIS_MRN_CD
LEFT JOIN STUDENT.STMJMNDT FOR_MNR
ON STD.MAJOR_SCHL_CD = FOR_MNR.SCHL_CD and
STD.MAJOR_DEPT_CD = FOR_MNR.DEPT_CD and
STD.MAJOR_MAJR_CD = FOR_MNR.MAJR_CD and
STD.MAJOR_EMPHASIS_MNR_CD = FOR_MNR.EMPHASIS_MRN_CD
;
-- invalid major / minor / other
SELECT
FOR_TYP.REC_TYPE,
STD.POINTER,
DEM.IDNO,
STD.CCYY,
STD.TERM_CD,
STD.INSTRL_CD,
STD.MAJOR_SCHL_CD,
SCH.DESCR40 SCHL_DESCR40,
STD.MAJOR_DEPT_CD,
STD.MAJOR_MAJR_CD,
STD.MAJOR_EMPHASIS_MNR_CD,
STD.MAJOR_SEQ,
STD.DEGREE_CD,
FOR_TYP.DEGREE_DESCR,
STD.ADDED_POINTER,
STD.ADDED_DTIME,
STD.UPDATE_POINTER,
STD.UPDATE_DTIME,
STD.UPDATE_CTR
FROM STUDENT.STSEMMJT STD
LEFT JOIN DEMOGRAPHIC.DMINDNMT DEM
ON STD.POINTER = DEM.POINTER
LEFT JOIN COURSE.CMSCHOLC SCH
ON STD.MAJOR_SCHL_CD = SCH.CODE
LEFT JOIN TMP_MAJ_MNR FOR_TYP
ON STD.MAJOR_SCHL_CD = FOR_TYP.MAJOR_SCHL_CD and
STD.MAJOR_DEPT_CD = FOR_TYP.MAJOR_DEPT_CD and
STD.MAJOR_MAJR_CD = FOR_TYP.MAJOR_MAJR_CD and
STD.MAJOR_EMPHASIS_MNR_CD = FOR_TYP.MAJOR_EMPHASIS_MNR_CD AND
NVL(STD.DEGREE_CD,' ') = NVL(FOR_TYP.DEGREE_CD,' ')
WHERE
STD.MAJOR_SEQ =1 AND
NVL(STD.MAJOR_SCHL_CD,0) + NVL(STD.MAJOR_DEPT_CD,0) + NVL(STD.MAJOR_MAJR_CD,0) + NVL(STD.MAJOR_EMPHASIS_MNR_CD,0) <> 0 AND
TO_CHAR(STD.MAJOR_SCHL_CD,'00')||TO_CHAR(STD.MAJOR_DEPT_CD,'00')||TO_CHAR(STD.MAJOR_MAJR_CD,'000')||TO_CHAR(STD.MAJOR_EMPHASIS_MNR_CD,'000')
NOT IN
(SELECT TO_CHAR(SCHL_CD,'00')||TO_CHAR(DEPT_CD,'00')||TO_CHAR(MAJR_CD,'000')||TO_CHAR(EMPHASIS_MRN_CD,'000')
FROM STUDENT.STMJRMNT);
SELECT
FOR_TYP.REC_TYPE,
STD.POINTER,
DEM.IDNO,
STD.CCYY,
STD.TERM_CD,
STD.INSTRL_CD,
COUNT(*) MAJOR_SEQ_CT
FROM STUDENT.STSEMMJT STD
INNER JOIN TMP_MAJ_MNR FOR_TYP
ON STD.MAJOR_SCHL_CD = FOR_TYP.MAJOR_SCHL_CD and
STD.MAJOR_DEPT_CD = FOR_TYP.MAJOR_DEPT_CD and
STD.MAJOR_MAJR_CD = FOR_TYP.MAJOR_MAJR_CD and
STD.MAJOR_EMPHASIS_MNR_CD = FOR_TYP.MAJOR_EMPHASIS_MNR_CD AND
NVL(STD.DEGREE_CD,' ') = NVL(FOR_TYP.DEGREE_CD,' ')
LEFT JOIN DEMOGRAPHIC.DMINDNMT DEM
ON STD.POINTER = DEM.POINTER
WHERE
STD.MAJOR_SEQ =1
GROUP BY
FOR_TYP.REC_TYPE,
STD.POINTER,
DEM.IDNO,
STD.CCYY,
STD.TERM_CD,
STD.INSTRL_CD
HAVING COUNT(*) > 1;