[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2035
  • Last Modified:

ORA-00933 Executing Oracle Select Commnd using Oracle Client driver in Microsoft.NET Error "SQL command not properly ended"

I am executing SQL Command
 Select REF_NO, CHANGE_TYPE, CODE, CONSEQ from TB_MASTER where OBJECT_TYPE='TABLE' and OBJECT_NAME='TABLE2' and PROJECTNAME='SANDEEP' and relname='FCC' order by SUBSTR(REF_NO,2,1) DESC, REF_NO ASC
which works fine when I execute from PLSQL Developer or SQL Plus.
But when I execute this SQL from VB.NET gives an error .
      fn_call_conv      "CoreLab.Oracle.OracleException: ORA-06550: line 2, column 209:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 2, column 4:
PL/SQL: SQL Statement ignored
   at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, Int32 A_1, Boolean A_2, Int32 A_3, Int32 A_4, Int32& A_5)
   at CoreLab.Oracle.OracleCommand.ExecuteReader(CommandBehavior behavior)
   at CoreLab.Oracle.OracleCommand.c(CommandBehavior A_0)
   at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at Flexmart.Flexmart.ClsFlex.fn_call_conv(String p_path, String p_projname, String p_relname, String p_type, String conn) in D:\Flexmart\Flexmart_VB\Flexmart.NET Uaing ODBC\Flexmart.vb:line 399"      String
I could able to understand it is happening at line number 399. Other than that I am not understanding what column number, line number it is refering ( eg Column 4 line 2).
I am using following code to connect to execute this SQL

Dim Connect As New OracleConnection()
            Dim Comm As New OracleCommand()
            Dim DAdapter As New OracleDataAdapter()
            Dim RowCounter As Integer = 0
            Dim i As Object
            Dim Dr As OracleDataReader
            Dim Rs1 As New DataSet()
Comm.CommandText = _
             " Select REF_NO, CHANGE_TYPE, CODE, CONSEQ from TB_MASTER " & _
            " where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 'TABLE2' and PROJECTNAME = 'SANDEEP' and relname = 'FCC' order by SUBSTR(REF_NO,2,1) DESC, REF_NO ASC "
                        Comm.Connection = Connect
                        DAdapter.SelectCommand = Comm
                        DAdapter.Fill(Rs1)
                       
DAdapter.Fill(Rs1) is the line number 399 in my code. Can some body put insight into it.
Thanks in Advance

Regards,
Sandeep Shivathaya S
 
0
sandeepshivathaya
Asked:
sandeepshivathaya
1 Solution
 
mmusanteCommented:
Try removing the starting space in your string
change ...

Comm.CommandText = _
             " Select REF_NO, CHANGE_TYPE, CODE, CONSEQ from TB_MASTER " & _
            " where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 'TABLE2' and PROJECTNAME = 'SANDEEP' and relname = 'FCC' order by SUBSTR(REF_NO,2,1) DESC, REF_NO ASC "

to ...

Comm.CommandText = _
             "Select REF_NO, CHANGE_TYPE, CODE, CONSEQ from TB_MASTER " & _
            " where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 'TABLE2' and PROJECTNAME = 'SANDEEP' and relname = 'FCC' order by SUBSTR(REF_NO,2,1) DESC, REF_NO ASC "

0
 
sandeepshivathayaAuthor Commented:
OK I have done but still facing the same error.
Thanks for suggestion.
It will be helpful if I get any other suggestion.

Regards,
Sandeep
0
 
banurekhapCommented:
try to put the value in ""

Comm.CommandText = _
             "Select REF_NO, CHANGE_TYPE, CODE, CONSEQ from TB_MASTER " & _
            " where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 'TABLE2' and PROJECTNAME = 'SANDEEP' and relname = 'FCC' order by SUBSTR(REF_NO,2,1) DESC, REF_NO ASC "

like
Comm.CommandText = _
             "Select REF_NO, CHANGE_TYPE, CODE, CONSEQ from TB_MASTER " & _
            " where OBJECT_TYPE = "TABLE" && OBJECT_NAME = "TABLE2" && PROJECTNAME = "SANDEEP" && relname = "FCC" order by SUBSTR(REF_NO,2,1) DESC, REF_NO ASC "


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sandeepshivathayaAuthor Commented:
Hi This SQL did not work more over this SQL gives error in the PLSQL Developer it self. Previous SQL was working with PLSQL Developer and SQL Plus.

More information regarding this problem .This same SQL with differnt conastants in where clause worked fine when executed from application which returns 2 records.
The above SQL returns single record.

Is there any problem with Sort clause?

Regards,
Sandeep
0
 
mmusanteCommented:
I tried your query on this table

create table TB_MASTER (REF_NO VARCHAR2(10),       
                                CHANGE_TYPE VARCHAR2(10),
                                CODE VARCHAR2(10),
                                CONSEQ VARCHAR2(10),
                                OBJECT_TYPE VARCHAR2(10),
                                OBJECT_NAME VARCHAR2(10),
                                PROJECTNAME VARCHAR2(10),
                                relname VARCHAR2(10) )

and it works!
Can you post the script for creationg your table?
0
 
sandeepshivathayaAuthor Commented:
IMPORTANT: SQL works fine from PLSQL Developer and SQL Plus. So please try executing from .NET enviornament.

Table Structure
Field Name Type Nullable
REF_NO      VARCHAR2(16)                  
CHANGE_TYPE      VARCHAR2(1)      Y            
CODE      LONG      Y
CONSEQ      VARCHAR2(1)      Y            
OBJECT_TYPE      VARCHAR2(15)      Y            
OBJECT_NAME      VARCHAR2(40)                  
PROJECTNAME      VARCHAR2(25)                  
RELNAME      VARCHAR2(25)      Y            

Thanks and Regards,
Sandeep
0
 
sandeepshivathayaAuthor Commented:
I got answer for this two simple mistakes I had done.
1. I executed a stored procedure while executiing stored procedure I set
Comm.CommadType = CommandType.StoredProcedure
So Command tried to take SQL Command as stored procedure and tries to execute as stored procedure not as an SQL statement which resulted in the error ORA-00933 "SQL Command not Ended Properly"
2. While executing SQL Command I added some parameter which I didn't cleared so when I executed the command I got the error "ORA-01036 Illegal variable name/number". I solved it by simply adding a statement in my code
Comm.Parameters.Clear().

Previously I thought it is the problem with driver. It has got nothing to do with driver simply the changing the above things.
Thanks for all those who helped to solve the problem.
Only difficult here people face is the way .NET gives the exeception. It is confusing.

Thanks and Regards,
Sandeep Shivathaya S
0
 
GhostModCommented:
PAQed, with points refunded (500)

GhostMod
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now