ORA Error Message When Passing SQL statements to VB Command Object.

Toad Data Point & Toad Intelligence Central
1

Hello
I’m new to using Oracle and Oracle TOAD. So, bear with me here.

I got this sql statement of chosen fields that keeps causing ORA errors like the following below. I need to be able to assign this sql as a string to the following variable name - strSQL. Also, in the WHERE clause, there is another variable name cKEY_SEQ, that contains the row number of the record choosen in the grid. The variable content in - strSQL, will be passed to a VB Property (as String) and on to a VB CommandText to be executed. Could someone help me with the syntax. I never use dealt with Oracle and just don’t how to do this. Thanks in advance for your time.

ORA-00933: SQL COMMAND NOT PROPERLY ENDED.
ORA-01756 : QUOTED STRING NOT PROPERLY TERMINATED

*THIS IS MY FIRST ATTEMPT AT SETTING UP THE SQL STATEMENT.*strSQL = "UPDATE MY_DB_ENVIRONMENTS " & _

" VB_APP_NAME = " & cVB_APP_NAME & ", " & _

" TUX_USER = " & cTUX_USER & ", " & _

"WHERE KEY_SEQ = " & cKEY_SEQ

" VB_APP_NAME = " & “:” & cVB_APP_NAME & ", " & _

“WHERE(MY_DB_ENVIRONMENTS.KEY_SEQ =” & “:” & cKEY_SEQ & “)”

I need help desparatetly with this problem. I’m sending two methods on how I set up the sql’s. Not sure which is right.

Kevin --------------

*My company softwares of choice are – VB.NET, Visual Studio.NET, Oracle 10g, Tuxedo, PL/SQL.*****"SET MY_ICON_NAME = " & cCITRIX_ICON_NAME & “, " & _” TUX_DOMAIN = " & cTUX_DOMAIN & ", " & _
" TUXEDO_URL = " & cTUXEDO_URL & " " & _

THIS IS MY SECOND ATTEMPT AT SETTING UP THE SQL STATEMENT.
strSQL = "UPDATE MY_DB_ENVIRONMENTS " & _
"SET MY_ICON_NAME = " & “:” & cCITRIX_ICON_NAME & “, " & _” MY_USER = " & “:” & cTUX_USER & ", " & _
" MY_URL = " & “:” & cTUXEDO_URL & " " & _

2

There is a feature in the edtior that converts SQL statements into applicaiton code. To use, go to Options | Editor | Application Code and set your target language. Then in the editor highlight the SQL statement and choose 'Application Code'. It will convert the statement and copy to the clip board. 'Remove Applicaiton Code' reverts to SQL.

See screenshots. I think this should cover what you need.

Debbie

ApplicationCode.png
ApplicationCode.png784×496 26.8 KB

3

There is a feature in the edtior that converts SQL statements into applicaiton code. To use, go to Options | Editor | Application Code and set your target language. Then in the editor highlight the SQL statement and choose 'Application Code'. It will convert the statement and copy to the clip board. 'Remove Applicaiton Code' reverts to SQL.

See screenshots. I think this should cover what you need.

Debbie

ApplicationCode2.png
ApplicationCode2.png1178×667 60.6 KB

4

Debbie
First off, thank you so much for answering my question. Please, find the image of the version of TOAD I’m using.

Question: I can’t seem to find the app your talking about. It there a software I need to get? Is it in this version? I’ll keep looking. Please, answer asap. Thank you.
TOAD VERSION kl.bmp

5

I see. I was talking about Toad for Data Analysts. If you have any of the Toad for Oracle Suites installed you will have this installed and licensed.

I don’t know the Toad for Oracle product that well. If you want to see if they have a similiar feature you could post on their board.

http://toadfororacle.com/index.jspa

p.s. I did take a quick look at Toad for Oracle. It looks like they have a similiar function but it is called “Code Statement”. You set the language in the options like before. Search for Code Statement. You’ll see a drop down for Languages.

Debbie

6

This is part of the sql. Do you see anything wrong with it the syntax? This is what my version of toad produces.

It ‘quotes everything’, the variables should be on the outside (relatively speaking) because they are populated with data (already). All I need to do is convert to a string to pass to the RS or CommandText object. Let me know what you think, I’m not sure what to do. Any help would be greatly appreciate at this point.

*** Convert sql ****
strSQL = " UPDATE CPR_DB_ENVIRONMENTS "
strSQL = strSQL & "SET CITRIX_ICON_NAME = & cCITRIX_ICON_NAME, "
strSQL = strSQL & " VB_APP_NAME = & cVB_APP_NAME &, "
strSQL = strSQL & "WHERE CPR_DB_ENVIRONMENTS.KEY_SEQ = cKEY_SEQ "

*** Original 1****
"UPDATE CPR_DB_ENVIRONMENTS " & _
“SET CITRIX_ICON_NAME = '” & cCITRIX_ICON_NAME & “’, " & _
" VB_APP_NAME = '” & cVB_APP_NAME & "’, " & _
"WHERE KEY_SEQ = " & cKEY_SEQ

7

"SET ADDRESS_ID = {0} , ADDRESS = ‘{1}’"+ vbCrLf + _"WHERE Address_id > 10"

**";", Value1, Value2)

Do you have something like that for VB? I’m sure there is.

Debbie**

I don’t code in VB.

If it was in C# I would take the Application code and then use a String.Format to replace the variables.

Dim SQL As String = String.Format("UPDATE QUEST_DEV.ADDRESS "+ vbCrLf + _+ vbCrLf + _