Click here to Skip to main content
14,937,212 members
Please Sign up or sign in to vote.
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
Updated 30-Aug-18 1:36am
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

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]
   
v2
Comments
Member 13944807 30-Aug-18 8:25am
   
Thanks for your suggestion!!!
Code: 6550
Message: ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "D" when expecting one of the following:
:= . ( @ % ;
This is the Exception I faced!!!
Member 13944807 30-Aug-18 8:26am
   
Thanks for improving my code!!!
Same query if executed as it is @ SQLDeveloper; file is getting created & records are getting inserted in newly created file as well!!
Jochen Arndt 30-Aug-18 8:48am
   
See my updated answer.
The error occurs because the SPOOL command is not supported with the OracleConnection interface.
Member 13944807 30-Aug-18 8:55am
   
Thank you for sharing your knowledge with me!!!
My requirement is to save million of records from result set of sql into one txt file
Is there any other alternative???
Jochen Arndt 30-Aug-18 9:10am
   
You can execute the command line utility passing the input from a file like
sqlplus -s user/pass @script.sql
You might also put the above into a batch file. The login parameters can be also stored on top of the script file. Because at least one file would show your username and password, it should have restricted access.

If you want to use .Net, you can also execute the above using ShellExecute.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900