Re: Stored Procedures

Cursor in Oracle equals recordset in VB/sqlserver. If you are using VB, you should use Oracle Objects for Ole which is a DAO-like Oracle interface.

Also, you should definitely NOT assume stored procedures in Oracle are equivalent to stored procedures in Sqlserver. They are not. To return record/resultsets
refer to http://osi.oracle.com/~tkyte topic resultsets.

Reading the PL/SQL manual is *warmly* recommended, and/or the books on PL/SQL by Steven Feuerstein, published by O'Reilly.

Regards,

Sybrand Bakker, Oracle DBA

"Jeff" <jeff.cichocki_at_wgrfurniture.com> wrote in message news:92tf44$hlc$1_at_nnrp1.deja.com...
> I hate to sound like a rookie, but I am. I am new to the system and to
> oracle. Permissions are not a problem. The query will only return one
> row, but the others that I am working on will return more than one
> row. I am not familiar with cursors at all in oracle, could you shed
> some light on it for me (hopefully an example)? This procedure will be
> called from inside a VB application I am writing if that helps.
>
> In article <3A52363B.634188CC_at_Unforgetable.com>,
> Walter T Rejuney <BlueSax_at_Unforgetable.com> wrote:
> > jscichocki_at_my-deja.com wrote:
> >
> > > Trying to create the following stored procedure. Everytime I try to
> > > create it, it compiles with errors. I have validated the query. It
> > > functions perfect. I have two other procedure that are giving me
 the
> > > same grief (also select oriented as opposed to updates or deletes).
> > >
> > > CREATE OR REPLACE PROCEDURE CUSTOM.MY_UPS_GREETED
> > > (HOME_STORE IN VARCHAR2, TODAY IN VARCHAR2) AS
> > > BEGIN
> > > SELECT COUNT(EMP_CD) GREETED
> > > FROM CUSTOM.UPS_OPPORTUNITY
> > > WHERE HOME_STORE_CD = HOME_STORE
> > > AND UP_DATE = TODAY
> > > AND EMP_CD <> 'ZZZ';
> > > END;
> > >
> > > Thanks for your help.
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> > At the very minimum you need a host variable for the target of an INTO
> > clause. Otherwise the cursor has no place to put the data. You
 probably
> > also need some type of exception handler in case the query itself
> > returns more than one row.
> >
> > Beyond that, we need to know exactly what compile errors you are
 getting.
> > Do you have the proper permissions to create stored procedrues in the
> > CUSTOM schema? Do you have proper grants to SELECT from the
> > CUSTOM.UPS_OPPORTUNITY table? Is UP_DATE a varchar field where the
 date
> > is in a particular format? If it is a DATE field then you need to
 perform
> > a to_date() function on your TODAY variable and apply the appropriate
> > date mask (although this is a run-time problem, not a compile
 problem)?
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 02 2001 - 15:19:45 CST