I have a batch file that runs multiple .sql files using sqlplus command. I'm encountering an issue with one of the .sql files getting hung up and causing me to quit the batch job.
I don't encounter an error code, but I've let the batch file run for more than 12 hours on this one part with no success. When I manually run this .sql file in sqldeveloper, it finishes in approximately 2 minutes. The sql code is an insert statement that inserts less than 400k rows with 5 columns. Originally this code was part of a bigger .sql file that had 4 insert statements inserting into the same table, but I separated each insert statement into its own file and step in the batch file to try and fix the problem. The other 3 files run with no issue and in a time similar to a manual run.
I've tried changing the order it runs, and making this code it's own separate batch file but it hasn't helped.
The code starts with a declare statement with multiple variables and then it does:
BEGIN
SELECT VARIABLE INTO DECLARED VARIABLE
FROM TABLE;
etc...
which it does for all the sql files in the batch file. It then has an IF statement(with same test as other sql files) followed with:
THEN
INSERT INTO TABLE (
SELECT VAR1,
VAR2,
CASE WHEN COLUMN1 IS NOT NULL AND COLUMN2 IN (VALUE)
THEN COLUMN1 || 'D' || RNK
ELSE COLUMN1
END AS VAR3
FROM( SUBQUERIES );
For "SUBQUERIES", it has nested subqueries that are left joined and inner joined to other subqueries. I've considered that this step might be too complex and causing the error, but not sure.
It ends the IF-THEN with a COMMIT statement, followed by:
ELSE
NULL;
END IF;
END;
The only thing that changes between the 4 files are the insert statements. How do I troubleshoot this to figure out what is causing it to get hung up in the batch file, but not in sqldeveloper.
