?
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
Medium Priority
?
1,943 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Expert Comment

by:mmusante
ID: 11840599
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
ID: 11840637
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
ID: 11845604
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
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.

 

Author Comment

by:sandeepshivathaya
ID: 11849614
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
 
LVL 6

Expert Comment

by:mmusante
ID: 11849717
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
ID: 11849745
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
ID: 11850272
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
ID: 12088093
PAQed, with points refunded (500)

GhostMod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Starting up a Project

765 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