Re: Passing variable values into a stored procedure

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

Being used to something is not a good reason to not learn how to do something the right way in another environment. I assume, for example, you wouldn't try to translate your knowledge of how to drive a car into how to fly an airplane.

The differences between SQL Server and Oracle are quite large. So my suggestion, as was Sybrand's, is that you stop writing code in an environment in which you understand so little and start by picking up a book and learning about the architecture, about multiversioning, about locking, about latches, about transactions, and the differences in syntax.

We like to help people ... who at least make an effort to help themselves.

Daniel Morgan Received on Fri Aug 09 2002 - 10:11:15 CDT