How to execute an Oracle .SQL script file using asp.net (vb.net)

The overall goal of what I am trying to do here is execute an Oracle SQL Script from as asp.net (vb.net) web application that has SQL*Plus commands in it that queries the data and drops the results to a text file and then display the text file to the end user of the web application. I am running into a problem attempting to execute the .sql file in my visual studio code it tells me that it is not a valid SQL statement. Of course that makes sense because there is more in the .sql file than a sql statement.

I am using .NET version 4.6.1 in my Visual Studio project and I am calling an Oracle 11 database.

Here is the code that I am trying to execute. When it gets to the line where it is attempting to execute the script it throws the following error:

ERROR: Oracle.OracleException: 'ORA-00900: invalid SQL statement'
​

Here is the VB.NET code:

Dim OracleConnection As OracleConnection = New OracleConnection()
OracleConnection.ConnectionString = Settings.GetOracleConnectionString
OracleConnection.Open()
Dim script As String = File.ReadAllText("C:\OracleScripts\p_sum.sql")

Dim command As OracleCommand = OracleConnection.CreateCommand()
command.CommandText = script
command.ExecuteNonQuery() << Errors on this line


The .sql file makes a few calls to Oracle and then exports (spools) the results to a .txt file. Once the .sql script is executed I would then display the .txt file that is created in the browser.

I have provided a copy of the .sql file below:

SPOOL data/p_sum.txt
PROMPT REPORT P_SUM.SQL Rev.04 (MIXER=&&MIXER) (CMPD=&&CMPD) (START=&&START) (END=&&END)
SET VERIFY OFF
SET LINESIZE 130

CLEAR BREAKS
CLEAR COMPUTES

COLUMN SHIFT_DT FORMAT A6 HEADING Shift|Date
COLUMN SHIFT_NUM FORMAT B9 HEADING Sh|#
COLUMN MIXER_NUM FORMAT B99 HEADING Mix|Num
COLUMN RUN_START_DT FORMAT A12 HEADING Shift|Start
COLUMN COMPOUND FORMAT A11 HEADING |Compound
COLUMN BAG_NAME FORMAT A11 HEADING 'Bag Name'
COLUMN BAG_MIXER_NUM FORMAT B99 HEADING Bag|Usr
COLUMN BATCH_CNT FORMAT B99,999 HEADING Good|Cnt
COLUMN ERROR_CNT FORMAT B999 HEADING Bad|Cnt
COLUMN EDIT_CNT FORMAT B999 HEADING Edt|Cnt
COLUMN WT_PROD FORMAT B999,999 HEADING Lbs/Kg|Produced
COLUMN TCHARGE FORMAT B9999.9 HEADING Cycle|Min
COLUMN TDELAY FORMAT B9999.9 HEADING Delay|Min
COLUMN TCHANGE FORMAT B9999.9 HEADING Change|Min
COLUMN TNOTSCHED FORMAT B9999.9 HEADING Unschd|Min
COLUMN RUN FORMAT B99,999 HEADING Run|Min
COLUMN Avg_CHARGE FORMAT B9.99 HEADING Cycle|Avg
COLUMN pct_up FORMAT B999.9 HEADING '% Up|Time'

BREAK ON SHIFT_DT SKIP 1
CLEAR &&CLAUSE1

