Need to execute spool command through C #?

Question

1.00/5 (3 votes)

See more:

Giving Exception as Invalid sql Statement!!!

What I have tried:

spool myfile.txt ;
my query result set;
spool off;

Posted 30-Aug-18 0:36am

Member 13944807

Updated 30-Aug-18 1:36am

Add a Solution

Comments

Jochen Arndt 30-Aug-18 6:48am

"my query result set;"
is not a valid SQL statement. If that is just a placeholder, check the actually used command or show it here.

Note also that the SPOOL command is not terminated with a semicolon.

Member 13944807 30-Aug-18 6:59am

Thanks for your suggestions!!!

Using mOracleConnection As New OracleConnection(mConnectionString)
mOracleConnection.Open()
Dim mOracleCommand As OracleCommand
Dim mOracleDataReader As OracleDataReader
mOracleCommand = mOracleConnection.CreateCommand()
mOracleCommand.CommandType = CommandType.Text
mOracleCommand.CommandText = GetStrSQL()
mOracleCommand.ExecuteNonQuery()
End Using

Private Sub GetStrSQL() AS String

Dim mStrSQL AS New StringBuilder
With mStrSQl
.AppendLine(" spool D:\30Aug\Testing.txt ")

.AppendLine(" SELECT * FROM [Payroll.Payslip].Payslip WHERE PayslipID > 1500; ")
.AppendLine(" spool off ")

End With

End Private

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2018-08-30T01:37:00

The code from your comment is VB.Net and not C#.

"[Payroll.Payslip].Payslip" does not look like a valid table name.

You are trying to execute a script containing multiple commands. This won't work this way. You have to either send each command on it's own, or put the commands into a BEGIN - END block.

I'm not sure but this might work:

VB

.AppendLine("BEGIN")
.AppendLine("spool D:\30Aug\Testing.txt")
.AppendLine("SELECT * FROM [Payroll.Payslip].Payslip WHERE PayslipID > 1500")
.AppendLine("spool off")
.AppendLine("END;")

Note that there are no semicolons besides that after the last command.

You should also catch exceptions to get the error message of the database engine:

VB

Try
    ' Code goes here
Catch e As OracleException
    Dim errorMessage As String = "Code: " & e.Code & vbCrLf & "Message: " & e.Message
    ' Report error here
End Try

These help to identify SQL syntax errors.

[EDIT]
SPOOL is an Oracle SQL*Plus command. That is an Oracle database command line utility. So this command can't be used with the common programming language interfaces.

There is even no need for such an output because the interface provides all the data which would be written to the file. Just execute the query as usual and iterate over the result set. If file output is required, do that in the iteration loop.
[/EDIT]