Path: news.easynews.com!easynews!feedwest.aleron.net!aleron.net!sfo2-feed1.news.algx.net!allegiance!logbridge.uoregon.edu!tethys.csu.net!nntp!sn-xit-05!sn-xit-06!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Sybrand Bakker Newsgroups: comp.databases.oracle.misc Subject: Re: Passing variable values into a stored procedure Date: Thu, 08 Aug 2002 23:02:37 +0200 Organization: Posted via Supernews, http://www.supernews.com Message-ID: Reply-To: postbus@sybrandb.demon-verwijderdit.nl References: <24ffeef2.0208081229.9fb1456@posting.google.com> X-Newsreader: Forte Agent 1.91/32.564 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: newsabuse@supernews.com Lines: 55 Xref: easynews comp.databases.oracle.misc:85344 X-Received-Date: Thu, 08 Aug 2002 13:58:43 MST (news.easynews.com) On 8 Aug 2002 13:29:03 -0700, pjones@akirasoft.com (Paul Jones) wrote: >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 I think you should do at least some effort and use the Pl/SQL reference manual when appropiate. Your code is glaring with proof you didn't. http://tahiti.oracle.com (both html and pdf) or http://otn.oracle.com Why do all people exposed to Microsoft products loose all energy to look something up? Regards Sybrand Bakker, Senior Oracle DBA To reply remove -verwijderdit from my e-mail address