Re: Error in calling stored procedure via DB link

Hi Paul,
This was of great help. It worked. Thanks a lot.

Regards,
Deva.

"Paul Clement" wrote:

> On Thu, 4 Aug 2005 12:09:01 -0700, "Deva" <Deva@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> ¤ Hi Paul,
> ¤
> ¤ I tried this out now, its not working. The same error is thrown. Will dblink
> ¤ calls not be supported by ADODB?
> ¤
>
> I didn't recognize the Dblink syntax. Below is an Oracle KB article that documents the issue. I
> don't know whether this will address the use of query parameters and I don't know which driver or
> provider you are using. It's possible that Microsoft's drivers do not support this feature directly.
>
> I suppose another option would be to execute the Dblink indirectly through a stored procedure and
> call that from ADO instead.
>
>
> Doc ID: Note:186561.1
> Subject: Calling Stored Procedure over Dblink Using Oracle OleDb Provider Fails
> Type: PROBLEM
> Status: PUBLISHED
> Content Type: TEXT/X-HTML
> Creation Date: 07-NOV-2001
> Last Revision Date: 17-SEP-2002
>
> fact: Oracle Server - Enterprise Edition
> fact: Oracle Provider for OLE DB
> fact: PL/SQL
> fact: MS Windows NT
> fact: MS Windows 2000
> symptom: Calling remote stored procedure via database link
> symptom: Using ADO (ActiveX Data Object) in MS Visual Basic
> symptom: Using command type adCmdStoredProc
> symptom: Failed to execute command
> symptom: DBLINK is used
> symptom: Unspecified error
> change: NOTE ROLE:
> Enabling tracing of the OleDb provider gives the following in the trace file:
> TID: 620 (ENTRY) COracleSource::SetProperties(1, 0x6baff58)
> TID: 620 (EXIT) COracleSource::SetProperties(hr=80040e21): 4349
>
> Enabling Sql*Net client trace show no procedure call. This indicates that OleDb
> does not pass the procedure calls to the Oracle Client
> cause: To call a procedure over database link, Oracle native syntax is
> required
>
>
>
> fix:
>
> To call a stored procedure over database link, use the command type adCmdText
> to execute the command using Oracle native syntax. That is, use CommandType=
> adCmdText and BEGIN .. END syntax in the command. Other syntaxes will cause
> problems with the at-sign
>
> Oracon.Open
> cmd.ActiveConnection = Oracon
> Dim orarset As ADODB.Recordset
> Set orarset = New ADODB.Recordset
> cmd.CommandText = "BEGIN foo@v; END;"
> cmd.CommandType = adCmdText
> cmd.CommandTimeout = 0
> cmd.Execute
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>
.

Relevant Pages

  • Re: loadjava and Java version
    ... I'm trying to upload a Java class into an 8.1.7 database as a Java ... Stored Procedure. ... I issue the following command: ... Oracle 8.1.7 uses Java version 1.2.1. ...
    (comp.databases.oracle.misc)
  • Re: Performance problems with StoredProcedure in Web application
    ... you could probably just ignore my comments about stored procedure performance as compared to textual queries because they ... As for your question about how to avoid using a cursor (as I suggested was possible in Oracle) read the following paragraph for more ... The .NET Framework Data Provider for Oracle does not support batched SQL statements. ... CURSOR output parameters to fill a DataSet, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Passing Back Table Information from Oracle .NET Stored Procedures
    ... stored procedure, not in the typical PL/SQL sense. ... I don't care if it's MS SQL in-line code or Oracle in-line code, MS SQL Server, Oracle Proc, or this so called .NET Stored Procedure using a Select statement, they are all returning a RESULT SET whether it be one row or multiple rows. ... I used SqlContext above because I don't want to bother registering at the Oracle site to download their Oracle Database Extensions for .NET in order to get at the presumably extant OracleContext class. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: EXEC syntax for SP which returns row set
    ... >>> Oracle experts, none of whom seem to know. ... >> SQL> create or replace package returncur is ... > I don't know why you are supplying the above example. ... to illustrate HOW you'd call a stored procedure and return ...
    (comp.databases.oracle.server)
  • Re: inserting XML Document into Oracle database using a stored procedure
    ... What i am trying to do is i want to insert a xml file into the Oracle database using a stored procedure which takes a XmlType type parameter. ...
    (perl.dbi.users)