Re: How to return a result set from a stored proc?

althought Oracle Server supports returning resultsets from a SP (since 7.x), the Microsoft ODBC Drivers and Oracle 7.x ODBC drivers do not have this functionality (may be the cause of your error message 'wrong number of parameter' )

i use 8.0.5 server with Oracle ODBC Driver 8.01.55.00

i dont know if OLEDB For Oracle (by Microsoft) support these "{call" syntax, but i think the Oracle OLEDB Provider (the one on technet.oracle.com -> 39Mb to download) will work

HTH
Christian

"Patrick Joyal" <please.reply_at_to.the.newsgroup> a écrit dans le message news: 391afcab_at_news...
> What Oracle or ODBC version are you using?
>
> I'm using Oracle 8.1.5 w/ the ODBC that came with it, and it says
> wrong number or type of argument,
>
> so how the heck do you do that?
>
> Christian BRUNO wrote in message <8feoes$112$1_at_s2.feed.news.oleane.net>...
> >try this (code has not been tested!! maybe syntax problems) :
> >
> >- declare a new package that contains a REF CURSOR type definition and
 the
> >procedure prototype
> >
> >create or replace package MYPACK as
> >type MYTYPE is ref cursor;
> >procedure MYPROC( result IN OUT MYTYPE, other parameters... );
> >end;
> >
> >- create the procedure like this
> >
> >create or replace procedure MYPROC( result IN OUT MYTYPE, other
> >parameters... )
> >as
> >open result for select * from whatyouwant;
> >end
> >
> >from ODBC, for example, call
> >--> SQLExecDirect -> "{call MYPACK.MYPROC( parameters but NOT THE
> >FIRST ) }"
> >
> >if you do not use ODBC, you will certainly have to give the cursor as
 first
> >parameter
> >
> >HTH
> >Christian
> >
> >
> >"Alexander Jerusalem" <ajeru_at_my-deja.com> a écrit dans le message news:
> >8fefdl$18t$1_at_nnrp1.deja.com...
> >> Hi,
> >>
> >> The subject basically says it all. I switched to Oracle from SQL-
> >> Server. In SQL-Server I could return a result set to the calling
> >> program from a stored procedure just by executing a sql statement. The
> >> same doesn't work in Oracle though. Following is my guess, which is not
> >> working:
> >>
> >> create or replace procedure (id in number) as
> >> begin
> >> select a, b, c from test where testId = id;
> >> end;
> >>
> >> Any hint would be very much appreciated.
> >>
> >> --
> >> Alexander Jerusalem
> >> VKN
> >> ajeru_at_gmx.net
> >>
> >>
> >> Sent via Deja.com http://www.deja.com/
> >> Before you buy.
> >
> >
>
>
Received on Fri May 12 2000 - 00:00:00 CDT