Re: Oracle stored procedure limits with resultsets

First, I believe the procedure has to be part of a package to call it from ODBC, and second, it may only work for procedures and not functions (I'm not certain of this, but I have found no examples of calling a function). Try the following:

create or replace package odbc_pkg
as
  type recordType is record ( DNAME DYSSN.DNAME%TYPE );   type cursorType is ref cursor return recordType;   procedure find_doc( code IN VARCHAR2 , recset IN OUT cursorType ); end;

create or replace package body odbc_pkg
as
procedure find_doc( code IN VARCHAR2 , recset IN OUT cursorType ) is
begin
  open recset for select DYSSN,DNAME from DOCTORS where DOCCODE=code; end;
end;

Once again, I got this information for the Oracle ODBC Help (sqora.hlp) which comes with the 8.1.5 installation for NT4. If you don't have this file and would like a copy, let me know and I'll email it to you.

Josh Miller

Christian BRUNO <brunoc_at_ifrance.com> wrote in message news:8b5mlg$hjk$1_at_minus.oleane.net...
> thanks for your answer Thomas,
>
> i have another technical question
> i got this from the osi.oracle.com site :
>
> "If you use ODBC here is a working example, but it requires the use of the
> 8.0.5.2.0 or later Oracle ODBC driver, and an 8.0.5 server. "
>
> so, i have installed my 8.0.5 Client, and an ODBC 8.x driver from Oracle
> i can connect to my 7.3 server without trouble.
>
> i ran the following orders :
>
> create or replace package types
> as
> type cursorType is ref cursor;
> end;
>
> create or replace function find_doc( code IN VARCHAR2 ) return
> types.cursorType
> as
> recset types.cursorType;
> begin
> open recset for select DYSSN,DNAME from DOCTORS where DOCCODE=code;
> return recset;
> end;
>
> when i call the stored procedure from ODBC, i get the error message (in
> french) :
>
> S1000 : [Oracle][ODBC][Ora]ORA-06550: Line 1, Col 7
> PLS-00221: 'FIND_DOC' is not a procedure or is undefined
> ORA-06550: Ligne 1, colonne 7:
> PL/SQL: Statement ignored
>
> could someone tell me where is the problem ?
>
> Thanks to all guys who participate this thread
>
> Regards,
> Christian
>
>
>
Received on Mon Mar 20 2000 - 00:00:00 CST