Re: Passing variable values into a stored procedure

From: Mark <markg_at_mymail.co.uk>
Date: 9 Aug 2002 02:55:42 -0700
Message-ID: <ddb31653.0208090155.20ab0ea@posting.google.com>

Because i have a spare moment and because i am a nice guy ;) Here is the code you need.

You'll have to pick up a good PL/SQL book sometime and learn the Oracle way, your code was a tad SQL Sever orientated.

CREATE OR REPLACE PROCEDURE WFS.TP_CLEANMYT4WDOC (TierDocid IN NUMBER) AS

   CURSOR c_sel IS

      Select op__Statusord
      from T4W_Documents
      where op__id=TierDocid;

   v_retval	NUMBER;

BEGIN

   --
   -- Cursor assumes only 1 row will be returned
   --

   OPEN c_sel;
   FETCH c_sel INTO v_retval;
   CLOSE c_sel;

   IF v_retval = 1 THEN

      Delete from T4W_Documents
      where op__id =TierDocid;

   END IF; END; To execute in SQL*Plus

SQL> EXEC WFS.TP_CLEANMYT4WDOC(1234); M

pjones_at_akirasoft.com (Paul Jones) wrote in message news:<24ffeef2.0208081229.9fb1456_at_posting.google.com>...
> I have an MS SQL background so please bear with me. I am used to being
> able to run:
>
> EXECUTE Stored_Proc_Name value, value, value
>
> to a stored procedure that I create like the following:
>
> Create Procedure Stored_Proc_Name
> @variable1 int,
> @variable2 varchar(25),
> @variable3 float
>
> AS
> Insert Procedure here
>
> I have looked into Oracle's IN and OUT functions, but am uncertain at
> this point. Here is what I have tried:
>
> CREATE OR REPLACE PROCEDURE
> WFS.TP_CLEANMYT4WDOC (TierDocid IN int)
> AS
>
> BEGIN
> if (Select op__Statusord from T4W_Documents where op__id=TierDocid)=1
> Delete from T4W_Documents where op__id =TierDocid
> END
>
> Then executing using:
>
> EXECUTE WFS.TP_CLEANMYT4WDOC 1234
>
> This is resulting in the error: "Invalid SQL statement."
> I am using Oracle 8.1.7
>
> All I need to do is delete the record with the appropriate id, which I
> am passing as a variable. Any help is greatly appreciated.
>
> Paul Jones
Received on Fri Aug 09 2002 - 04:55:42 CDT