Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!64.245.249.51!sfo2-feed1.news.algx.net!allegiance!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: pjones@akirasoft.com (Paul Jones)
Newsgroups: comp.databases.oracle.misc
Subject: Passing variable values into a stored procedure
Date: 8 Aug 2002 13:29:03 -0700
Organization: http://groups.google.com/
Lines: 38
Message-ID: <24ffeef2.0208081229.9fb1456@posting.google.com>
NNTP-Posting-Host: 204.248.56.226
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1028838543 19041 127.0.0.1 (8 Aug 2002 20:29:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2002 20:29:03 GMT
Xref: easynews comp.databases.oracle.misc:85341
X-Received-Date: Thu, 08 Aug 2002 13:26:07 MST (news.easynews.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