Call Oracle Function Problem

[vzjfgm]

I am trying to call an oracle function via MS Access with three parameters. I am getting an error. VM_Delete_Chem is not a procedure or undefined. How do I call this as a Function to return an integer. Please help!! Thanks you!!


Code:

Dim db As Database
Dim qryOracleProc As QueryDef
Dim strSQL, strDeleteHeat, strDeleteSampleid, strSearchError As String
Dim strDeleteDateSample As Date
Dim varUpdateScriptMsg As Variant


strDeleteHeat = Me.txtSaveOldHeat.Value
strDeleteSampleid = Me.txtSaveOldSampleid.Value
strDeleteDateSample = Format(Me.TxtSampleDate.Value, "mm/dd/yyyy hh:mm:ss AMPM")

Set db = CurrentDb()
Set qryOracleProc = db.CreateQueryDef("")

'SQL to call stored procedure VM_Delete_Chem with the following parameters
'Call with Heat number
' Sample ID
' Sample date and Time YYYY-MM-DD-HH-MM-SS
'Returns: 0 - successful
' 1 - no records found procedure failed
' 2 - multi records found procedure failed
' 3 - Other exception procedure failed

strSQL = "BEGIN VMSTAR.VM_DELETE_CHEM("
strSQL = strSQL & "'" & strDeleteHeat & "', "
strSQL = strSQL & "'" & strDeleteSampleid & "', "
strSQL = strSQL & "to_date('" & strDeleteDateSample & "','mm/dd/yyyy HH:Mi:SS PM'))"
strSQL = strSQL & "; END;"

'ODBC connection
qryOracleProc.Connect="ODBC;DSN=pamioh;UID=chemxx;PWD=xxxx;SERVER=pamioh"
qryOracleProc.SQL = strSQL
qryOracleProc.ReturnsRecords = False
qryOracleProc.ODBCTimeout = 60
qryOracleProc.Execute

Set qryOracleProc = Nothing

 

[lameid]

I don't know anthying about Oracle...

But shouldn't you retrun a record?

qryOracleProc.ReturnsRecords = True 'You have false

This would also mean you would have to assign a recordset to it.

I hope that's enough to get you going.

 

[vzjfgm]

Thanks for the reply! I tried you suggestion and got another error. Error 3065 - Can't execute a select query.
Any more suggestions? I'm lost! Thank you.

 

[lameid]

Try making the following modifications to your code to see if you can figure out what field name you get... Don't forget to check the immediate window for results.

Dim RS as recordset
Dim fld as Field

'qryOracleProc.Execute
Set RS = qryOracleProc.openrecordset

While Not RS.eof
For Each FLD In RS.Fields
Debug.print fld.name, fld.value
Next
Wend

 

[vzjfgm]

Thanks for the suggestion. Not seem to be working. Has anybody used the direction property with the QueryDef object for a return value and input parameters? Thank YOU

 

[lameid]

Does Oracle have an immediate query tool that you could paste what strsql evaluates to (print it to the immediate window and copy)?

It just feels like a syntax error on the Oracle side to me.

Unless my code runs and it doesn't error... if so you did leave .ReturnsRecords true, correct?

 

[Thargy]

vzjfgm,

I'm an oracle DBA wandering from a different forum, but I saw the mighty 'O' being mentioned, so I thought I'd have a nosey.

First of all, you need to check permissions. If the user id you're logging on with doesn't have permissions on the stored procedure, then it will appear to 'not exist'.

Second, you can't execute a function, but you can execute a procedure. If you're invoking a function, you use it as the right hand side of an expression.

To evaluate what you're sending to oracle, start a sqlplus session, and run your query native, and see what results you get.

Please post your precise code, attempted use, and returned errors. I'll hang around to see what happens.

Regards

Tharg

Grinding away at things Oracular