Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to fire a stored proc from a query window

Re: How to fire a stored proc from a query window

From: Lee <lduhl_at_corp.realcomp.com>
Date: 20 Aug 2004 09:56:45 -0700
Message-ID: <719532c5.0408200856.d8b692c@posting.google.com>


Frank

Thanks for the info. The VarChar thing was a typo in the sample and the "Omwb" was copied from another sp that did come from the Workbench. We have since found out the "Non Workbench" method of using and refering to them in the proc.

The whole problem from the original post was how to call the proc and get the returned Ref Cursor back and display the results.

Actually at this point we still have not figured it out. Doing this same thing is a MS SQL world is very simple becuase the result sets (or record sets) are returned by default.

Thanks
Lee

Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<cg23it$bhv$1_at_news2.tilbu1.nb.home.nl>...
> Lee wrote:
>
> > Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<cfv8sf$m5k$1_at_news3.tilbu1.nb.home.nl>...
> >
> >>Lee wrote:
> >>
> >>
> >>>We've been working with Oracle (10g) for a couple months and we are
> >>>just now trying to fire a stored proc from a query window (Sql/Plus or
> >>>3rd party utility)
> >>>
> >>>I would like to call the proc declared below and have the results
> >>>returned, but for the life of me I cannot figure out how to call it.
> >>>
> >>>
> >>>CREATE OR REPLACE PROCEDURE SA.USP_LEES_TEST_SP (
> >>> strAgentCode IN CHAR DEFAULT NULL,
> >>> RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
> >>>AS
> >>> -- Variable Declarations
> >>> strAgentCode_ VARCHAR(10) := strAgentCode;
> >>>
> >>> -- Query Logic
> >>> BEGIN
> >>>
> >>> OPEN RC1 FOR
> >>> SELECT *
> >>> FROM SA.UserInfo
> >>> WHERE UserCode LIKE USP_LEES_TEST_SP.strAgentCode_ || '%';
> >>>
> >>> END USP_LEES_TEST_SP;
> >>>
> >>>I've tried things like the following, but nothing works:
> >>>EXECUTE sa.usp_Lees_Test_Sp('12345'),
> >>>CALL sa.usp_Lees_Test_sp('12345') and
> >>>EXECUTE sa.usp_Lees_Test_Sp('12345', RC1)
> >>>
> >>>If anyone can give me the command to execute this proc it would be
> >>>greatly appreciated.
> >>>
> >>>Thanks
> >>>Lee
> >>
> >>I would seriously test this app, as it is a ported
> >>application from SQL Server (by the looks of it),
> >>via Oracle's Migration Workbench (OMWB).
> >>Both products have their flaws...
> >
> >
> > Frank
> >
> > This proc was just written as a test to figure out how to get a result
> > set from
> > within a store proc returned to its calling program (whether that is a
> > application or just another query window).
> >
> > We are new to Oracle, (comming from a MS SQL world) and having a heck
> > of a time figuring some things out.
> >
> > Thanks
> > Lee
>
> What Daniel said.
>
> To name some anomalies/bad coding habits:
> - use varchar2, not varchar. (strAgentCode_ declaration)
> This is OMWB, if the proc was generated, else bad habit.
>
> - close rc1 (or run against too many open cursors):
> if rc1%isopen then
> close rc1;
> end if;
> open rc1 for ....
> This is an OMWB omission.
> - Why use strAgentCode as an IN varable (make it varchar2, please!),
> and have strAgentCode_ with the same value? What's wrong with:
> UserCode LIKE strAgentCode || '%'; ?!?
> - Now that your at it, make it:
> UPPER(UserCode) LIKE UPPER(strAgentCode) || '%'; ?!?
> The world is case sensitive....
Received on Fri Aug 20 2004 - 11:56:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US