Executing statement and commiting statement via qtp

[unbeliever]

Hello All,

I am connecting to DB and trying to execute statement:

Code:

strQuery = "exec STATEMENT"

Please note that this is not a SELECT statement.

I am getting error message that SQL statement is invalid, though I can successfully execute same statement in SQL Plus. Can you please advise me proper syntax how to execute this statement and after that commit changes to DB with code sample?

[Saket]

try this, may be it can help you

Code:

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "Connection String"

Set oCommand = CreateObject("ADODB.Command")
oCommand.ActiveConnection = oConnection

oCommand.CommandText = "exec Statement"
oCommand.Execute

[unbeliever]

Thanks, I've tried but outcome is the same:

Code:

ORA-00900: Invalid SQL Statement

[Saket]

I doubt there is something wrong in your exec staement.
Can you paste exeactly what you are trying here?

[unbeliever]

Actually there can be a problem, sql statements which are executable in SQL Plus require different syntax in SQL Developer, otherwise - cannot be executed and result in exactly the same manner: Invalid SQL statement.

For example:
- in SQL Plus I execute statement like this: exec statement
- in SQL Developer I wrap statement:

Code:

begin;
exec statement
end;

As for my current code it's below:

Code:

Dim oConn
Dim strConn
Dim strQuery

Set oConn = CreateObject("ADODB.Connection")
Set oRecSet = CreateObject("ADODB.Recordset")
Set oCommand = CreateObject("ADODB.Command")
oConn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
oCommand.ActiveConnection = oConn
oCommand.CommandText = "exec STATEMENT"
oCommand.Execute
oConn.Close

[Saket]

Yes, I agree that syntax could be different for some or the other application but this should not be the case when you are doing it through the script. like as in the example you said in SQL Developer you need to wrap, this is the way sql developer tells server to execute the statement.
anyways are you able to execute the same statement on either of the the application ie SQL plus or sql dev
bcoz what i found about ORA-00900: invalid SQL statement is
"The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed. "

which i beleive is a syntax error and correcting that should solve the issue.

let me know your comments on this.

[unbeliever]

Working solution is:

Code:

Dim oConn
Dim oCommand
Set oConn = CreateObject("ADODB.Connection")
Set oCommand = CreateObject("ADODB.Command")
oConn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;"
oCommand.ActiveConnection = oConn
oCommand.CommandText = "STATEMENT"
oCommand.Execute
oConn.Close

See oCommand.CommandText line for modifications: exec word was removed.