Re: Passing variable values into a stored procedure

From: Paul Jones <pjones_at_akirasoft.com>
Date: 9 Aug 2002 10:21:08 -0700
Message-ID: <24ffeef2.0208090921.581f2d57@posting.google.com>

Just to clear my name, I wanted to let you guys know that I had not started working on the conversion to PL/SQL for the actual procedure part of the stored proc at the time I posted. I was simply trying to understand the syntax for passing and accepting a variable value. I thought what I had was wrong, but it appears I was right. DBArtisan was reporting an error in the first line. I guess my debugger was wrong. It was erroring on line 1, and I'd spent hours on it trying to find the problem so that I could start working through the rest of the procedure, so what you saw was not, by any means, complete.

I've been working with Oracle, in a limited capacity, for 4 years. Most of my work has been system level, not PL/SQL. I was able to finish the procedure last night once I realized that the debugger was reporting an error where there was no problem. I have been using "Oracle SQL: The Essential Referance" (O'Reily) and "Oracle 8: The Complete Reference" (Oracle Press). Both of these books assisted me in creating the following finished procedure:

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

    testcase NUMBER;
BEGIN

• 08/2002 Sequest Technologies Inc
• If the op__Statusord=1 in T4W_DOCUEMTNS then
• there is nothing in the data in the data table,
• therefore, we can delete the record in T4W_DOCUMENTS.

    Select op__Statusord
    INTO testcase
    from T4W_Documents
    where op__id = TierDocid;

        IF  testcase = 1   THEN
            Delete FROM T4W_Documents where op__id = TierDocid;
        END IF;

END; I hope you see I'm not the M$ lacky you may have mistook me for. I may need your help in the future and may be able to offer help too. Just wanted to make sure my first post to this group didn't get me shunned by certain member.

Paul Received on Fri Aug 09 2002 - 12:21:08 CDT