output stored procedure results to variable 1

[mm236]

HI,

I wrote a stored proc that will result in a single value.
The stored proc accepts 3 values for input and uses them in the SQL.
I did some reading but can't figure out the code and syntax to get the results of the SQL into a variable.
I can run the stored proc in SQL PLUS and it displays the value 1.7 for the LOW_PRICE variable.
My problem is, this stored proc is being called from an RPG program on an AS/400 using gateway software to connect to Oracle/Windows database.
I looked at using a variable name and OUT after the 3 IN variables.
I saw some code using "RETURN NUMBER AS".
All I want to do is pass the value of LOW_PRICE (1.7) to the program that is calling the stored proc.

If anybody can help with this, I'd appreciate it.

Thanks,
mm236

CREATE OR REPLACE PROCEDURE GET_LOW_PRICE (WDEA IN VARCHAR,
IDEA IN VARCHAR,
NDC_NUM IN VARCHAR) IS
LOW_PRICE NUMBER;

CURSOR c1 (NDC VARCHAR) IS


SELECT Min(T6.AGREEMENT_PRICE) AS LOW_PRICE
FROM VINTAGE.CUSTOMER T1 INNER JOIN VINTAGE.PRODUCT_CROSS_REF T2 INNER JOIN VINTAGE.AGREEMENT_CUSTOMERS T3 INNER JOIN
VINTAGE.AGREEMENT_CROSS_REF T4 ON T3.AGREEMENT_ID = T4.AGREEMENT_ID INNER JOIN VINTAGE.CUSTOMER_CROSS_REF T5 ON
T3.CUSTOMER_ID = T5.CUSTOMER_ID INNER JOIN VINTAGE.AGREEMENT_PRODUCTS T6 ON T4.AGREEMENT_ID = T6.AGREEMENT_ID ON
T2.PRODUCT_ID = T6.PRODUCT_ID ON T1.CUSTOMER_ID = T5.CUSTOMER_ID INNER JOIN VINTAGE.AGREEMENT_PRIME_VENDOR T7 ON
T3.AGREEMENT_ID = T7.AGREEMENT_ID INNER JOIN VINTAGE.PRIME_VENDOR T8 ON T7.PRIME_VENDOR_ID = T8.PRIME_VENDOR_ID
where T2.INDUSTRY_ID=NDC AND T5.INDUSTRY_ID=IDEA AND T8.PRIME_VENDOR_ID In (SELECT
T9.PRIME_VENDOR_ID
FROM VINTAGE.PRIME_VENDOR T9 INNER JOIN VINTAGE.CUSTOMER_CROSS_REF T10 ON T9.PRIME_VENDOR_ID = T10.CUSTOMER_ID
WHERE T10.INDUSTRY_ID=WDEA);



BEGIN
OPEN c1(NDC_NUM);
LOOP
FETCH c1 INTO LOW_PRICE;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LOW_PRICE);
END LOOP;
CLOSE c1;
END;

 

[sem]

Youn need a FUNCTION, not PROCEDURE. The difference is that function returs a value.

CREATE OR REPLACE [B]FUNCTION[/B] GET_LOW_PRICE (WDEA IN VARCHAR,
                IDEA IN VARCHAR,    
                NDC_NUM IN VARCHAR)
[B]RETURN NUMBER[/B]
IS
   LOW_PRICE NUMBER;
                
   CURSOR         c1 (NDC VARCHAR) IS
                

SELECT Min(T6.AGREEMENT_PRICE) AS LOW_PRICE
FROM VINTAGE.CUSTOMER T1 INNER JOIN VINTAGE.PRODUCT_CROSS_REF T2 INNER JOIN VINTAGE.AGREEMENT_CUSTOMERS T3 INNER JOIN 
VINTAGE.AGREEMENT_CROSS_REF T4 ON T3.AGREEMENT_ID = T4.AGREEMENT_ID INNER JOIN VINTAGE.CUSTOMER_CROSS_REF T5 ON 
T3.CUSTOMER_ID = T5.CUSTOMER_ID INNER JOIN VINTAGE.AGREEMENT_PRODUCTS T6 ON T4.AGREEMENT_ID = T6.AGREEMENT_ID ON 
T2.PRODUCT_ID = T6.PRODUCT_ID ON T1.CUSTOMER_ID = T5.CUSTOMER_ID INNER JOIN VINTAGE.AGREEMENT_PRIME_VENDOR T7 ON 
T3.AGREEMENT_ID = T7.AGREEMENT_ID INNER JOIN VINTAGE.PRIME_VENDOR T8 ON T7.PRIME_VENDOR_ID = T8.PRIME_VENDOR_ID
where T2.INDUSTRY_ID=NDC AND T5.INDUSTRY_ID=IDEA AND T8.PRIME_VENDOR_ID In (SELECT 
T9.PRIME_VENDOR_ID
FROM VINTAGE.PRIME_VENDOR T9 INNER JOIN VINTAGE.CUSTOMER_CROSS_REF T10 ON T9.PRIME_VENDOR_ID = T10.CUSTOMER_ID
WHERE T10.INDUSTRY_ID=WDEA);

BEGIN
   OPEN c1(NDC_NUM);
   LOOP
      FETCH c1 INTO LOW_PRICE;
      EXIT WHEN C1%NOTFOUND;

   END LOOP;
   CLOSE c1;
[b]   return LOW_PRICE;[/b]
END;

BTW I'm not sure you really need explicit cursor, select into seems to be better.

Regards, Dima

http://seminihin.narod.ru

 

[mm236]

Thank you very much.

Regards,
mm236