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....
--
Regards,
Frank van Bortel
Received on Thu Aug 19 2004 - 06:46:42 CDT
