CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
pragya
pragya

Reputation: 23

oracle Stored Procedure to get the sum(balance) given a parameter

I have a supplier_product table ( supp_id, prod_id, invoice_id,price) and an invoice table (invoice_id, balance). I tried a stored proc. given (supp_id) it should all the existing invoice_id and display the balance. here's my code:

set serverouput on;
create or replace
Procedure SUP_loop
(v_SUPPLIER_ID int   )
AS
  CURSOR c_SUP IS
    select  SUPPLIER_ID , SUPP_INVOICE_ID, balance 
      from SUPPLIER_PRODUCT, supplier_invoice
     where SUPPLIER_ID=v_SUPPLIER_ID 
       and supp_invoice_id.supplier_product=supp_invoice_id.supplier_invoice;
BEGIN
  --LOOP WITH IMPLICIT VARIABLE DECLARED 
  --AUTOMATIC, OPEN FETCH, CLOSE

  FOR v_SUP_data IN c_SUP LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_SUP_data.SUPPLIER_ID) || '   ' || 
                             TO_CHAR(v_SUP_data.SUPP_INVOICE_ID) || '   ' || 
                             TO_CHAR(v_SUP_data.balance)  );
  END LOOP;
END;
/

the error i am getting is v_sup_data Error(20,31): PLS-00364: loop index variable 'V_SUP_DATA' use is invalid

Error(9,74): PL/SQL: ORA-00904: "SUPP_INVOICE_ID"."SUPPLIER_INVOICE": invalid identifier

Upvotes: 0

Views: 952

Answers (2)

Henry Collingridge
Henry Collingridge

Reputation: 1970

You have the field and the table names swapped round the wrong way.

You have...

supp_invoice_id.supplier_invoice

...where you should have...

supplier_invoice.supp_invoice_id

:D

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231881

The syntax for referring to a column is <>.<>. So your cursor query needs the join condition to be supplier_produce.supp_invoice_id = supplier_invoice.supp_invoice_id, i.e.

create or replace
Procedure SUP_loop
(v_SUPPLIER_ID int   )
AS
  CURSOR c_SUP IS
    select  SUPPLIER_ID , SUPP_INVOICE_ID, balance 
      from SUPPLIER_PRODUCT, supplier_invoice
     where SUPPLIER_ID=v_SUPPLIER_ID 
       and supplier_product.supp_invoice_id = supplier_invoice.supp_invoice_id;
BEGIN
  --LOOP WITH IMPLICIT VARIABLE DECLARED 
  --AUTOMATIC, OPEN FETCH, CLOSE

  FOR v_SUP_data IN c_SUP LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_SUP_data.SUPPLIER_ID) || '   ' || 
                             TO_CHAR(v_SUP_data.SUPP_INVOICE_ID) || '   ' || 
                             TO_CHAR(v_SUP_data.balance)  );
  END LOOP;
END;
/

Upvotes: 0

Related Questions