Pass through queries from Access to Oracle 9i

[mbames]

I seem to be having trouble getting Access to called a procedure in Oracle. I am trying to do this via a pass thru method. Essentially I create a query on the fly and pass that through the ODBC link to Oracle.

I can run my procedure through SQL*Plus so I know it is fine, and I can view the tables via Access so I know the ODBC link is working fine.

When I try and run the query via the debug window I get an ODBC call failed error dialog pop up.

Any ideas why it is not working?

Cheers,
Matt

My code is shown below.

Access Function

Function StoreMBA1(iID As Double, sName As String, sXml As String)

    Dim db As Database
    Dim qrydef As QueryDef
    Dim sConn As String
    Dim sSQL As String
    Set db = CurrentDb
    
    'Set connection string
    sConn = "ODBC;DSN=matt1;UID=MBAMES;PWD=MBAMES;DBQ=MATT1;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"
    
    'Create a tempoary querydef
    Set qrydef = db.CreateQueryDef("")
    
    'Set no return of records
    qrydef.ReturnsRecords = False
    
    'Build SQL string
    sSQL = "exec MBA1_INSERT_B (" & iID & ", '" & sName & "', '" & sXml & "');"
    
    qrydef.Connect = sConn
    qrydef.SQL = sSQL
    qrydef.Execute
    qrydef.Close

End Function

and this is called via the Debug window:

?StoreMBA1 (17, &quot;seventeen&quot;, &quot;<?xml version=&quot;&quot;1.0&quot;&quot;?><number><one>one</one><two>seven</two></number>&quot;)

and finally the procedure in Oracle (which works!):

CREATE OR REPLACE  PROCEDURE "MBAMES"."MBA1_INSERT_B" (
 i_id in number,
 s_name in varchar2,
 s_xml in varchar2
)
as
begin
 INSERT INTO mbames.mba1 (id, name, myxml) VALUES (i_id, s_name, sys.XMLType.createXML(s_xml));
end;

 

[sem]

The fact that something may run in sql*plus doesn't mean that this is a valid pl/sql statement. Sql*plus supports a set of its own commands. In your case it's EXEC word, that is substituted in sql*plus by BEGIN .. END;
Enclose your statement into begin..end; to make it a valid statement.

Regards, Dima

 

[mbames]

Hi Dima,

That cured it - thanks!!

Cheers,
Matt