SELECT TO_CHAR(SHIFT_DT,' MM/DD') SHIFT_DT,
R.SHIFT_NUM,
TO_CHAR(MIN(R.RUN_START_DT),' MM/DD HH24:MI') RUN_START_DT,
'*SHIFT' COMPOUND,
'TOTAL ' BAG_NAME,
SUM(R.BATCH_CNT)-NVL(SUM(R.ERROR_CNT),0) BATCH_CNT,
SUM(R.ERROR_CNT) ERROR_CNT,
SUM(EDIT_CNT) EDIT_CNT,
SUM(R.WT_PROD) WT_PROD,
SUM(R.TCHARGE) TCHARGE,
SUM(R.TDELAY) TDELAY,
SUM(R.TCHANGE) TCHANGE,
SUM(R.TNOTSCHED) TNOTSCHED,
NVL(SUM(R.TCHARGE),0)+NVL(SUM(R.TDELAY),0)+NVL(SUM(R.TCHANGE),0)+NVL(SUM(R.TNOTSCHED),0) RUN,
DECODE(SUM(R.BATCH_CNT)-SUM(R.ERROR_CNT),0,0,SUM(R.TCHARGE)/(SUM(R.BATCH_CNT)-SUM(R.ERROR_CNT))) AVG_CHARGE,
SUM(R.TCHARGE)*100/(NVL(SUM(R.TCHARGE),0)+NVL(SUM(R.TDELAY),0)+NVL(SUM(R.TCHANGE),0)) PCT_UP
FROM MIXER.RUN_SUMM R, MIXER.SPEC S
WHERE R.SPEC_NUM=S.SPEC_NUM
AND R.MIXER_NUM IN(&&MIXER)
AND R.MIXER_NUM >=80
AND SHIFT_DT BETWEEN TO_DATE('&&START','MM/DD/RR') AND TO_DATE('&&END','MM/DD/RR')
GROUP BY R.SHIFT_DT, R.SHIFT_NUM
UNION
SELECT TO_CHAR(R.SHIFT_DT,' MM/DD') SHIFT_DT,
0 SHIFT_NUM,
'ALL SHIFTS' RUN_START_DT,
'**DAILY' COMPOUND,
'TOTAL' BAG_NAME,
SUM(R.BATCH_CNT)-NVL(SUM(R.ERROR_CNT),0) BATCH_CNT,
SUM(R.ERROR_CNT) ERROR_CNT,
SUM(EDIT_CNT) EDIT_CNT,
SUM(R.WT_PROD) WT_PROD,
SUM(R.TCHARGE) TCHARGE,
SUM(R.TDELAY) TDELAY,
SUM(R.TCHANGE) TCHANGE,
SUM(R.TNOTSCHED) TNOTSCHED,
NVL(SUM(R.TCHARGE),0)+NVL(SUM(R.TDELAY),0)+NVL(SUM(R.TCHANGE),0)+NVL(SUM(R.TNOTSCHED),0) RUN,
DECODE(SUM(R.BATCH_CNT)-SUM(R.ERROR_CNT),0,0,SUM(R.TCHARGE)/(SUM(R.BATCH_CNT)-SUM(R.ERROR_CNT))) AVG_CHARGE,
SUM(R.TCHARGE)*100/(NVL(SUM(R.TCHARGE),0)+NVL(SUM(R.TDELAY),0)+NVL(SUM(R.TCHANGE),0)) PCT_UP
FROM MIXER.RUN_SUMM R, MIXER.SPEC S
WHERE R.SPEC_NUM=S.SPEC_NUM
AND R.MIXER_NUM IN(&&MIXER)
AND R.MIXER_NUM >=80
AND SHIFT_DT BETWEEN TO_DATE('&&START','MM/DD/RR') AND TO_DATE('&&END','MM/DD/RR')
GROUP BY R.SHIFT_DT
UNION
SELECT 'ALL' SHIFT_DT,
0 SHIFT_NUM,
' ' RUN_START_DT,
'***GRAND' COMPOUND,
'TOTAL' BAG_NAME,
SUM(R.BATCH_CNT)-NVL(SUM(R.ERROR_CNT),0) BATCH_CNT,
SUM(R.ERROR_CNT) ERROR_CNT,
SUM(EDIT_CNT) EDIT_CNT,
SUM(R.WT_PROD) WT_PROD,
SUM(R.TCHARGE) TCHARGE,
SUM(R.TDELAY) TDELAY,
SUM(R.TCHANGE) TCHANGE,
SUM(R.TNOTSCHED) TNOTSCHED,
NVL(SUM(R.TCHARGE),0)+NVL(SUM(R.TDELAY),0)+NVL(SUM(R.TCHANGE),0)+NVL(SUM(R.TNOTSCHED),0) RUN,
DECODE(SUM(R.BATCH_CNT)-SUM(R.ERROR_CNT),0,0,SUM(R.TCHARGE)/(SUM(R.BATCH_CNT)-SUM(R.ERROR_CNT))) AVG_CHARGE,
SUM(R.TCHARGE)*100/(NVL(SUM(R.TCHARGE),0)+NVL(SUM(R.TDELAY),0)+NVL(SUM(R.TCHANGE),0)) PCT_UP
FROM MIXER.RUN_SUMM R, MIXER.SPEC S
WHERE R.SPEC_NUM=S.SPEC_NUM
AND R.MIXER_NUM IN(&&MIXER)
AND R.MIXER_NUM >=80
AND SHIFT_DT BETWEEN TO_DATE('&&START','MM/DD/RR') AND TO_DATE('&&END','MM/DD/RR')
ORDER BY SHIFT_DT,RUN_START_DT;

COLUMN SHIFT_DT FORMAT A5 HEADING Shift|Date
COLUMN RUN_START_DT FORMAT A11 HEADING Run|Start
COLUMN COMPOUND FORMAT A10 HEADING |Compound
COLUMN BAG_NAME FORMAT A10 HEADING 'Bag Name'

BREAK ON SHIFT_DT SKIP PAGE ON SHIFT_NUM SKIP 1
CLEAR &&CLAUSE1

SELECT TO_CHAR(SHIFT_DT,'MM/DD') SHIFT_DT,
R.SHIFT_NUM,
TO_CHAR(RUN_START_DT,'MM/DD HH24:MI') RUN_START_DT,
S.COMPOUND,
R.BAG_NAME,
R.BAG_MIXER_NUM,
R.BATCH_CNT-NVL(R.ERROR_CNT,0) BATCH_CNT,
R.ERROR_CNT,
R.EDIT_CNT,
R.WT_PROD,
R.TCHARGE,
R.TDELAY,
R.TCHANGE,
R.TNOTSCHED,
NVL(R.TCHARGE,0)+NVL(R.TDELAY,0)+NVL(R.TCHANGE,0)+NVL(R.TNOTSCHED,0) RUN,
DECODE(R.BATCH_CNT-NVL(R.ERROR_CNT,0),0,0,R.TCHARGE/(R.BATCH_CNT-NVL(R.ERROR_CNT,0))) AVG_CHARGE
FROM MIXER.RUN_SUMM R, MIXER.SPEC S
WHERE R.SPEC_NUM=S.SPEC_NUM
AND R.MIXER_NUM IN(&&MIXER)
AND R.MIXER_NUM >=80
AND SHIFT_DT BETWEEN TO_DATE('&&START','MM/DD/RR') AND TO_DATE('&&END','MM/DD/RR')
AND ROWNUM<=&&ROWLIMIT
ORDER BY SHIFT_DT,RUN_START_DT;

SPOOL OFF
SET VERIFY ON
CLEAR BREAKS
CLEAR COMPUTES

Continue reading...