Re: SQL statement works with SQL/Plus

From: Andrey <aakit_at_softhome.net>
Date: Thu, 19 Apr 2001 16:33:46 +0400
Message-ID: <9bmlus$d78$1@josh.sovintel.ru>

oracle version does not matter

Function OracleTheExec(SQL_String As Variant, Optional Is_SQL_Statement As Long, _
Optional Hide_Error As Long) As Integer

'
' Execute SQL statemen or stored procedure on Oracle server
'

On Error GoTo EXECUTE_ERROR

'write log
sql_log_create_entry 1, SQL_String ' server SQL

'reinit
db_reinit_currentdb_with_check

Dim qdef1 As QueryDef
Dim result As Integer

Set qdef1 = thisdb.CreateQueryDef("")

qdef1.Connect = ODBC_Get_Connect
qdef1.ReturnsRecords = False

'ShowStatusBar SQL_String

If Boolean_To_Num(Is_SQL_Statement) = 1 Then qdef1.SQL = SQL_String
Else

      If Mid(SQL_String, Len(SQL_String), 1) = ";" Then
      Else
        SQL_String = SQL_String & ";"
      End If
      qdef1.SQL = "DECLARE BEGIN " & SQL_String & " END;"

End If

If global_is_debug_sql = 1 Or global_is_debug_sql = -1 Then

exec_sql_debug_sql qdef1.SQL
End If

qdef1.Execute
TheExec = 1

qdef1.Close

'write log
sql_log_update_entry 0

Exit Function

EXECUTE_ERROR: errnum = Err.number
errtext = Err.DESCRIPTION

'dump errors
sql_dump_errors_stack

'write log
sql_log_update_entry errnum

        If Boolean_To_Num(Hide_Error) = 1 Then
            VBM_MSSG.custom_msgbox sql_errors_dump & get_NL, 3
            TheExec = 0
        Else  ' If Boolean_To_Num(Hide_Error) = 1 Then
           Select Case Err.number
           Case 3325
             TheExec = 0
           Case Else
             Dim var_err_text
             errtext = "EXEC SQL: " & Mid(SQL_String, 1, 254) & get_NL &

errtext

VBM_MSSG.custom_msgbox sql_errors_dump & get_NL, 3

             result = MsgBox(errtext, vbCritical, _
              "ERROR")

             TheExec = 0
           End Select

           qdef1.Close
        End If   ' If Boolean_To_Num(Hide_Error) = 1 Then

End Function

"Christian Weyer" <christian.weyer_at_eyesoft.de> wrote in message news:3AD4004A.3B579AF4_at_eyesoft.de...
> Hi,

> > You didn't mention the version of Oracle.
> > The syntax is legal in Oracle8i, not prior to Oracle8i.

> I am using Oracle 8i (8.1.6).

>


> > You may change as following:

> >

> >    UPDATE ... SET ...

> >    VERSION_NUMBER = (SELECT 1+MAX(VERSION) ... ) ...

> >
> > It does not need to be put into a PL/SQL block, remove the BEGIN/END.

> Well I tried it even without the BEGIN ... END block ... noop :-(

> Regards,
> Christian
> --
> ___________________________________________________
> eYesoft We see your visions.
> ???????????????????????????????????????????????????
> Christian Weyer http://www.eyesoft.de

>


> christian.weyer_at_eyesoft.de    Bernhard-Krieg-Str. 4

> Tel.: +49-9393-993161         97845 Neustadt/Main

> ___________________________________________________

>        Building Smart Internet Architectures

> ???????????????????????????????????????????????????

Received on Thu Apr 19 2001 - 07:33:46 CDT

Re: SQL statement works with SQL/Plus - but not with ODBC