Solved

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

Posted on 2004-08-19
9
1,806 Views
Last Modified: 2008-01-09
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
Comment
Question by:sandeepshivathaya
9 Comments
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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
 

Author Comment

by:sandeepshivathaya
Comment Utility
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
 

Expert Comment

by:banurekhap
Comment Utility
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
 

Author Comment

by:sandeepshivathaya
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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
 

Author Comment

by:sandeepshivathaya
Comment Utility
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
 

Author Comment

by:sandeepshivathaya
Comment Utility
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
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
Comment Utility
PAQed, with points refunded (500)

GhostMod
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
This is an explanation of a simple data model to help parse a JSON feed
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now